MySQL複製應用中繼日誌解析

作者：沃趣科技高階資料庫專家邱文輝

1、前言

SQL執行緒應用中繼日誌，在binlog_format是row格式的時候，是居於主鍵更新，下面結合一張圖來證明

從一個大神那邊得到一張圖片，SQL執行緒應用中繼日誌流程，下面就實驗驗證一下:(PS,我個人認為這張圖binlog_format為ROW格式是正確的)

2.驗證有PK表情況

在主庫建立表結構

CREATE TABLE `table_pk` (
`id` int(11) NOT NULL ,
`name` varchar(20) NOT NULL ,
`age` tinyint NOT NULL ,
`sex` tinyint NOT NULL COMMENT '0,man,1,woman' ,

PRIMARY KEY (`id`)
) ENGINE=InnoDB;

插入測試資料

insert into table_pk (`id`,`name`,`age`,`sex`) values(111,'zhangsan',20,0);
insert into table_pk (`id`,`name`,`age`,`sex`) values(222,'lisi',22,1);
insert into table_pk (`id`,`name`,`age`,`sex`) values(333,'wangwu',22,1);
insert into table_pk (`id`,`name`,`age`,`sex`) values(444,'lilei',32,0);
insert into table_pk (`id`,`name`,`age`,`sex`) values(555,'hanmeimei',30,1);

(dg6)root@localhost [(none)]> use mytest;

(dg6)root@localhost [mytest]> select * from table_pk;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg6)root@localhost [mytest]> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
row in set (0.00 sec)

那麼我們去從庫看看
(dg7)root@localhost [mytest]> select * from table_pk;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> show global variables like '%binlog_format%';
+--------------------------+-------------------+
| Variable_name | Value |
+--------------------------+-------------------+
| binlog_format | ROW |
+--------------------------+-------------------+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 4469
Relay_Log_File: dg7-relay-bin.000002
Relay_Log_Pos: 4681
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4469
Relay_Log_Space: 4883
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-17
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-9,
b888e1ea-9739-11e4-a24e-000c29b24887:1-17
Auto_Position: 1
row in set (0.00 sec)

資料是複製過來的，MySQL主從複製是正常的，那麼我們為了驗證MySQL複製SQL執行緒是居於剛才那張圖的流程，有主鍵，就按主鍵更新匹配更新記錄。

那麼我們在從庫修改一行資料，故意製造不一致。

(dg7)root@localhost [mytest]> UPDATE `table_pk` SET `name`='laowang' WHERE `id`=333；
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

(dg7)root@localhost [mytest]> select * from table_pk;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | laowang | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

這時候主從資料不一致了
主庫
(dg6)root@localhost [mytest]> select * from table_pk where id=333;
+-----+--------+-----+-----+
| id | name | age | sex |
+-----+--------+-----+-----+
| 333 | wangwu | 22 | 1 |
+-----+--------+-----+-----+
row in set (0.00 sec)

從庫

(dg7)root@localhost [mytest]> select * from table_pk where id=333;
+-----+---------+-----+-----+
| id | name | age | sex |
+-----+---------+-----+-----+
| 333 | laowang | 22 | 1 |
+-----+---------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

那麼，我們在主庫更新一行資料。
(dg6)root@localhost [mytest]> UPDATE `table_pk` SET `name`='wangzi' WHERE `id`=333;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg6)root@localhost [mytest]> select * from table_pk where id=333;
+-----+--------+-----+-----+
| id | name | age | sex |
+-----+--------+-----+-----+
| 333 | wangzi | 22 | 1 |
+-----+--------+-----+-----+
row in set (0.00 sec)

我們來看一下從庫狀態，是不是主庫的更新給複製過來了，見證奇蹟的時候到了
###############################################
(dg7)root@localhost [mytest]> select * from table_pk where id=333;
+-----+---------+-----+-----+
| id | name | age | sex |
+-----+---------+-----+-----+
| 333 | laowang | 22 | 1 |
+-----+---------+-----+-----+
row in set (0.00 sec)
######################### 神奇的是主庫的更新過來了#############################################

(dg7)root@localhost [mytest]> select * from table_pk where id=333;
+-----+--------+-----+-----+
| id | name | age | sex |
+-----+--------+-----+-----+
| 333 | wangzi | 22 | 1 |
+-----+--------+-----+-----+
row in set (0.00 sec)

#########################那麼看一下MySQL主從複製狀態看看，也是正常的######################
(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 5249
Relay_Log_File: dg7-relay-bin.000002
Relay_Log_Pos: 5461
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5249
Relay_Log_Space: 5663
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-20
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-11,
b888e1ea-9739-11e4-a24e-000c29b24887:1-20
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

3.驗證沒有索引的情況

主庫建立表和插入記錄

CREATE TABLE `table_index` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint(4) NOT NULL,
`sex` tinyint(4) NOT NULL COMMENT '0,man,1,woman'
) ENGINE=InnoDB

