這篇文章主要介紹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 模式后，能解決很多原先出現的主鍵重復問題。
以上是“MySQL復制應用中繼日志的示例分析”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
