Setup mySQL replicated slave using r1soft

I have been using r1soft Continuous Data Protection for many years now with the mySQL Addon and wanted to share a trick I have used several times.

For those of us that don’t have the luxury of using all transactional based tables such as InnoDB and the the ability to use the likes of –single-transaction with –master-data we do have an option of using r1soft to avoid lengthy downtimes in the event we have to setup/resetup a slave. You can turn a potential several hour outage in a 30-60 second READ LOCK and perform the rest in the background.

Firstly a quick explanation of the r1soft mySQL addon, the mySQL addon works by issuing a global lock and then flushes changes from memory to disk to ensure consistent snapshots. As it works with the r1soft device driver the process usually takes less than a second making this the perfect method to take backups and have the master position at the time the backup was taken. At the time of writing r1soft does not support the ability to store the master/slave status data although hopefully in the future it will store and display it to avoid having to use the following trick.

We can however work around this by monitoring the r1soft server-log.txt to determine when it has flushed and unlocked the mySQL instance. As it happens very early on in the process we can manually issue a global read lock, get the current position, monitor the server-log.txt until r1soft has performed the snapshot of mySQL and then release the global lock manually.

I have performed an example of this for this article. I started by logging into the CDP interface, browsing to the system in question and getting to the start backup process, having this all filled in and ready to submit speeds the process up. Once this was ready I logged into the database system and issued a global read lock then obtained the current master position,

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+—————–+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+—————–+———–+————–+——————+
| mysql-bin.024272 | 120146094 | | |
+—————–+———–+————–+——————+
1 row in set (0.00 sec)

I then clicked ok under the schedule backup window and started a tail on server-log.txt (usually found in /usr/r1soft/buserver/log) and waited until it reached the mySQL lock, flush and unlock.


2011-01-27 02:44:27,743 INFO: (tid:976019) Starting task 'Backup' for host (xx.xx.xx.xx) with host id (xxxxxxxx)
2011-01-27 02:44:27,771 INFO: (tid:976019) Starting Backup Task.
2011-01-27 02:44:27,772 INFO: (tid:976019) Backing up partition tables.
2011-01-27 02:44:27,773 INFO: (tid:976020) Starting task 'Partition Table Backup' for host (xx.xx.xx.xx) with host id (xxxxxxxx)
2011-01-27 02:44:27,790 INFO: (tid:976020) Starting partition table backup for host (xx.xx.xx.xx) device (/dev/sda) Disk Safe (0) recovery point (6025).
2011-01-27 02:44:27,892 INFO: (tid:976020) AGENT: (Righteous Backup Linux Agent) 1.62.0 build 6386
2011-01-27 02:44:28,019 WARN: (tid:976020) AGENT: allowing control from backup server (xx.xx.xx.xx) with valid RSA key
2011-01-27 02:44:28,059 WARN: (tid:976020) AGENT: sending auth challenge for allowed host at (xx.xx.xx.xx) port (41758)
2011-01-27 02:44:28,108 INFO: (tid:976020) AGENT: host (xx.xx.xx.xx) port (41758) authentication successful
2011-01-27 02:44:28,193 INFO: Agent (xx.xx.xx.xx) authenticated successfully
2011-01-27 02:44:28,267 INFO: (tid:976020) AGENT: Partition Backup request accepted. Starting backup.
2011-01-27 02:44:28,269 INFO: (tid:976020) AGENT: Found 4 partition(s), 1 extended partition(s) and 124 extra sector(s)
2011-01-27 02:44:28,270 INFO: (tid:976020) AGENT: Need to back up 126 sectors
2011-01-27 02:44:28,270 INFO: (tid:976020) Sector size (512) bytes.
2011-01-27 02:44:28,271 INFO: (tid:976020) Number of sectors (126).
--SNIP--
2011-01-27 02:44:32,083 INFO: Sending (1) MySQL instances to agent.
2011-01-27 02:44:32,083 INFO: Sending next MySQL instance to agent.
2011-01-27 02:44:32,117 INFO: (tid:976019) AGENT: Backup request accepted. Starting backup.
2011-01-27 02:44:39,340 INFO: (tid:976019) AGENT: Flushed 1 MySQL instances.
2011-01-27 02:44:39,353 INFO: (tid:976019) AGENT: Flushed 1 MySQL instances.
2011-01-27 02:44:39,354 INFO: (tid:976019) AGENT: Flushed 1 MySQL instances.
2011-01-27 02:44:39,355 INFO: (tid:976019) AGENT: Locked 1 MySQL instances.
2011-01-27 02:44:39,355 INFO: (tid:976019) AGENT: Unlocked 1 MySQL instances.
2011-01-27 02:44:39,356 INFO: (tid:976019) AGENT: Snapshot completed in 0.059 seconds.
2011-01-27 02:44:39,357 INFO: (tid:976019) AGENT: Acquired MySQL lock in 0.025 seconds and held lock for 0.085 seconds on 1 instance.
2011-01-27 02:44:39,357 INFO: (tid:976019) Backup pipeline size (256) blocks.
2011-01-27 02:44:39,358 INFO: (tid:976019) Block size (4096).
2011-01-27 02:44:39,358 INFO: (tid:976019) Partition size (34529701) blocks.
2011-01-27 02:44:39,359 INFO: (tid:976019) Mount Point (/).
2011-01-27 02:44:39,803 INFO: (tid:976019) Network Queue Size: 1280 blocks

Once it has completed the stage in bold I simply released the read lock,

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

I now had a backup in r1soft and had the master position of the time it was taken. Once the backup completed I simply restored the tables I needed to the slave using the restore to an alternate host and started replication using this data.


INFO 01/27/2011 02:44:27 GMT (tid:976019) Starting task 'Backup' for host (xx.xx.xx.xx) with host id (xxxxxxxx)
INFO 01/27/2011 02:51:10 GMT (tid:976019) Backup Task finished.

I restored the backup to the mysql datadir, as you are backing up the master you can skip all the binlogs. After this was restored I started mySQL, changed the master to the details we got from the master status earlier and started the slave.


mysql> CHANGE MASTER to MASTER_HOST='192.168.1.3', MASTER_USER='slave', MASTER_PASSWORD='xxxxxx', MASTER_LOG_FILE='mysql-bin.024272', MASTER_LOG_POS=120146094;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
Seconds_Behind_Master: 0