Reduce mySQL slave setup time with 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)

Select all Open in new window

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.
                      [b]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.[/b]
                      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

Select all Open in new window

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)

Select all Open in new window

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.

Select all Open in new window

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

Select all Open in new window

So, hope you can find benefits with your mysql installation...