(dg6)root@localhost [mytest]> select * from table_index;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg6)root@localhost [mytest]>

從庫看看
(dg7)root@localhost [mytest]> select * from table_index;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

我們在從庫繼續搞破壞，把name為lisi的age修改為33，這時候主從已經不一致了。
(dg7)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
+-----+------+-----+-----+
| 222 | lisi | 22 | 1 |
+-----+------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> update table_index set age=33 where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg7)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+---------+-----+-----+
| id | name | age | sex |
+-----+---------+-----+-----+
| 222 | lisi | 33 | 1 |
+-----+---------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

那麼我們還是在主庫更新一下記錄。把lisi的age修改成30，看看從庫能不能更新過來
(dg6)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
+-----+------+-----+-----+
| 222 | lisi | 22 | 1 |
+-----+------+-----+-----+
row in set (0.00 sec)

(dg6)root@localhost [mytest]> update table_index set age=30 where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg6)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
+-----+------+-----+-----+
| 222 | lisi | 30 | 1 |
+-----+------+-----+-----+
row in set (0.00 sec)

(dg6)root@localhost [mytest]>

回到從庫看看，資料沒有更新過來，lisi的年齡還是33，這時候主從複製也是異常的,提示1032錯誤（找不到記錄）
(dg7)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
+-----+------+-----+-----+
| 222 | lisi | 33 | 1 |
+-----+------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 7376
Relay_Log_File: dg7-relay-bin.000003
Relay_Log_Pos: 724
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table mytest.table_index; Can't find record in 'table_index', Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 7345
Skip_Counter: 0
Exec_Master_Log_Pos: 7112
Relay_Log_Space: 8090
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table mytest.table_index; Can't find record in 'table_index', Error_code: 1032; Corrupted replication event was detected, Error_code: 1610; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 7345
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 150425 08:30:49
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-28
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-14,
b888e1ea-9739-11e4-a24e-000c29b24887:1-27
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

4.驗證有唯一索引情況

測試方法都一樣，下面步驟我都就貼結果了。（核心思想就是，從庫先修改記錄，做成主從資料不一致這種情況，然後主庫再更新，看看從庫有沒有同步主庫記錄）
(dg6)root@localhost [mytest]> select * from table_index;
+-----+-----+-----------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+-----------+-----+-----+
| 111 | 1 | zhangsan | 20 | 0 |
| 222 | 2 | lisi | 30 | 1 |
| 333 | 3 | wangzi | 22 | 1 |
| 444 | 4 | lilei | 32 | 0 |
| 555 | 5 | hanmeimei | 30 | 1 |
+-----+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg6)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+--------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+--------+-----+-----+
| 333 | 3 | wangzi | 22 | 1 |
+-----+-----+--------+-----+-----+
row in set (0.00 sec)

(dg6)root@localhost [mytest]> update table_index set name='wangwu' where sid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg6)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+--------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+--------+-----+-----+
| 333 | 3 | wangwu | 22 | 1 |
+-----+-----+--------+-----+-----+
row in set (0.00 sec)

(dg6)root@localhost [mytest]>

從庫看看，能更新過來，而且主從複製狀態是正常的
(dg7)root@localhost [mytest]> select * from table_index;
+-----+-----+-----------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+-----------+-----+-----+
| 111 | 1 | zhangsan | 20 | 0 |
| 222 | 2 | lisi | 30 | 1 |
| 333 | 3 | wangzi | 22 | 1 |
| 444 | 4 | lilei | 32 | 0 |
| 555 | 5 | hanmeimei | 30 | 1 |
+-----+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> update table_index set name='laowang' where sid=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg7)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+---------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+---------+-----+-----+
| 333 | 3 | laowang | 22 | 1 |
+-----+-----+---------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 13038
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 5841
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 13038
Relay_Log_Space: 6615
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-52
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-26,
b888e1ea-9739-11e4-a24e-000c29b24887:1-52
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+--------+-----+-----+
| id | sid | name | age | sex |
+-----+-----+--------+-----+-----+
| 333 | 3 | wangwu | 22 | 1 |
+-----+-----+--------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 13302
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 6105
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 13302
Relay_Log_Space: 6879
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-53
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-26,
b888e1ea-9739-11e4-a24e-000c29b24887:1-53
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

5.驗證有主鍵和有普通索引情況

(dg6)root@localhost [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg6)root@localhost [mytest]> update table_key set name='zhangsir' where age=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg6)root@localhost [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsir | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg6)root@localhost [mytest]>

