Copy Multiple MySQL Databases Across Linux Servers

We are in the process of moving our staging server from a Virtualbox VM to it’s own dedicated server. In the process, we have to move every website, including all of the MySQL databases associated with each site. Since we use `git`, we just pull in the code, but moving the databases could be tedious. Enter shell commands.

To copy the databases normally I would run mysqldump, save the file, and then run mysql to pull them in. But to do multiple MySQL databases would be tedious, at best. So I simply used the following:

mysqldump -u USERNAME -pPASSWORD --compact --extended-insert 
--skip-comments --databases database1 database2 database3 
...etc... | ssh USERNAME@ mysql -u USERNAME 

This allows me to dump all of the listed databases in one command. I then pipe that to an SSH connection that then runs MySQL to import the databases. This took all of a few seconds.



Posted in MySQL Tagged with: ,