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?

  • As when mysqldump is running in InnoDB using save point at same server web application going slow down. So, I want to take this backup to another server. Mar 12, 2018 at 12:18
  • There is another tool that might possibly help with this (you will need to try it) Assuming that by cloud you mean something like AWS RDS, then the only improvement might be to parallel-ize the operation using mydumper instead of mysqldump. It does the same logical backup but in multiple concurrent threads. github.com/maxbube/mydumper
    – greenweeds
    Mar 20, 2018 at 15:37
  • which cloud? name?
    – Sybil
    Apr 27, 2018 at 5:51

2 Answers 2


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.

  • No, I have to take direct backup from cloud 1 server to cloud 2 server. However I have direct access between these two server. Mar 13, 2018 at 5:28
  • see if my edit helps
    – Rick James
    Mar 13, 2018 at 18:36
  • I have checked it not working. Mar 15, 2018 at 6:39
  • @PankajKumar - Any clues of why "not working"? Messages? Did it fail quickly? Or after a long time?
    – Rick James
    Mar 15, 2018 at 12:42
  • I always see mysqldump Lost connection. in logger. Mar 16, 2018 at 12:59

I have take backUp by this query :

mysqldump mysqldump --single-transaction --routines --host -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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.