Slave SQL_THREAD如何重放Relay log

   优点：

    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 ~]# 

View Code

可以看到，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 

                  

	 	
                    

                        

                            

                                
< 上一篇
linux操作系统-源码包安装mysql
                            
                            

                                 
下一篇 >
MSSQL - 多表查询