Skip links
Main content

How to migrate MySQL databases without downtime

Saturday 25 February 2012 14:21

By Casper Langemeijer

It is time for me to write a new techblog article, not only because it's been a while, but mostly I found myself going through my old notes again and again. This time I'm going to write about copying MySQL databases without bringing the database server down.

I should really say copy a database with minimal downtime. Depending on your current MySQL server configuration, you might need to restart mysql before the actual copying is done. Also if you have a big number of MyISAM tables, while copying that table locks are used to ensure database integrity. It might be worth converting tables to InnoDB for smooth migration.

In this document I'll refer to 'source server' when I'm talking about the server that currently holds the database, 'target server' is the server that will have a copy when we're done.

Copying the database is done in these easy steps:

  • Change config on source server (Enable MySQL binlog)
  • Use xtrabackup to write the database to the target server
  • Start target server with the database as copied
  • Start replication at the point the database was copied
  • Switch your application to the target server
  • Tear down the source server if you like

Change config on source server

Because we use mysql replication in a later stage of the migration, we need to prepare ourselves for that. We need to:

  • Enable MySQL binlog
  • Allow incoming mysql connections from the target server
  • Create a user for the replication
  • Create users for when your application uses the target server

Choose a unique identifier for your mysql server. To avoid an administration nightmare use a server number, or concatenared ip address of the server. Something that is unique for your server already.

Add the following lines to your mysql configuration (typically /etc/mysql/my.cnf):

log_bin = binlog
bind-address =
server-id = <uniqueid>

Now create a user for the replication process:

CREATE USER root@<targetip> IDENTIFIED BY '****************';
GRANT ALL PRIVILEGES ON *.* TO root@<targetip> IDENTIFIED BY '****************' WITH GRANT OPTION;

Also create the users required for your application to connect to the new machine.

Use xtrabackup to write the database to the target server

Install the xtrabackup tool on both the source and target machines. I use the packages provided by Percona

aptitude install xtrabackup

Now connect to your source machine with agent forwarding if you use keys to authenticate to your server (You should really!).

From the source machine use SSH to connect to the target machine as a test. Also this adds the host to the list of known hosts.

source:/# ssh root@<targetip>
The authenticity of host '<targetip>' can't be established.
RSA key fingerprint is xx:xx:xx:xx. . . xx:xx.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '<targetip>' (RSA) to the list of known hosts.

On the server we execute innobackupex, telling it to create a tar image. We pipe that image directly to the remote machine. This eliminates the disk writing I/O on the source server, to reduce load. On the target server the files are untarred on the fly.

Make sure to create the directory on the target server first!

innobackupex --stream=tar ./ | gzip | ssh root@<remoteip> "cd /data/mysql-incoming; tar -izx"

At some time in the process, you'll see:

innobackupex: Starting to lock all tables...
innobackupex: All tables locked and flushed to disk
innobackupex: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex: subdirectories of '/themysqldatadir/'

This locking is required to copy all MyISAM data. As it says, it locks all tables and might block your application. Keep usage of MyISAM tables to a minimum to avoid downtime here.

At the end of this process, it will output something like:

innobackupex: MySQL binlog position: filename 'binlog.000001', position 102865665

This information is very important. This is were the replication should start.

Now go to the target server, into the directory the mysql files were written to. We need to 'prepare' the files for usage by the mysql server

cd /data/mysql-incoming
xtrabackup --prepare --target-dir=.

This command also returns something like:

InnoDB: Last MySQL binlog file position 0 102865261, file name ./binlog.000001

This is different from the position recorded earlier, and my experiments proved the first to be correct, and this one to be false.

Start target server with the database as copied

Now move the /data/mysql-incoming dir to it's final destination, replacing the original database. (/var/lib/mysql in our example)

On target server:

/etc/init.d/mysql stop
mv /var/lib/mysql /var/lib/mysql.zzz
mv /data/mysql-incoming /var/lib
chown -R mysql. /var/lib/mysql

/etc/init.d/mysql start

Start replication at the point the database was copied

Execute the following mysql sql queries on the target server to start replication, replace the MASTER_LOG_FILE and MASTER_LOG_POS values with the values you've been told by innobackupex.


From the last query you will get something like this:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xx.xx.xx.xx
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 31115988
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 24556044
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Last_Errno: 0
Skip_Counter: 0
Exec_Master_Log_Pos: 24555901
Relay_Log_Space: 31116750
Until_Condition: None
Until_Log_Pos: 0
Master_SSL_Allowed: No
Seconds_Behind_Master: 561
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_SQL_Errno: 0
Master_Server_Id: 109
1 row in set (0.00 sec)

Note the value Seconds_Behind_Master. If you execute 'SHOW SLAVE STATUS' a few times this should read zero. Once zero is reached, your target server is a live running slave of the source server that is running as master.

Switch your application to the target server

Now it's time to switch your application to use the target server. Remember that changes are only synced from master to slave, thus from source to target. Any changes on the target machine will not be reflected on the master. Because of this, you should make sure your application atomically changes server.

For most web applications you'll be fine changing the database host in some configuration file. Watch out for long running background processes. You cannot reverse easily. Once over to the master, there is no way back except setting up circle replication. I'll probably discuss this in a later article.

Also, watch your Seconds_Behind_Master value when you switch database servers. Unexpected things could happen if your target server is lagging behind.

Tear down the source server

If you do not plan to keep the source server up, remove it! It will avoid confusion in the future.

« Back

Reactions on "How to migrate MySQL databases without downtime"

No posts found

Log in to comment on news articles.