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;
(dg7)root@localhost [mytest]> show global variables like '%binlog_format%';
+--------------------------+-------------------+
| variable_name | value |
| binlog_format | row |
(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
資料是複制過來的，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
| 333 | laowang | 22 | 1 |
這時候主從資料不一緻了
主庫
(dg6)root@localhost [mytest]> select * from table_pk where id=333;
+-----+--------+-----+-----+
| id | name | age | sex |
| 333 | wangwu | 22 | 1 |
從庫
(dg7)root@localhost [mytest]> select * from table_pk where id=333;
+-----+---------+-----+-----+
| id | name | age | sex |
| 333 | laowang | 22 | 1 |
(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
| 333 | wangzi | 22 | 1 |
我們來看一下從庫狀态，是不是主庫的更新給複制過來了，見證奇迹的時候到了
###############################################
######################### 神奇的是主庫的更新過來了#############################################
#########################那麼看一下mysql主從複制狀态看看，也是正常的######################
read_master_log_pos: 5249
relay_log_pos: 5461
exec_master_log_pos: 5249
relay_log_space: 5663
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
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;
(dg6)root@localhost [mytest]>
從庫看看
(dg7)root@localhost [mytest]> select * from table_index;
我們在從庫繼續搞破壞，把name為lisi的age修改為33，這時候主從已經不一緻了。
(dg7)root@localhost [mytest]> select * from table_index where name='lisi';
+-----+------+-----+-----+
| id | name | age | sex |
| 222 | lisi | 22 | 1 |
(dg7)root@localhost [mytest]> update table_index set age=33 where name='lisi';
| 222 | lisi | 33 | 1 |
那麼我們還是在主庫更新一下記錄。把lisi的age修改成30，看看從庫能不能更新過來
(dg6)root@localhost [mytest]> select * from table_index where name='lisi';
(dg6)root@localhost [mytest]> update table_index set age=30 where name='lisi';
| 222 | lisi | 30 | 1 |
回到從庫看看，資料沒有更新過來，lisi的年齡還是33，這時候主從複制也是異常的,提示1032錯誤（找不到記錄）
| 222 | lisi | 33 | 1 |
read_master_log_pos: 7376
relay_log_file: dg7-relay-bin.000003
relay_log_pos: 724
slave_sql_running: no
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
exec_master_log_pos: 7112
relay_log_space: 8090
seconds_behind_master: null
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
slave_sql_running_state:
last_sql_error_timestamp: 150425 08:30:49
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
4.驗證有唯一索引情況
測試方法都一樣，下面步驟我都就貼結果了。（核心思想就是，從庫先修改記錄，做成主從資料不一緻這種情況，然後主庫再更新，看看從庫有沒有同步主庫記錄）
+-----+-----+-----------+-----+-----+
| 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 |
(dg6)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+--------+-----+-----+
| id | sid | name | age | sex |
| 333 | 3 | wangzi | 22 | 1 |
(dg6)root@localhost [mytest]> update table_index set name='wangwu' where sid=3;
query ok, 1 row affected (0.00 sec)
| 333 | 3 | wangwu | 22 | 1 |
從庫看看，能更新過來，而且主從複制狀态是正常的
(dg7)root@localhost [mytest]> update table_index set name='laowang' where sid=3;
(dg7)root@localhost [mytest]> select * from table_index where sid=3;
+-----+-----+---------+-----+-----+
| id | sid | name | age | sex |
| 333 | 3 | laowang | 22 | 1 |
read_master_log_pos: 13038
relay_log_file: dg7-relay-bin.000005
relay_log_pos: 5841
exec_master_log_pos: 13038
relay_log_space: 6615
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
read_master_log_pos: 13302
relay_log_pos: 6105
exec_master_log_pos: 13302
relay_log_space: 6879
retrieved_gtid_set: b888e1ea-9739-11e4-a24e-000c29b24887:1-53
b888e1ea-9739-11e4-a24e-000c29b24887:1-53
5.驗證有主鍵和有普通索引情況
(dg6)root@localhost [mytest]> select * from table_key;
| 666 | lucy | 30 | 1 |
| 777 | lili | 30 | 1 |
| 888 | lintao | 32 | 0 |
(dg6)root@localhost [mytest]> update table_key set name='zhangsir' where age=20;
| 111 | zhangsir | 20 | 0 |
(dg7)root@localhost [mytest]> select * from table_key;
(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 |
(dg7)root@localhost [mytest]> update table_key set name='xiaozhang' where age=20;
query ok, 1 row affected (0.03 sec)
| 111 | xiaozhang | 20 | 0 |
read_master_log_pos: 16026
relay_log_pos: 8829
exec_master_log_pos: 16026
relay_log_space: 9603
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
6.驗證隻有普通索引情況
`sex` tinyint(4) not null comment '0,man,1,woman',
key age_index (`age`)
(dg6)root@localhost [mytest]>select * from table_key where age=20;
+-----+----------+-----+-----+
| id | name | age | sex |
| 111 | zhangsir | 20 | 0 |
(dg6)root@localhost [mytest]>update table_key set name='zhaoliu' where age=20;
| 111 | zhaoliu | 20 | 0 |
(dg7)root@localhost [mytest]> select * from table_key where age=20;
(dg7)root@localhost [mytest]> update table_key set name='zhangsan' where age=20;
| 111 | zhangsan | 20 | 0 |
##########################提示1032錯誤，找不到記錄
read_master_log_pos: 16463
relay_log_pos: 8993
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
exec_master_log_pos: 16190
relay_log_space: 10040
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
last_sql_error_timestamp: 150425 09:43:27
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
7.binlog格式是sbr,mbr格式的時候（ps，因為我使用了gtid，是以找了另外兩台機測試）
(dg1)[email protected] [mytest]> select * from table_key;
(dg1)[email protected] [mytest]> show global variables like '%binlog_format%';
| binlog_format | mixed |
(dg1)[email protected] [mytest]> update table_key set name='zhangzong' where age=20;
從庫看一下
(dg2)[email protected] [mytest]> select * from table_key where age=20;
row in set (0.01 sec)
(dg2)[email protected] [mytest]> update table_key set name='zhangsir' where age=20;
| 111 | zhangzong | 20 | 0 |
(dg2)[email protected] [mytest]> show slave status\g
master_host: 192.168.80.101
master_port: 3307
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
replicate_ignore_db:
exec_master_log_pos: 3340
relay_log_space: 3532
master_server_id: 12
master_uuid: fbc1bdf1-829b-11e4-9bdf-000c29b24882
master_info_file: /data/3307/master.info
retrieved_gtid_set:
executed_gtid_set:
auto_position: 0
(dg2)[email protected]
<b>本文來自雲栖社群合作夥伴“dbgeek”</b>
![SQL優化SQL語句優化的目的[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)