從庫看看
(dg7)root@localhost [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> desc update table_key set name='xiaozhang' where age=20;
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | table_key | range | age_index | age_index | 1 | const | 1 | Using where |
+----+-------------+-----------+-------+---------------+-----------+---------+-------+------+-------------+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> update table_key set name='xiaozhang' where age=20;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg7)root@localhost [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | xiaozhang | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsir | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 16026
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 8829
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 16026
Relay_Log_Space: 9603
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-63
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-28,
b888e1ea-9739-11e4-a24e-000c29b24887:1-63
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

6.驗證只有普通索引情況

主庫
CREATE TABLE `table_index` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint(4) NOT NULL,
`sex` tinyint(4) NOT NULL COMMENT '0,man,1,woman',
key age_index (`age`)
) ENGINE=InnoDB
(dg6)root@localhost [mytest]>select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsir | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.00 sec)

(dg6)root@localhost [mytest]>update table_key set name='zhaoliu' where age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg6)root@localhost [mytest]>select * from table_key where age=20;
+-----+---------+-----+-----+
| id | name | age | sex |
+-----+---------+-----+-----+
| 111 | zhaoliu | 20 | 0 |
+-----+---------+-----+-----+
row in set (0.00 sec)

從庫
(dg7)root@localhost [mytest]> select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsir | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> update table_key set name='zhangsan' where age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg7)root@localhost [mytest]> select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.00 sec)

(dg7)root@localhost [mytest]> select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.00 sec)
##########################提示1032錯誤，找不到記錄

