How can I take backup (by mysqldump) MySQL database from one cloud server to another?
I have a MySQL server that uses innodb and is about 150GB size.
I want to take backup of database to other server, what's the most efficient way to do this?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Clouds can't talk to each other
There is a 3rd machine that can access both of them, correct -- your machine.
your> mysqldump -h cloud1 ... >somefile.sql your> mysql -h cloud2 ... <somefile.sql
Or, to be more daring:
your> mysqldump -h cloud1 ... | mysql -h cloud2 ...
Then you aren't even touching your disk.
Performance will be determined by the bandwidth between you and each cloud. Adding a gzip in the path won't help because it won't be compressed until it gets to your machine. Percona's
Xtrabackup is a good alternative to
mysqldump. It has compression options and even some parallelism, but, again, you may not be able to take advantage of such.
I avoided the obvious (having one cloud send to the other) because I expect such traffic is forbidden. Or you can't run commands from a shell script on either cloud.
On either cloud machine, do
mysqldump -h cloud1 ... | mysql -h cloud2 ...
If the network is the slow part, then do this on the source machine
mysqldump ... | gzip | ssh cloud2 ... "gunzip | mysql"
(I forget the details of how to use ssh to execute the stuff over yonder.)
That could be reversed to run on cloud2:
ssh cloud1 "mysqldump ... | gzip" | gunzip | mysql ...
but it may not matter much.
I have take backUp by this query :
mysqldump mysqldump --single-transaction --routines --host 10.222.55.111 -u root -p 'Password' -P 3306 dbname > backup/dbname _$(date +%F).sql
It works fine for taking backup from one cloud to other cloud server. But now I get error
mysqldump Lost connection.
and my backup process not completed. Then I have changed
max_allowed_packet = 1073741824;
Now it works some time and sometime fails. So, It doesn't seems full proof.