Slave SQL_THREAD如何重放Relay log
复制的介绍：
根据日志定义的模式不一样，可以分为:Statement(SBR)模式，Row（RBR）格式或者是MIXED格式，记录最小的单位是一个Event，binlog日志前4个字节是一个magic number,接下来19个字节记录Format desc evnet:FDE。MySQL5.6版本增加了GTID复制。
下面对三种binlog格式的优缺点：
Statement（基于语句级的复制）：
优点：
1）Binlog文件较小
2）日志是包含用户执行的原始SQL，方便统计和审计
3）出现最早binlog兼容较好
4）Binlog方便阅读，方便故障修复
缺点：
1）存在安全隐患，可能导致主从数据不一致
2）对一些系统函数不能准复制或是不能复制,例如
Load_file()
uuid()
User()
Found_rows()
Sysdate()
Row格式（记录了行的修改情况）：
优点：
1）相比Statement更安全的复制格式
2）在某些情况下复制速度更快（SQL复杂，表有主键的时候）
3）系统的特殊函数也可以复制
4）更少的锁
缺点：
1）Binary log比较大 （mysql-5.6支持binlog_row_image）
2）单语句更新或者删除表的行数过多，会形成大量binlog
3）无法从Binlog看见用户执行的SQL （mysql-5.6增加了一个新的Event: binlog_row_query_log_events,记录用户的query）
MIXED格式：
1）混合使用ROW和STATEMENT模式。对于DDL记录会STATEMENT，
2）如果使用innodb表，事务级别使用了READ COMMITED 或READ UNCOMMITTED日志级别只能使用Row格式
3）但在使用ROW格式中的DDL语句还是会记录来STATEMENT格式
4）MIXED模式，那么在以下几种情况下会自动将binlog的模式，由SBR模式改成RBR模式。
1、当DML语句更新一个NDB表时
2、当函数中包含UUID（）时
3、2个及以上包含AUTO_INCREMENT字段的表被更新时
4、任何INSERT DELAYED语句时
5、用UDF时
6、视图中必须要求使用RBR时，例如创建视图是使用了UUID（）函数
下面我们看幅图，再进行举例子说明：（基于ROW格式的复制流程）
针对这个图，我们以下进行这四种测试：
一、表有主键索引
二、表有唯一键索引
三、表有普通索引
四、表没有任何索引
测试一：表有主键
在Master上创建tb1表，有主键：
<master>(root@localhost) [xuanzhi]> SHOW variables like 'binlog_format'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` tinyint(4) NOT NULL, `sex` tinyint(4) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | innodb | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | | 50 | codis | 30 | 1 | +----+--------+-----+-----+ 5 rows in set (0.00 sec) <master>(root@localhost) [xuanzhi]>
在slave上模拟数据不一致，把第一条数据修改下：
Database changed <slave>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | innodb | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | | 50 | codis | 30 | 1 | +----+--------+-----+-----+ 5 rows in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> update tb1 set name='innosql' where id=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 <slave>(root@localhost) [xuanzhi]> select * from tb1; +----+---------+-----+-----+ | id | name | age | sex | +----+---------+-----+-----+ | 10 | innosql | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | | 50 | codis | 30 | 1 | +----+---------+-----+-----+ 5 rows in set (0.00 sec) <slave>(root@localhost) [xuanzhi]>
这时主从数据已经不一致了，在master执行update操作会不会报1032没有找到记录的错误呢，我们试试：
<master>(root@localhost) [xuanzhi]> update tb1 set name='oracle' where id=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 <master>(root@localhost) [xuanzhi]>
回Slave库上查看同步是否有问题：
<slave>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | oracle | 20 | 0 | +----+--------+-----+-----+ 1 row in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 1632 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 1778 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes
可以看到数据是正常复制的，而且同步没有断开。
测试二：有唯一索引
查看表结构，在id列上是有唯一索引的：
<master>(root@localhost) [xuanzhi]> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` tinyint(4) NOT NULL, `sex` tinyint(4) NOT NULL, UNIQUE KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]>
在从库修改一行数据，模拟数据不一致：
<slave>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | oracle | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | | 50 | codis | 30 | 1 | +----+--------+-----+-----+ 5 rows in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> update tb1 set name='python' where id=10; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 <slave>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | python | 20 | 0 | +----+--------+-----+-----+ 1 row in set (0.00 sec) <slave>(root@localhost) [xuanzhi]>
在Master进行update操作：
<master>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | oracle | 20 | 0 | +----+--------+-----+-----+ 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]> update tb1 set name='java' where id=10; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 <master>(root@localhost) [xuanzhi]>
回slave库查看是否出现主从断开的可能：
<slave>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+------+-----+-----+ | id | name | age | sex | +----+------+-----+-----+ | 10 | java | 20 | 0 | +----+------+-----+-----+ 1 row in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 2035 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 2181 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: Yes
可以看到当主从数据不一到的时候，基于唯一键更新，同步也是正常的，数据也是同步过来的。
测试三：当表只有普通索引
在master上看到表只有普通索引：
<master>(root@localhost) [xuanzhi]> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` tinyint(4) NOT NULL, `sex` tinyint(4) NOT NULL, KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]>
在slave库上模拟数据不一致的情况：
<slave>(root@localhost) [xuanzhi]> update tb1 set name='php' where id=10; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 <slave>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+------+-----+-----+ | id | name | age | sex | +----+------+-----+-----+ | 10 | php | 20 | 0 | +----+------+-----+-----+ 1 row in set (0.00 sec) <slave>(root@localhost) [xuanzhi]>
回到master更新一条记录看看：
<master>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+------+-----+-----+ | id | name | age | sex | +----+------+-----+-----+ | 10 | java | 20 | 0 | +----+------+-----+-----+ 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]> update tb1 set name='nosql' where id=10; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 <master>(root@localhost) [xuanzhi]>
<slave>(root@localhost) [xuanzhi]> select * from tb1 where id=10; +----+------+-----+-----+ | id | name | age | sex | +----+------+-----+-----+ | 10 | php | 20 | 0 | +----+------+-----+-----+ 1 row in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 2426 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 2369 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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 xuanzhi.tb1; Can't find record in 'tb1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000012, end_log_pos 2399
我们查看一下Binlog做了什么操作：
[root@localhost ~]# mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=2223 --stop-position=2399 /data/mysql/data/mysql-bin.000012 /*!40019 SET @@session.max_insert_delayed_threads=0*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 2223 #150626 9:12:20 server id 1 end_log_pos 2294 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1435281140/*!*/; SET @@session.pseudo_thread_id=14/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 2294 # at 2344 #150626 9:12:20 server id 1 end_log_pos 2344 Table_map: `xuanzhi`.`tb1` mapped to number 45 #150626 9:12:20 server id 1 end_log_pos 2399 Update_rows: table id 45 flags: STMT_END_F ### UPDATE xuanzhi.tb1 ### WHERE ### @1=10 ### @2='java' ### @3=20 ### @4=0 ### SET ### @1=10 ### @2='nosql' ### @3=20 ### @4=0 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; [root@localhost ~]#
可以看到，update语句执行的条件大致是这样的：where id=xx and name=xx and age=xx and sex=xx，当这条语句在slave上被执行时，slave库上发现id=10 name=java...这条记录不存在，所以报了1032错误。（后来我看了有主键和唯一索引的binlog日志信息，也是跟普通索引一样的，只是有主键的和唯一索引的时候不会把这条记录的数据都检查一遍）
测试四：表没有索引
在master查看表结构，表是没有索引的：
<master>(root@localhost) [xuanzhi]> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL, `name` varchar(20) NOT NULL, `age` tinyint(4) NOT NULL, `sex` tinyint(4) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) <master>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | nosql | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | +----+--------+-----+-----+ 4 rows in set (0.00 sec) <master>(root@localhost) [xuanzhi]>
在slave上修改数据，模拟主从数据不一致：
<slave>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | nosql | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | +----+--------+-----+-----+ 4 rows in set (0.00 sec) <slave>(root@localhost) [xuanzhi]> update tb1 set name='zabbix' where id=10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 <slave>(root@localhost) [xuanzhi]>
回到master库执行update操作：
<master>(root@localhost) [xuanzhi]> update tb1 set name='imysql' where id=10; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 <master>(root@localhost) [xuanzhi]> select * from tb1; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 10 | imysql | 20 | 0 | | 20 | myisam | 22 | 1 | | 30 | memory | 22 | 1 | | 40 | redis | 32 | 0 | +----+--------+-----+-----+ 4 rows in set (0.00 sec) <master>(root@localhost) [xuanzhi]>
回到slave查看同步情况：
<slave>(root@localhost) [xuanzhi]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.132 Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000012 Read_Master_Log_Pos: 2914 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 346 Relay_Master_Log_File: mysql-bin.000012 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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 xuanzhi.tb1; Can't find record in 'tb1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000012, end_log_pos 2887
可以看到，当没有索引或者索引是普通索引的时候，Slave SQL_THREAD线程是全表扫描匹配记录，当全表扫描匹配记录时，Relay log在回放的时候发现没有这记录，所以同步断开了。
我们先了解下InnoDB引擎表的一些关键特征：
- InnoDB引擎表是基于B+树的索引组织表(IOT)；
- 每个表都需要有一个聚集索引(clustered index)；
- 所有的行记录都存储在B+树的叶子节点(leaf pages of the tree)；
- 基于聚集索引的增、删、改、查的效率相对是最高的；
- 如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；
- 如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；
- 如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。
综上总结，如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话，这时候存取效率是最高的，也就是下面这几种情况的存取效率最高：
- 使用自增列(INT/BIGINT类型)做主键，这时候写入顺序是自增的，和B+数叶子节点分裂顺序一致；
- 该表不指定自增列做主键，同时也没有可以被选为主键的唯一索引(上面的条件)，这时候InnoDB会选择内置的ROWID作为主键，写入顺序和ROWID增长顺序一致；
- 除此以外，如果一个InnoDB表又没有显示主键，又有可以被选择为主键的唯一索引，但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况)，该表的存取效率就会比较差。
总结：
一、 每种Binlog格式都有自己的优缺点，如果有数据复制的环境，建议Binlog使用ROW的格式
二、Innodb是索引组织表，建议在设置表的时候都尽管设置有主键，如果没有主键，在插入大数据的时候，会导致严重的主从延时
三、如果表没有主键索引或者唯一键索引，Slave回放Relay log时会全表扫描匹配记录，这速度会非常慢。
参考资料：
http://17173ops.com/2014/09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml?utm_source=tuicool
http://www.cnblogs.com/LMySQL/p/4514554.html
|
作者：陆炫志 出处：xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111 您的支持是对博主最大的鼓励，感谢您的认真阅读。本文版权归作者所有，欢迎转载，但请保留该声明。
|
