Migrating MySQL databases from one server to another

Posted by & filed under Server Admin.

Today I needed to begin migrating databases from my live server to the new dedicated database server. The first step is to identify the tables to be moved.

Login to the server as per normal

me@dev:~$ mysql -u root -p

Then list out the tables to find the one(s) we need to move:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| Syslog             |
| test               |
| test2              |
+--------------------+
4 rows in set (0.00 sec)

For this example, we will move the “test” database. The next step is to create the new empty test database on the remote server:

me@dev:~$ mysqladmin -h remote-server.name create test

The next step is to use the mysqldump command to extract the contents of the current test database and pipe it to the new server database:

me@dev:~$ mysqldump test -h localhost -u root -p | mysql -h remote-server.name test

Once this command completes, the database will be on both servers.