The Fastest Way to Migrate MySQL Databases to Another Server with Mysqldump

Migrating a MySQL database to another server can be challenging, especially if you aim for minimal downtime and fast performance.

One of the most popular tools for accomplishing this task is mysqldump, a utility provided by MySQL for backing up and restoring databases. In this article, we’ll walk you through using mysqldump to migrate your database and create a script to automate the process.

Migratemsql databases to another server and clone the data.

Prerequisites

  • Source and destination servers with MySQL installed
  • MySQL root credentials or an account with sufficient privileges on both servers
  • SSH access to both servers or the DB server needs to allow incoming connections (if not on the same machine)

Export the Database Using mysqldump

The first step in the migration process is to create a source database backup. We’ll use mysqldump to accomplish this. Mysqldump creates a SQL script containing all the necessary commands to recreate the database structure and data on the destination server.

Log in to the source server and run the following command to create a compressed backup of your database:

bash
mysqldump -u [username] -p --single-transaction --extended-insert --quick --lock-tables=false [database_name] | gzip > [backup_file].sql.gz

Replace [username], [database_name], and [backup_file] with your MySQL username, the name of the database you want to migrate, and the desired name of the backup file, respectively.

This command uses the --single-transaction flag to ensure data consistency, --quick reduce memory usage, and –lock-tables=false prevent table locking during the export process.

The most important parameters is –extended-insert. This will make the import of the exported sql file 10 to 15 times faster on the destination server.

Transfer the Backup File to the Destination Server

After creating the backup, you can import it to the other database server.

  • Copy the compressed gzip file to the server and import it
  • Uncompress the file and load it with MySQL via SSH to the DB server

To transfer it to the destination server, you can use a tool like SCP, SFTP, or rsync. For example, to use SCP:

bash
scp [backup_file].sql.gz [destination_user]@[destination_ip]:/path/to/destination

Replace [backup_file], [destination_user], [destination_ip], and /path/to/destination with the appropriate values.

Import the Database on the Destination Server

Once the backup file is transferred, log in to the destination server and run the following command to import the database:

bash
gunzip < [backup_file].sql.gz | mysql -u [username] -p [database_name]

Replace [username], [database_name], and [backup_file] with your MySQL username, the name of the database you want to migrate, and the name of the backup file, respectively.

Download Database Migration Script

To automate the MySQL Database Migration process, you can use this bash script that we used here on wp-staging.com for migrating our database to a managed db hosted on digitalocean. (This is not used any longer as WP Staging can now do database migration):

bash
#!/bin/sh
# migrate.sh

sourceUser=""
sourcePassword=""
sourceHostname=""
sourcePort=""
sourceDatabase=""

destinationUser=""
destinationPassword=""
destinationHostname=""
destinationPort=""
destinationDatabase=""

echo "Exporting $sourceDatabase database to ./wp-staging.com.sql"

start_export=`date +%s`

mysqldump -P $sourcePort -h $sourceHostname -u $sourceUser  -p$sourcePassword --set-gtid-purged=OFF --extended-insert --single-transaction --skip-lock-tables --column-statistics=0 $sourceDatabase > ./wp-staging.com.sql

# Done creating a dump of wp-staging.com

end_export=`date +%s`

echo "Database exported in" $((end_export-start_export)) "seconds"

echo "Importing Database to... "

start_import=`date +%s`

mysql -u $destinationUser -p$destinationPassword -h $destinationHostname -P $destinationPort $destinationDatabase < ./wp-staging.com.sql

# Done importing the dump to managed db

end_import=`date +%s`

echo "Database imported in" $((end_import-start_import)) "seconds"

echo "Total runtime: " $((end_export-start_export+end_import-start_import)) "seconds"

Replace the values of the database credentials on the top of the file with the appropriate information for your source and destination databases.

Before running the script, make sure that the source and destination databases are accessible.

Next, give the script execution permissions:

bash
chmod +x migrate.sh

Run the script to perform the migration:

bash
./migrate.sh

This script exports the source database using mysqldump, imports the database on the destination server using the mysql command, and then cleans up by removing the backup file.

Related Articles