The new Development Release for MySQL 5.6 contains a great feature that our users have been asking for for a while (work log 344 first raised in 2010!) – delayed replication.
The concept (and as you’ll see the execution) is extremely simple. If a user makes a mistake on the master – such as dropping some critical tables – then we want to give them the opportunity to recover the situation by using the data held on one of the slaves. The problem is that the slave is busily trying to keep up with the master and in all likelihood will have dropped these tables before the user has time to pull the plug on the replication stream. What this feature does is give the DBA the option to introduce a configurable delay into the replication process so that they have time to cut it off before the mistake is propagated.
This blog explains how this works, how to set that up and then how to bring the slave up to date (to the point in time just before the mistake was made on the master).
To understand how this is implemented, it helps to have a little bit of background on how MySQL replication is implemented. When a change is made on the master, it is applied to the master’s local disk copy and then written to the binary log. The change is then asynchronously (but normally immediately) copied from the master’s binary log to the relay log on the slave; from there an SQL thread on the slave will read the change from the relay log and apply it to the slave’s copy of the data.
This feature works by allowing the user to configure a delay between when the change is applied on the master and when that change is taken from the relay log and applied to the slave. Note that if the master fails during this delay period then the change is not lost as it is has already been safely recorded in the slave’s relay log.
As the delay is implemented on the slave, you are free to use ‘real-time’ replication to one slave (to allow the fastest possible failover if the master fails) and delayed replication to a second slave to guard against user error. This is the setup that this post steps through.
For simplicity, all three MySQL Servers will be run on a single host but each uses a different port number as shown in the diagram. “slave” will apply changes as quickly as it can while “slave2” will introduce a delay when applying changes from its relay log.
Setting up the first slave is very standard:
|
1
2
3
|
<span style="color: #333399;">master> CREATE USER repl_user@localhost;</span> <span style="color: #333399;">master> GRANT REPLICATION SLAVE ON *.* TO </span> <span style="color: #333399;"> repl_user@localhost IDENTIFIED BY 'pw';</span> |
|
1
2
3
4
5
|
<span style="color: #ff6600;">slave> CHANGE MASTER TO</span> <span style="color: #ff6600;"> -> MASTER_HOST = 'localhost',</span> <span style="color: #ff6600;"> -> MASTER_PORT = 3306,</span> <span style="color: #ff6600;"> -> MASTER_USER = 'repl_user',</span> <span style="color: #ff6600;"> -> MASTER_PASSWORD = 'pw';</span> |
|
1 |
<span style="color: #ff6600;">slave> start slave;</span> |
|
1
2
3
4
5
6
|
<span style="color: #008000;">slave2> CHANGE MASTER TO</span> <span style="color: #008000;"> -> MASTER_HOST = 'localhost',</span> <span style="color: #008000;"> -> MASTER_PORT = 3306,</span> <span style="color: #008000;"> -> MASTER_USER = 'repl_user',</span> <span style="color: #008000;"> -> MASTER_PASSWORD = 'pw',</span> <span style="color: #008000;"> -> <strong>MASTER_DELAY = 20</strong>;</span> |
|
1 |
<span style="color: #008000;">slave2> START SLAVE;</span> |
|
1
2
|
<span style="color: #333399;">master> CREATE DATABASE clusterdb;USE clusterdb;</span> <span style="color: #333399;">master> CREATE TABLE towns (Town VARCHAR(20));</span> |
|
1 |
<span style="color: #333399;">master> INSERT INTO towns VALUES ("Maidenhead"),("Bray");</span> |
|
1
2
3
4
5
6
7
|
<span style="color: #ff6600;"><span>slave> SELECT * FROM towns; </span><span> +------------+ </span><span> | Town | </span><span> +------------+ </span><span> | Maidenhead | </span><span> | Bray | </span><span> +------------+</span></span> |
|
1
2
3
4
5
6
7
8
9
10
|
<span style="color: #008000;">slave2> SELECT * FROM towns;</span> <span style="color: #008000;"> Empty set (0.00 sec)</span> <span style="color: #008000;">slave2> SELECT * FROM towns; </span><span style="color: #008000;"> +------------+ </span><span style="color: #008000;"> | Town | </span><span style="color: #008000;"> +------------+ </span><span style="color: #008000;"> | Maidenhead | </span><span style="color: #008000;"> | Bray | </span><span style="color: #008000;"> +------------+</span> |
|
1
2
|
<span style="color: #333399;">master> INSERT INTO towns VALUES ("Cookham"),("Marlow"); master> DROP TABLE towns;</span> |
|
1
2
|
<span style="color: #ff6600; padding-left: 30px;">slave> SELECT * FROM tables; ERROR 1146 (42S02): Table 'clusterdb.tables' doesn't exist</span> |
|
1
2
3
4
5
6
7
8
|
<span style="color: #008000;">slave2> STOP SLAVE;</span> <span style="color: #008000;">slave2> SELECT * FROM towns;</span> <span style="color: #008000;"> +------------+</span> <span style="color: #008000;"> | Town |</span> <span style="color: #008000;"> +------------+</span> <span style="color: #008000;"> | Maidenhead |</span> <span style="color: #008000;"> | Bray |</span> <span style="color: #008000;"> +------------+</span> |
This is a good start, while slave has dropped the table, it still exists on slave2. Unfortunately, slave2 is missing the additions to the table that were made just before the mistake was made. The next step is to bring slave 2 almost up to date – stopping just before the table was dropped. To do this we need to find the position within the master’s binary log just before the table was dropped – this can be done using the SHOW BINLOG EVENTS command on the master. Once we have that position (file-name + position) we can tell slave 2 to catch up just to that point using START SLAVE UNTIL
|
1 |
<span style="color: #333399;">master> SHOW BINLOG EVENTSG</span> |
|
1 |
<span style="color: #333399;">....</span> |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
<span style="color: #333399;">*************************** 10. row ***************************</span> <span style="color: #333399;"> Log_name: ws2-bin.000001</span> <span style="color: #333399;"> Pos: 842</span> <span style="color: #333399;"> Event_type: Query</span> <span style="color: #333399;"> Server_id: 1</span> <span style="color: #333399;"> End_log_pos: 957</span> <span style="color: #333399;"> Info: use `clusterdb`; INSERT INTO towns VALUES ("Cookham"),("Marlow")</span> <span style="color: #333399;"> *************************** 11. row ***************************</span> <span style="color: #333399;"> Log_name: ws2-bin.000001</span> <span style="color: #333399;"> Pos: 957</span> <span style="color: #333399;"> Event_type: Xid</span> <span style="color: #333399;"> Server_id: 1</span> <span style="color: #333399;"> End_log_pos: 984</span> <span style="color: #333399;"> Info: COMMIT /* xid=32 */</span> <span style="color: #333399;"> *************************** 12. row ***************************</span> <span style="color: #333399;"> Log_name: <strong>ws2-bin.000001</strong></span> <span style="color: #333399;"> Pos: <strong>984</strong></span> <span style="color: #333399;"> Event_type: Query</span> <span style="color: #333399;"> Server_id: 1</span> <span style="color: #333399;"> End_log_pos: 1096</span> <span style="color: #333399;"> Info: use `clusterdb`; <strong>DROP TABLE `towns`</strong> /* generated by server */</span> |
|
1
2
3
|
<span style="color: #008000;">slave2> START SLAVE UNTIL</span> <span style="color: #008000;"> -> MASTER_LOG_FILE='ws2-bin.000001',</span> <span style="color: #008000;"> -> MASTER_LOG_POS=984;</span> |
|
1
2
3
4
5
6
7
8
9
|
<span style="color: #008000;">slave2> SELECT * FROM towns;</span> <span style="color: #008000;"> +------------+</span> <span style="color: #008000;"> | Town |</span> <span style="color: #008000;"> +------------+</span> <span style="color: #008000;"> | Maidenhead |</span> <span style="color: #008000;"> | Bray |</span> <span style="color: #008000;"> | Cookham |</span> <span style="color: #008000;"> | Marlow |</span> <span style="color: #008000;"> +------------+</span> |
Success! Now slave2 contains exactly the data we need. After this it’s up to you what to do next; typically this could involve promoting slave2 to be the new master.
If you want to try this out for yourselves then you can download the MySQL 5.6 Milestone Development Release from dev.mysql.com (select the Development Maintenance Release sub-tab to get MySQL 5.6).