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.