So I'm trying to figure what the best/quickest way would be to refresh a massive amount of data. We're wanting to copy 3 months worth of data from a replication server to a QA server for product testing.

It's one table and I "only" need to refresh the QA server with the last 3 months, then set something up so it does a weekly refresh of the new records. My biggest priority at the moment is getting this 400G worth of data over. Mysqldump would obviously take forever, even if I cut it up. I was thinking about doing 1 week at a time but that's still 150 million rows and probably about 40GB.

It's a reporting server, so the table only gets added to, none of the data gets updated or changed. The table is innodb and binlong_format is set to row and logs are expired every 4 days on the host. The destination server is a VM with 16G of memory, innodb_buffer_pool_size = 8G, and innodb_flush_log_at_trx_commit = 2

I could probably go through the backups and get the previous binlogs, but is there even a way to pull out the data for just that one table? Am I destined to spending days on end dump/restoring this data?

  • Not to answer the question, but having a 400G table is little not good.. no!!!! – Jehad Keriaki Apr 30 '15 at 18:07
  • Please run SHOW CREATE TABLE tblname\G on the giant table and post it in the question. Also, tell us what date column determines the last 3 months. – RolandoMySQLDBA Apr 30 '15 at 18:12
  • The table is actually 1.4T o_0. Added the show create... thanks for the help! – awdboxer Apr 30 '15 at 19:55
  • and i'd be using the createdtime column to pull out the last 3 months. – awdboxer Apr 30 '15 at 20:10
  • This might be a little simplistic, but could you not create a new QA DB server which is also a replication of live, allow the replication to populate the server and then stop the replication going to the QA server? – gabe3886 May 7 '15 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.