MySQL – How to Backup Database from One Cloud Server to Another

MySQLmysql-5.7mysqldump

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?

Best Answer

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.

Connection possible

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.

Related Question