At the office a while back I was experimenting with techniques to initialize MySQL replication for both InnoDB and MyISAM tables without significant downtime. The MySQL systems in question didn't use LVMs and the idea of locking all tables and performing a backup to ship to the slave simply takes far too long. The method that I ultimately ended up adding to my toolbelt was an adaptation of the process outlined in this article by Badan Sergiu which uses a tool from Idera called R1Soft Hot Copy for Linux.
R1Soft Hot Copy
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.
Motivation and Caveats
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 and that they've read the source article mentioned above.
Also keep in mind that R1Soft Hot Copy is a Linux utility making this article not directly applicable to other operating systems.
A central theme in Badan Sergiu's article was to avoid locking tables (or using lvm). 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
# hcp-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.
On the master create a user specifically to be used for replication.
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'SLAVE_IP_OR_HOSTNAME' IDENTIFIED BY 'slavepass';
Step 3: Create/mount a snapshot
Ensure mysql has flushed all data to disk and then lock tables so no writes can occurr.
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.
# /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;
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.
Also, thanks Badan Sergiu for posting the original article. It helped me immensely.
Sun Nov 18 2012 00:00:00 GMT+0000 (UTC)