Steps to Migrate a MySQL Database From One Server to Another

Migrating a MySQL Database From One Server to Another

In a migration or disaster recovery situation, these are the steps you can take to make a backup of a database, and import it onto another server. For obvious reasons, both the source and target MySQL servers should be running the same mysql-server version.

1. Dump the database to a backup file. You must use a logon name that has the proper rights on the source database.

mysqldump -u [username] -p[password] --single-transaction [databasename] > backup-[databasename]-$(date +%Y%m%d-%H%M).sql

2. Copy the backup-[databasename].sql over to the new server. SCP, FTP, or any method you prefer or have available.

3. Create the target database with the same name as the one that is to be restored.

mysqladmin --user=root create [databasename]

4. Import the data from the backup copy of the database.

mysql [databasename] < [database-filename].sql