Nov 8, 2018

How to replicate MySQL with hotbackup without down time

Here’s a quick step-by-step guide to using xtrabackup to replicate a slave.

Install xtrabackup

If you don't have any Percona software already on your server, you will need their repository installed. Here’s how:

$ sudo rpm -Uhv

From there simply install xtrabackup:

$ sudo yum install -y xtrabackup

Snapshot master datadir

The innobackupex utility comes with xtrabackup. We can just use it to perform the backup.

$ innobackupex /data/backup/

Now we'll see a new directory created inside /data/backup which looks something like this:


Apply binary logs

The backup which xtrabackup created above is of the current state of the database. However there are transactions which are incomplete, and others which have not been flushed to their datafiles. In other words the backup as-is would be similar to a datadir if your database crashed. Some additional transactions must still be applied.

To apply those changes, use the following command on the backup directory you created above:

$ innobackupex --apply-log /data/mysql/backup/2018-08-08_08-08-08/

Copy to slave

$ scp -r /data/mysql/backup/2018-08-08_08-08-08 root@slave:/data/

Stop MySQL

$ sudo /etc/init.d/mysql stop

Swap datadir

$ cd /data
$ mv mysql mysql_old
$ mv 2018-08-08_08-08-08 mysql

Adjust my.cnf parameters

At least you need to set the server id to a unique value. The IP address with the periods removed can make a good server id.

Start MySQL

$ sudo /etc/init.d/mysql start

Point to master & start the slave

One very nice thing about xtrabackup is that it automatically captures the master info, so we can easily find out the current log file & log position. That’s a very nice feature.

Find out where the slave should start from:

$ cat /data/mysql/xtrabackup_binlog_infolog_bin.000188 1881888
Now tell MySQL where the new master is:

mysql> change master to-> master_user=’rep’,-> master_password=’rep’,
-> master_host=’′,

-> master_log_file=’log_bin.000188′,

-> master_log_pos= 1881888;

Now start the slave:

mysql> start slave;

At last verify that it is running properly:

mysql> show slave status\G;

You should see the following:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

xtrabackup makes building a slave much simpler in MySQL. It captures the file & position for you and what’s more there is no dump file to apply – which typically takes a lot of time too! All in all the tool makes you more efficient, and allows you to snapshot slaves anytime you like.