Migrating MySQL Databases with No Downtime – For Non-DBAs

Migrating MySQL databases to a new server can be among a DBA’s most daunting projects, but fear not, our cheat sheet outlines the process step-by-step that even non-DBAs can follow. There are so many considerations surrounding data consistency, minimizing or avoiding downtime, and maintaining security — especially if the migration is to a separate datacenter.

With the 24/7 nature of the modern world of commerce, most organizations want to entirely avoid any downtime when a database move is necessary. Reasons for migrating a MySQL database might include scaling up to a more robust DB server, or switching an entire infrastructure to a different hosting provider (for cost savings).

This guide assumes you have a single, production MySQL server running on localhost, or within an internal network such as an AWS Virtual Private Cloud (VPC), Azure Virtual Network, or RackSpace ServiceNet. It also assumes that your new MySQL server is outside the external perimeter of your network (such as with a different cloud provider), therefore the mysqldump and replication data needs to be transmitted with encryption over the Internet. Also, it assumes your database is not managed by a service such as AWS Relational Database Service (RDS) or Azure SQL Database as the steps require access to the my.cnf configuration.

Conventionally speaking, migrating a MySQL server would first involve stopping the mysql daemon (mysqld) by issuing the command sudo service mysqld stop or sudo systemctl stop mysqld to prevent the data from drifting (diverging from the export) during the process.

You could also flush the DB server with a global read lock, using the query mysql > FLUSH TABLES WITH READ LOCK; to keep the database available for read-only access, but not write operations. If your applications require write access to the database, either of these options result in downtime during the migration — which is clearly undesirable.

Although data consistency is guaranteed by a complete DB server shutdown or global read lock prior to exporting the data using mysqldump, it simply isn’t a practical option for busy production applications that customers count on.

Migrating MySQL Databases with Master/Slave Replication

A better way to migrate MySQL servers to a new home is to set up Master/Slave Replication between the existing and new servers. The existing server is configured as the master, with the new server as the slave. For those uninitiated (pardon the pun) with MySQL replication, it is a real-time process where a link is established between two database servers. As writes are made to the master, the events are pushed to the slave, which replays the identical queries to sync up the slave copy of the data with the master. The slave is usually set up as read-only to prevent any changes from being made to it directly.

It should be clear why this is useful in migrating data between an existing and new MySQL server. The master server (old environment) can remain in-service, while data is being synced up on the slave server (new environment). Once the slave is completely caught up to the master, the application is remapped to the new DB server and the read-only flag is removed — essentially promoting the slave to the master. As soon as everything is cut-over, the original server can be shut down yielding cost savings.

SQL replication relies on the MySQL binary log, also known as the bin-log. The bin-log assigns every SQL query a set of coordinates identified by a bin-log file and an index. This feature is not enabled by default, and must be enabled on the master by editing my.cnf, the MySQL configuration file. The default location of my.cnf is located at /etc/my.cnf for the MySQL Community Server 5.7.25 installed on CentOS 7.6.

STEP 1 – Enable the binary log.

On master: As root, add the following lines under the [mysqld] block to enable bin-logging.

[mysqld]
server-id = 1
binlog-format = mixed
log-bin = mysql-bin
innodb_flush_log_at_trx_commit = 1
sync_binlog=1

Restart the mysql server by issuing the command sudo service mysqld restart.

By default the bin-log is stored in the same directory as the rest of your MySQL data. If you haven’t moved your MySQL data directory (datadir) to an external volume, this will reside at /var/log/mysql.

STEP 2 – Create replication user.

On master: Authenticate to the MySQL console as root and create the replication user.

Replace 222.222.222.222 with the external IP address of the slave server and aSecurePa$$w0rd with a secure password up to 32 characters long. The REQUIRE X509 option is set to require client key authentication to ensure replication takes place over SSL.

mysql > CREATE USER 'replication'@'222.222.222.222' IDENTIFIED BY 'aSecurePa$$w0rd' REQUIRE X509;

mysql > GRANT REPLICATION SLAVE ON *.* TO 'replication'@'222.222.222.222';

mysql > FLUSH PRIVILEGES;

STEP 3 – Generate CA, server, and client keys.

