07 May

MySQL Replication with Minimal Downtime Using Hot Copy for Linux

I was experimenting with techniques to initialize MySQL replication for both InnoDB and MyISAM tables without significant downtime. The idea of locking all tables and performing a backup to ship to the replica simply takes far too long. I remember coming across this utility which is sorta similar
To LVM’s snapshot but what if you’re in a situation where your system doesn’t live on LVM based volume then what ?  How does one snapshot mysql-data directory for building replica for replication?  What I found was a tool from Idera called R1Soft Hot Copy for Linux.
This little nifty tool hooks itself into the kernel and listens to the disk at a raw block level allowing
You to snapshot the system without the need for LVM.

What differentiates this process from a more standard approach is the employment of R1Soft Hot Copy. R1Soft Hot Copy is a tool that facilities the creation a snapshot of a block device. When changes to the original device occur only the differences are placed in the snapshot in a Copy-on-Write fashion (similar to VSS in Microsoft Windows). This allows an administrator to create a functional, mountable backup of an entire device almost instantly with very little effort.

I’m posting these instructions because I’d like some feedback not only on my adaptation, but also on the initial method. Feel free to use any of this information, but please be careful. It worked for me, but I’m not qualified to write authoritative tutorials on the subject.

 

Prerequisites and Requirements

I’m going to make the assumption that the reader knows how to setup MySQL replication using the methods outlined in the official documentation.

Also keep in mind that R1Soft Hot Copy is a Linux utility making this article not directly applicable to other operating systems.

Methods

The cost of not locking tables was a restart of the MySQL service itself on the master; meaning that even read queries were not able to be processed momentarily. My idea was to instead flush and lock tables in the standard fashion while creating the Hot Copy mount. That should allow read queries to still be processed and connection attempts to succeed. Writes will be temporarily blocked, but only briefly and clients should have an error free, albeit slower, experience.

Step 1: Install R1Soft Hot Copy

Use the instructions on Idera’s website to install Hot Copy and then run

# <span class="GINGER_SOFATWARE_noSuggestion GINGER_SOFATWARE_correct">hcp</span>-setup --get-module

on the master.

Step 2: Configure master

Enable binary logging on the master server and configure a server id in my.cnf.

<span class="GINGER_SOFATWARE_correct">log</span>-bin=mysql-bin
<span class="GINGER_SOFATWARE_correct">server</span>-id=10

On the master create a user specifically to be used for replication.
<span class="GINGER_SOFATWARE_correct">mysql</span>&gt; GRANT REPLICATION SLAVE ON *<span class="GINGER_SOFATWARE_correct">.</span>* TO 'repl'@'SLAVE_IP_OR_HOSTNAME' IDENTIFIED BY '<span class="GINGER_SOFATWARE_noSuggestion GINGER_SOFATWARE_correct">slavepass</span>';

Step 3: Create/mount a snapshot

Ensure mysql has flushed all data to disk and then lock tables so no writes can occur.

mysql> FLUSH TABLES WITH READ LOCK;

Obtain log coordinates. Record the values of the File and Position fields.

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 1234     |              |                  |
+------------------+----------+--------------+------------------+

Create and mount the snapshot on the master. Because all tables are locked the coordinates obtained above will be consistent with the data in the snapshot.

# hcp -o /dev/sda2

… where /dev/sda2 is the device containing the filesystem which houses the MySQL databases to be replicated. Watch the output for the resulting mount point. This process should take mere seconds.

Release locks on the tables. This will return operation on the master to normal.

mysql> UNLOCK TABLES;

Step 4: Shutdown the slave’s mysqld and copy the data

Run these commands on the slave:

# /etc/init.d/mysql stop
# rm -rf /var/lib/mysql
# rsync -avz root@MASTER_IP_OR_HOST:/var/hotcopy/sda2_hcp1/lib/mysql /var/lib/

… where /var/lib/mysql is an example path to MySQL’s data.

Step 5: Unmount the snapshot on the master

# hcp -r /dev/hcp1

Step 6: Configure the slave’s identity and start MySQL

Edit /etc/mysql/my.cnf on the slave and set a server id.

[mysqld]
server-id=20

# /etc/init.d/mysql start

Step 7: Configure and start slave

Now it’s time to point the slave at the master and start replication. The MASTER_LOG_FILE and MASTER_LOG_POS should be set to the File and Position fields recorded in Step 3.

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='MASTER_IP_OR_HOST',
    ->     MASTER_USER='repl',
    ->     MASTER_PASSWORD='slavepass',
    ->     MASTER_LOG_FILE='mysql-bin.000002',
    ->     MASTER_LOG_POS=1234;

mysql> START SLAVE;

Conclusion

At this point replication should be running and the only major service interruption was that writes were blocked for a short period on the master.

There’s nothing fundamentally different in the finished product between replication setup in this fashion and a more typical dump-and-copy process. That means monitoring and maintenance should be quite standard.