How to migrate your MySQL / MariaDB database with mysqldump, tar and scp

If you had been building the content for your new WordPress site on a Raspberry Pi 3, there will come a time when you need to migrate the underlying MySQL database to another machine, for eg. a DigitalOcean droplet, for production usage.

MySQL provides us with the mongodump client utility to produce a set of SQL statements that can be executed to reproduce the original database object definitions and table data.

A simple MySQL database migration can be performed with the following steps:

1. Use mysqldump to get the set of SQL statements that can be executed to reproduce the original database object definitions and table data.
2. If the mysqldump output is too big, use the tar command to compress it.
3. Use the scp command to send the mysqldump output from the source server to the destination server.
4. If you had used the tar command to compress the mysqldump output, use the tar command at the destination server to decompress it.
5. Execute the set of SQL commands in the mysqldump output at the destination server.

This post documents how you can migrate your MySQL / MariaDB database with utilities provided by MySQL and most Linux servers.

Exporting data from MySQL / MariaDB database with mysqldump

The way you export data from a MySQL / MariaDB database depends on whether you have a default user that is set to use the Authentication Plugin.

Exporting data from MySQL / MariaDB server with a regular user without the Authentication Plugin

To export data from a MySQL / MariaDB database instance with a regular user without the Authentication Plugin, run the following command:

sudo mysqldump -u root -p db_to_migrate > db_to_migrate.sql

The command will prompt for the password for the root database user. Once the command completes, you will find a text file, db_to_migrate.sql, in your current directory. This file will contain all the SQL statements that can be run to reconstruct the db_to_migrate database instance.

Exporting data from MySQL / MariaDB server with a default user set to use the Authentication Plugin

To export data from a MySQL / MariaDB database instance with a default user set to use the Authentication Plugin, run the following command:

sudo mysqldump db_to_migrate > db_to_migrate.sql

If you had not supplied your system user password via previous sudo command executions, the command will prompt for the password of your current system user. Once the command completes, you will find a text file, db_to_migrate.sql, in your current directory. This file will contain all the SQL statements that can be run to reconstruct the db_to_migrate database instance.

Using the tar command to compress the output from mysqldump

If your database instance is large, you can use the tar command to compress the output from mysqldump to reduce the time needed to send the output file from the source server to a destination server.

To compress the output from the mysqldump command mentioned earlier, we can run the following command:

tar -cvzf db_to_migrate.tar.gz db_to_migrate.sql 

When the command complete, you should get the file, db_to_migrate.tar.gz in your current working directory. This file should be significantly smaller than the original output file that we got from running the mysqldump command mentioned earlier.

Sending the output from mysqldump from the source server to the destination server

If there is a SSH server running at your destination server, you can use the scp command to send the db_to_migrate.tar.gz file from your current working directory to the destination server.

Suppose your destination server is reachable by the domain example.com, has a system user account with the username root and has a /var/receiving directory, you can run the following command to send your db_to_migrate.tar.gz file to the destination server:

scp db_to_migrate.tar.gz root@example.com:/var/receiving/db_to_migrate.tar.gz

Using the tar command to decompress the output from mysqldump

Once the file transfer is done, you can use the ssh command to get into your destination server to extract the contents of db_to_migrate.tar.gz:

ssh root@example.com

Once you got into your destination server, run the following commands to extract the original output file from running mysqldump at the source server:

cd /var/receiving
tar -zxvf db_to_migrate.tar.gz

Once the commands complete, you should be able to get back the original output file from running mysqldump at the source server.

Importing data into the destination MySQL / MariaDB server

The last step to migrating your MySQL / MariaDB database is to import the data to the destination MySQL / MariaDB server.

As with the case of exporting data from a MySQL / MariaDB database, importing data into the destination MySQL / MariaDB server depends on whether you have a default user that is set to use the Authentication Plugin.

Importing data into MySQL / MariaDB server with a regular user without the Authentication Plugin

Assuming that the a_database database instance was created in the destination MySQL / MariaDB server, run the following command to import the data from the source server:

sudo mysql -u root -p a_database < db_to_migrate.sql

The command will prompt for the password for the root user. Once the command completes, the database content from the source MySQL / MariaDB server will be reconstructed at the destination server.

Importing data into MySQL / MariaDB server with a default user set to use the Authentication Plugin

To import data into a MySQL / MariaDB database instance with a default user set to use the Authentication Plugin, run the following command:

sudo mysql a_database < db_to_migrate.sql 

If you had not supplied your system user password via previous sudo command executions, the command will prompt for the password of your current system user. Once the command completes, the database contents from the source MySQL / MariaDB server will be reconstructed at the destination server.

• Share
• Tweet
• Share
• Save