LinuxMySQLSSH

How to Migrate Local Databases to Remote Host or Vice Versa Without Dump Files

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
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 *