Just to give you an idea, we have a DR db server in another region of AWS (Oregon), from the master (Virginia). We had an issue where replication broke, and we have to do a dump and restore.. we are talking about 3 tb of data.. so making a backup, creating an AMI, moving it across, dumping it back to a volume and then restoring is a lot of work. I am doing an rsync across ssh, and it is taking forever.. I estimate 2 days for the task to complete.. The data is an xtrabackup - so all db tables, and files basically..
Has anyone come across this issue, and what is the best way to transfer such massive amounts of data in the shortest amount of time? Believe me, I have thought of S3 etc.. but don't have the experience in transfer speeds to/from buckets across regions etc. Any ideas?
First made an Xtrabackup using this command: xtrabackup -u root -H 127.0.0.1 -p 'supersecretpassword' --backup --datadir=/data/mysql/ --target-dir=/xtrabackup/ xtrabackup -u root -H 127.0.0.1 -p 'supersecretpassword' --prepare --datadir=/data/mysql/ --target-dir=/xtrabackup/ Then uploaded to S3 bucket using this command: aws s3 sync /dbbackup s3://tmp-restore-bucket/
From the DR server in the other region, ran this command to download the xtrabackup straight to the db data folder after removing the existing db data files. This is the fastest way. aws s3 sync s3://tmp-restore-bucket /data/mysql/ Finally start mysql on the DR server, and start your slave sync again using the command given in one of the xtrabackup files you created. Super easy and the best and fastest way I've found.