On master: MySQL provides a handy option for generating a set of self-signed CA, server, and client keys. To invoke this option, run mysql_ssl_rsa_setup at the command line as root. The following files will be created in your MySQL data dir. Assuming the default data directory:

/var/lib/mysql/ca.pem – CA public key
/var/lib/mysql/ca-key.pem – CA private key
/var/lib/mysql/server-cert.pem – server public key
/var/lib/mysql/server-key.pem – server private key
/var/lib/mysql/client-cert.pem – client public key
/var/lib/mysql/client-key.pem – client private key

Add the server keys to the my.cnf file on the master under the [mysqld] block.

[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

The four files to transfer to the slave (client) server are ca.pem, server-cert.pem, client-cert.pem, and client-key.pem. These key files permit the client to verify the CA and server public key, and identify itself using the client key pair.

Restart the mysql server by issuing the command sudo service mysqld restart.

Since key files are sensitive, use an encrypted channel such as scp to transfer them to the slave server. From the datadir on the master server:

su -
cd /var/lib/mysql
scp ca.pem server-cert.pem client-cert.pem client-key.pem root@222.222.222.222:/var/lib/mysql/

Remember that 222.222.222.222 should be the external IP address of the slave server. The SSH port on slave server must be open to the master server, so if there are any iptables or firewalld rules standing between the two servers, it’s up to you to rectify that.

STEP 4 – Export MySQL database using mysqldump.

Before proceeding, ensure you have sufficient disk space to export the database to a .sql file. This query can be used to estimate the exported size of all databases as a mysqldump. If your DB server does not have enough disk space on its root volume, create a block storage volume in your cloud provider’s dashboard, attach it to your instance, then format and mount it in a directory such as /mnt/mysqldump.

mysql > SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;

Since MySQL dumps are a flat text file, an effective strategy to reduce the size is to pipe the exported data directly to gzip for compression. For a large database, it will significantly save on the time required to transfer the data between the existing and new servers. Install gzip with sudo yum install gzip for RHEL and CentOS, or sudo apt install gzip for Ubuntu and Debian machines.

Since mysqldump is a long running process that will terminate if the SSH connection closes (broken pipe), it’s smart to use the screen utility to run it in the background. Install screen with sudo yum install screen for RHEL and CentOS, or sudo apt install screen for Ubuntu and Debian machines. Then launch a new screen by typing screen at the terminal.

On master: Run the following command in the desired destination, to begin the export. This is a non-locking process that keeps your production database server available for new connections while the export is underway.

mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A  | gzip -c > dump.sql.gz

The mysqldump utility does not show progress, so all you will see during the process is a solid cursor in the terminal. Press Ctrl-A and Ctrl-D to detach from the screen and return to the main terminal. You can reattach to the screen to check whether the export is completed by typing screen -r, even if you disconnect and reconnect to SSH.

STEP 5 – Transfer the MySQL dump to the new database server.

On master: Once the mysqldump is finished, the solid cursor will disappear and on the next line will be an ordinary terminal prompt. Using rsync, transfer the dump.sql.gz file to the new database server.

Remember when syncing a file to a remote destination with rsync, the local file name precedes the remote username, hostname, and directory. If the destination directory does not exist, it should be created on that host beforehand with mkdir -p <destination dir>.

rsync -vz --progress dump.sql.gz root@222.222.222.222:<destination dir>

Depending on the peering between the source and destination data centers and the file size, the transfer may take several hours. You can expect anywhere between 9-35MB/s transfer between most data centers over the Internet, faster if they are in the same region on a continent.

Like any other long running process, run the rsync in a screen so you can disconnect from SSH without interrupting the transfer. After starting the rsync, use the Ctrl-A and Ctrl-D key combinations to detach from the screen before closing the terminal.

STEP 6 – Import the MySQL dump to the new database instance.

On slave: The slave should be setup with a new database instance containing no data. It is ideal to use the same version of MySQL Community Server on both the master and the slave. Be sure to change the default root password after installing MySQL from the official repository by running mysql_secure_installation. The preset root password can be located by grep-ing from the MySQL log file — cat /var/log/mysqld.log | grep password

Launch a new screen with the screen utility prior to these steps.

Change to the directory containing the dump.sql.gz which was just transferred from the master.

The directory must have sufficient disk space to decompress the gzipped file. Note that its typical for a 10GB MySQL dump compressed with gzip to be over 60GB when expanded.

gunzip dump.sql.gz

gunzip will remove the dump.sql.gz (compressed) file and create a dump.sql (decompressed) in its place. Record the Master Log File and Master Log Position from the dump.sql file. This is needed in a later step to initiate replication.

head dump.sql -n80 | grep "MASTER_LOG_POS"

The output will look like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;

Finally, import the MySQL dump into your slave database server. All of the queries to create the current state of the existing database server are replayed into the new database server during the import.

The -f flag means force, which tells MySQL not to halt the import even if any errors are encountered. Sometimes tables from a running database server can be damaged, leading to errors when exported and imported.

The skipped lines are outputted to the terminal for you to review. If some queries lead to errors, the problematic rows or tables might also have to be skipped later during replication to prevent replication from hanging. One of our consultants can assist you in identifying the MySQL error code(s) and skipping it in the replication if appropriate.

mysql -uroot -p -f < dump.sql

Press Ctrl-A and Ctrl-D to detach from the screen, then wait for the import to complete when the solid cursor returns to a terminal prompt.

STEP 7 – Edit the slave my.cnf file.

On slave: Add the following lines to the slave’s my.cnf file in the [mysqld] and [client] blocks, located at /etc/my.cnf by default.

[mysqld]
server-id  = 2
binlog-format = mixed
log-bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1

Restart the MySQL service on the slave — sudo systemctl restart mysqld

STEP 8 – Start replication from the slave’s MySQL console.

mysql > CHANGE MASTER TO MASTER_HOST='111.111.111.111',MASTER_USER='replication', MASTER_PASSWORD='aSecurePa$$w0rd', MASTER_SSL_CA='/var/lib/mysql/ca.pem', MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem', MASTER_SSL_KEY='/var/lib/mysql/client-key.pem', MASTER_SSL=1, MASTER_LOG_FILE='mysql-bin.######', MASTER_LOG_POS=###;

mysql > START SLAVE;

mysql > SHOW SLAVE STATUS \G

Replace 222.222.222.222 with the IP address or hostname of the master database server, aSecurePa$$w0rd with the actual password of the replication user, and ###### and ### with the Master Log File and Master Log Position you recorded in step 6. Remember to include all leading zeros if applicable.

Pay attention that there is no space between the MASTER_HOST and MASTER_USER values. Also, unlike most MySQL queries, there is no semicolon following the SHOW SLAVE STATUS \G command.

If successful in initiating master/slave replication between the database servers, the output of SHOW SLAVE STATUS \G should look similar to this. Pay special attention to these values:

Slave_IO_State: Waiting for master to send event
Seconds_Behind_Master: 68523
Slave_SQL_Running_State: Reading event from the relay log

When Seconds_Behind_Master reaches 0, then the slave database server is in sync with the master database server. In the meantime, patiently wait while MySQL copies over the changes that have been made to the master database since you did the export. This may take several hours or more depending how actively your databases are written to.

mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 222.222.222.222
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 158605813
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 3840221
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3840055
Relay_Log_Space: 158606186
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/log/mysql/ca.pem
Master_SSL_CA_Path:
Master_SSL_Cert: /var/log/mysql/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /var/log/mysql/client-key.pem
Seconds_Behind_Master: 68523
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 360a313d-8633-11e8-8517-bc764e203f26
Master_Info_File: /var/log/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

Step 9 – Stop replication on the slave, and promote it to the master.

Once the replication is complete with Seconds_Behind_Master: 0, shut down the master database server with sudo systemctl stop mysqld to ensure no new writes can be made to it. Then, on the slave, run:

mysql > STOP SLAVE;

Finally, edit the my.cnf file on the slave and remove the following lines from the [mysqld] block.

[mysqld]
log-slave-updates = 1
read-only = 1

Then restart the MySQL service on the former slave, now promoted to master — sudo systemctl restart mysqld. You may now connect any applications relying on the database to the new database server.