migrate multiple mysql databases using mysqldump on Linux

Sometimes we need to migrate all mysql databases to a new machine. This can be the case when you are migrating your multiple wordpress blogs to a new machine on Amazon EC2. Recently I had to do this when I migrated to VPC. Here are quick instructions to migrate all databases on Ubuntu (14.04.2 LTS) Linux using mysqldump (Ver 10.13):

mysqldump all databases

Use mysqldump to dump all your databses to a file

$ mysqldump -u root --password=DB_PASSWORD --all-databases > all_dbs.txt

Note that –all-databases is used to specify all databases. This will not include mysql internal databases information_schema, performance_schema, etc. It will include mysql database though. That will mean all your users will also be migrated.
In case your database is on a remote machine, you can also use ssh to run mysqldump remotely and get data on current local machine.

$ ssh HOSTNAME mysqldump -u root --password=DB_PASSWORD --all-databases > all_dbs.txt

To confirm what databases got dumped to the file, run the following command:

$ grep "^CREATE DATABASE" all_dbs.txt
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bdname1` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bdname2` /*!40100 DEFAULT CHARACTER SET utf8 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bdname3` /*!40100 DEFAULT CHARACTER SET latin1 */;
...

Note that for mysql database CREATE DATABASE statement would not be present. Also note the present of “DROP TABLE IF EXISTS”. This will ensure all tables are overwritten.

$ grep "^DROP TABLE IF EXISTS" all_dbs.txt
DROP TABLE IF EXISTS `wp_commentmeta`;
DROP TABLE IF EXISTS `wp_comments`;
...

Create databases using mysql

Assuming the new machine mysql does not have any db created, run the following to create all these databases on new mysql machine.

$ cat all_dbs.txt | mysql -u root --password=DB_PASSWORD

This will create all the dbs. You can run the following command to display the names of all created databases:

$ mysql -u root --password=DB_PASSWORD -e "show databases;"