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?