How mysqldump Remote to Local

  • by

Remote to Local

For migrating a remote database, you can dump and pipe the whole database from remote server to local server via ssh tunnel without intermediate dump files.

[root@test ~]# ssh 10.10.10.1 "mysqldump --routines -u root -p'xxxxxxxx' chicago_db" | mysql -u root -p'xxxxxxxx' -D boston_db

Local to Remote

Vice versa, you can migrate a local database to the remote server.

[root@test ~]# mysqldump --routines -u root -p'xxxxxxxx' boston_db | ssh 10.10.10.1 "mysql -u root -p'xxxxxxxx' -D chicago_db"

If the remote server should be accessed across internet or the network bandwidth is poor between two sites, you can zip the dump stream and then transport to the remote server.

[root@test ~]# mysqldump --routines -u root -p'xxxxxxxx' boston_db | gzip | ssh 10.10.10.1 "gunzip | mysql -u root -p'xxxxxxxx' -D chicago_db"

All above commands are verified and executable. I assumed that the account on the both sides are all root.

Leave a Reply

Your email address will not be published. Required fields are marked *