Migrating a MySQL Database Using mysqldump

Migrating a MySQL database from one server to another is a quick process when you have shell access to both locations.  mysqldump can be used to create a SQL script that will recreate the database on the new server.

1. Dump the Database

Connect to the first server.  Use mysqldump to dump the database to a file.

ssh admin@server01.example.com 
mysqldump -u root -p --opt mydb > mydb_dump.sql

The contents of this file contain the structure and data of the database.


2. Copy the Dump File to the New Server

Use SCP to transfer the file to the second server.

scp mydb_dump.sql admin@server02.example.com:/home/admin/

The SQL file will now reside in /home/admin/ on the second server.


3. Import the Database from the Dump File

Use the mysql client to import the database.  The contents of the SQL file can be piped into the command which will evaluate and execute the script.

ssh admin@server02.example.com
mysql -u root -p < /home/admin/mydb_dump.sql

Note that if you do not use root to import the database, the user provided needs to have certain privileges granted on the database in order for the import to succeed.


Additional Reading: