Moving MySQL Databases To A New Server

Recently I needed to move 19 databases from server A to sever B. The new server B was a scaled down Linux server with no web services so I couldn't use phpMyAdmin, the MySQL database administration tool. I needed to learn how to take care of this task using the command line only.

After moving 19 databases, I created a fairly easy system for exporting, transferring, importing, and creating permissions.

Step 1: Dump Database

Use SSH and log into server A. Navigate to a temporary folder or create a new folder for dumping the databases into. From the command line issue the following command

PLAIN TEXT

CODE:

mysqldump DATABASE_NAME > DATABASE_NAME.sql

This will dump the structure and data from the database you named above into the directory you are currently in.

Step 2: Transfer File

Now you need to transfer the file to the new server.

PLAIN TEXT

CODE:

scp DATABASE_NAME.sql USERNAME@IP_ADDRESS:DIRECTORY_ON_SERVER_B/DATABASE_NAME.sql

Replace DATABASENAME with the name you gave the file in step 1. Replace USERNAME with the login name on server B. I used root. Replace IPADDRESS with the IP of server B. DIRECTORYONSERVER_B is the name of the directory on server B where you want to transfer the file to. It doesn't really matter where you put it.

Once you issue the above command, you will be asked to provide the password for the username you supplied. Once accepted, the file will be transferred.

Now log into server B and navigate to the directory where you transferred the database file to.

Step 3: Create Database

PLAIN TEXT

CODE:

mysql -u root -p -e 'CREATE DATABASE DATABASE_NAME';

This command will create a new database with the name you provide. Should be the same name as you used on server A. After entering the above command provide the MySQL root password and the new database will be created.

Step 4: Restore Data

PLAIN TEXT

CODE: