1

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. – Pankaj Kumar Mar 12 '18 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 – percona-lorraine Mar 20 '18 at 15:37
  • which cloud? name? – Ivanov Apr 27 '18 at 5:51
1

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. – Pankaj Kumar Mar 13 '18 at 5:28
  • see if my edit helps – Rick James Mar 13 '18 at 18:36
  • I have checked it not working. – Pankaj Kumar Mar 15 '18 at 6:39
  • @PankajKumar - Any clues of why "not working"? Messages? Did it fail quickly? Or after a long time? – Rick James Mar 15 '18 at 12:42
  • I always see mysqldump Lost connection. in logger. – Pankaj Kumar Mar 16 '18 at 12:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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