Copy multiple MySQL databases from server to server, securely

I had to migrate a multi-site installation of Drupal from one server to another, and devised a way to copy all the relevant MySQL databases from the old server to the new server with a single bash command. It is secure as it is piped through SSH. Hope someone finds this useful.

Execute the following statement on the server containing the databases to be migrated. Italicized items should be replaced with relevant values for your system. Replace the list of databases with your list of databases to be migrated -- I used this to migrate 20 or so databases with one command. :)

The requirements are mysqldump and SSH access to the destination server, a properly credentialed MySQL user on each server (i have used root in the example), and of course access to the mysql command line interface:

mysqldump -uroot -plocalrootpassword -a --databases database1 database2 database3 | ssh root@destinationserver.com "mysql -uroot -pdestinationrootpassword"

Upon executing, you will be prompted for a password, that is the SSH login password on the destination server. Databases will be copied over (no need to create them first on the destination server). Be careful not to overwrite any existing databases on the destination server.

Note that there is no indication of status or progress, and know that it can take quite a while to run depending on how many databases, how much data, and the bandwidth & processing power available to the servers. Patience will be rewarded. If you are craving status, execute SHOW DATABASES on the destination server and you can at least tell which databases are finished/in progress.

Enjoy!