(dg7)root@localhost [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.106
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: dg6-logbin.000001
Read_Master_Log_Pos: 16463
Relay_Log_File: dg7-relay-bin.000005
Relay_Log_Pos: 8993
Relay_Master_Log_File: dg6-logbin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table mytest.table_key; Can't find record in 'table_key', Error_code: 1032; Column 'name' cannot be null, Error_code: 1048; Column 'age' cannot be null, Error_code: 1048; Column 'sex' cannot be null, Error_code: 1048; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 16432
Skip_Counter: 0
Exec_Master_Log_Pos: 16190
Relay_Log_Space: 10040
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table mytest.table_key; Can't find record in 'table_key', Error_code: 1032; Column 'name' cannot be null, Error_code: 1048; Column 'age' cannot be null, Error_code: 1048; Column 'sex' cannot be null, Error_code: 1048; handler error HA_ERR_END_OF_FILE; the event's master log dg6-logbin.000001, end_log_pos 16432
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b888e1ea-9739-11e4-a24e-000c29b24887
Master_Info_File: /data/mydata/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 150425 09:43:27
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b888e1ea-9739-11e4-a24e-000c29b24887:1-65
Executed_Gtid_Set: a9926b45-975d-11e4-a339-000c29b24888:1-29,
b888e1ea-9739-11e4-a24e-000c29b24887:1-64
Auto_Position: 1
row in set (0.00 sec)

(dg7)root@localhost [mytest]>

7.binlog格式是sbr,mbr格式的時候（PS，因為我使用了GTID，所以找了另外兩臺機測試）

主庫

(dg1)root@127.0.0.1 [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> show global variables like '%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> select * from table_key;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
| 222 | lisi | 22 | 1 |
| 333 | wangwu | 22 | 1 |
| 444 | lilei | 32 | 0 |
| 555 | hanmeimei | 30 | 1 |
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
+-----+-----------+-----+-----+
rows in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> update table_key set name='zhangzong' where age=20;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

從庫看一下
(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsan | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.01 sec)

(dg2)root@127.0.0.1 [mytest]> update table_key set name='zhangsir' where age=20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
+-----+----------+-----+-----+
| 111 | zhangsir | 20 | 0 |
+-----+----------+-----+-----+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=20;
+-----+-----------+-----+-----+
| id | name | age | sex |
+-----+-----------+-----+-----+
| 111 | zhangzong | 20 | 0 |
+-----+-----------+-----+-----+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: dg1.000001
Read_Master_Log_Pos: 3340
Relay_Log_File: mysql3307-relay-bin.000002
Relay_Log_Pos: 3355
Relay_Master_Log_File: dg1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3340
Relay_Log_Space: 3532
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
Master_UUID: fbc1bdf1-829b-11e4-9bdf-000c29b24882
Master_Info_File: /data/3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]>

刪除索引，再測試一下
(dg1)root@127.0.0.1 [mytest]> alter table table_key drop key age_index;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

(dg1)root@127.0.0.1 [mytest]> insert into table_key (id,name,age,sex) values(999,'user1',0);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
(dg1)root@127.0.0.1 [mytest]> insert into table_key (id,name,age,sex) values(999,'user1',38,0);
Query OK, 1 row affected (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> select * from table_key where age=38;
+-----+-------+-----+-----+
| id | name | age | sex |
+-----+-------+-----+-----+
| 999 | user1 | 38 | 0 |
+-----+-------+-----+-----+
row in set (0.00 sec)

(dg1)root@127.0.0.1 [mytest]> update table_key set name='user3' where age=38;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg1)root@127.0.0.1 [mytest]>

從庫看一下
(dg2)root@127.0.0.1 [mytest]> show create table table_key;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| table_key | CREATE TABLE `table_key` (
`id` int(11) NOT NULL,
`name` varchar(20) NOT NULL,
`age` tinyint(4) NOT NULL,
`sex` tinyint(4) NOT NULL COMMENT '0,man,1,woman'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=38;
+-----+-------+-----+-----+
| id | name | age | sex |
+-----+-------+-----+-----+
| 999 | user1 | 38 | 0 |
+-----+-------+-----+-----+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> update table_key set name='user2' where age=38;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=38;
+-----+-------+-----+-----+
| id | name | age | sex |
+-----+-------+-----+-----+
| 999 | user2 | 38 | 0 |
+-----+-------+-----+-----+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> select * from table_key where age=38;
+-----+-------+-----+-----+
| id | name | age | sex |
+-----+-------+-----+-----+
| 999 | user3 | 38 | 0 |
+-----+-------+-----+-----+
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.101
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: dg1.000001
Read_Master_Log_Pos: 3952
Relay_Log_File: mysql3307-relay-bin.000002
Relay_Log_Pos: 3967
Relay_Master_Log_File: dg1.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3952
Relay_Log_Space: 4144
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 12
Master_UUID: fbc1bdf1-829b-11e4-9bdf-000c29b24882
Master_Info_File: /data/3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
row in set (0.00 sec)

(dg2)root@127.0.0.1 [mytest]>

總結

1、SQL執行緒應用中繼日誌，在binlog_format是row格式的時候，確實是居於主鍵更新（Innodb表，如果沒有顯示指定主鍵，如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引），所以這張圖是binlog_format=ROW格式是基本正確的。

2、使用自增列(INT/BIGINT型別)做主鍵，這樣資料分佈基本是有序的與B+數葉子節點分裂順序一致，效能相對比較好；

3、形象的證明了RBR模式下，在有主鍵和唯一鍵的情況下MySQL複製SQL執行緒在應用中繼日誌的時候，鎖範圍比語句模式要少

參考資料：SBR RBR兩種模式的優缺點

SBR和RBR兩種模式各自的優缺點： SBR 的優點：

歷史悠久，技術成熟。
binlog檔案較小。
binlog中包含了所有資料庫更改資訊，可以據此來稽核資料庫的安全等情況。
binlog可以用於實時的還原，而不僅僅用於複製。
主從版本可以不一樣，從伺服器版本可以比主伺服器版本高。

SBR 的缺點：

不是所有的UPDATE語句都能被複制，尤其是包含不確定操作的時候。
呼叫具有不確定因素的 UDF 時複製也可能出問題
使用以下函式的語句也無法被複制： * LOAD_FILE() * UUID() * USER() * FOUND_ROWS() * SYSDATE() (除非啟動時啟用了 --sysdate-is-now 選項)
INSERT ... SELECT 會產生比 RBR 更多的行級鎖
複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的 UPDATE 時，需要比 RBR 請求更多的行級鎖
對於有 AUTO_INCREMENT 欄位的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
對於一些複雜的語句，在從伺服器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響
儲存函式(不是儲存過程)在被呼叫的同時也會執行一次 NOW() 函式，這個可以說是壞事也可能是好事
確定了的 UDF 也需要在從伺服器上執行
資料表必須幾乎和主伺服器保持一致才行，否則可能會導致複製出錯
執行復雜語句如果出錯的話，會消耗更多資源

RBR 的優點：

任何情況都可以被複制，這對複製來說是最安全可靠的
和其他大多數資料庫系統的複製技術一樣
多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多
複製以下幾種語句時的行鎖更少： * INSERT ... SELECT * 包含 AUTO_INCREMENT 欄位的 INSERT * 沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句
執行 INSERT，UPDATE，DELETE 語句時鎖更少
從伺服器上採用多執行緒來執行復製成為可能

RBR 的缺點：

binlog 大了很多
複雜的回滾時 binlog 中會包含大量的資料
主伺服器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生 binlog 的併發寫問題
UDF 產生的大 BLOB 值會導致複製變慢
無法從 binlog 中看到都複製了寫什麼語句
當在非事務表上執行一段堆積的SQL語句時，最好採用 SBR 模式，否則很容易導致主從伺服器的資料不一致情況發生

另外，針對系統庫 mysql 裡面的表發生變化時的處理規則如下：

如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄
如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都採用 SBR 模式記錄

注：採用 RBR 模式後，能解決很多原先出現的主鍵重複問題。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2090565/，如需轉載，請註明出處，否則將追究法律責任。

MySQL複製應用中繼日誌解析

相關文章