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?
asked Mar 12, 2018 at 10:58
-
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 KumarCommented 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– greenweedsCommented Mar 20, 2018 at 15:37
-
which cloud? name?– SybilCommented Apr 27, 2018 at 5:51
Add a comment
|
2 Answers
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.
answered Mar 12, 2018 at 21:36
-
No, I have to take direct backup from cloud 1 server to cloud 2 server. However I have direct access between these two server. Commented Mar 13, 2018 at 5:28
-
-
-
@PankajKumar - Any clues of why "not working"? Messages? Did it fail quickly? Or after a long time? Commented Mar 15, 2018 at 12:42
-
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.
answered Mar 15, 2018 at 13:07