MySQL主备复制数据不一致的情况

在主备复制架构下，mysql5.6默认的复制的方式是SBR（基于SQL语句的复制），如果主从库的上下文不一致（如时间不一样、主从库发生了延迟），使用了sysdate（）函数，UUID（）函数,user（）函数，将出现主库与备库数据不一致。假设备库数据比主库多的情况，进行数据更新，也将产生不一致。结论：从下面的实验可以看出，主备复制，复制的方式不推荐使用statement,使用statement,在上下文不一致的情况，会造成主备复制，产生数据不一致。1、实验环境：2、实验一：主从库时间不一样的情况1.1、主库操作：
（1）查看时间mysql>select sysdate（）;+---------------------+|sysdate（） |+---------------------+| 2015-12-07 13:46:55 |+---------------------+1row in set （0.00 sec）（2）查看使用哪种方式进行复制mysql>show variables like "binlog_format";+---------------+-----------+|Variable_name | Value |+---------------+-----------+|binlog_format | STATEMENT |+---------------+-----------+1row in set （0.00 sec）STATEMENT表示基于sql语句的复制。（3）创建表createtable temporal_test（ d DATE,dt DATETIME,t TIME,ts TIMESTAMP）;（4）插入数据mysql>insert into temporal_test values（sysdate（）,sysdate（）,sysdate（）,sysdate（））;QueryOK, 1 row affected, 2 warnings （0.01 sec）（5）查询结果 mysql> select * from temporal_test;+------------+---------------------+----------+---------------------+|d | dt | t | ts |+------------+---------------------+----------+---------------------+| 2015-12-07 | 2015-12-07 13:52:33 | 13:52:33 | 2015-12-07 13:52:33|+------------+---------------------+----------+---------------------+1row in set （0.00 sec）1.2、备库操作：
（1）查询当前时间mysql>select sysdate（）;+---------------------+|sysdate（） |+---------------------+| 2014-01-10 01:39:41 |+---------------------+1row in set （0.00 sec）（2）查看使用哪种方式进行复制mysql>show variables like "binlog_format";+---------------+-----------+|Variable_name | Value |+---------------+-----------+|binlog_format | STATEMENT |+---------------+-----------+1row in set （0.00 sec）（3）查询结果mysql>select * from temporal_test;+------------+---------------------+----------+---------------------+|d | dt | t | ts |+------------+---------------------+----------+---------------------+| 2014-01-10 | 2014-01-10 01:45:19 | 01:45:19 | 2014-01-10 01:45:19|+------------+---------------------+----------+---------------------+1row in set （0.00 sec）结论：使用mysql主备架构，复制方式使用statement，一定要进行时间同步，否则会出现主备库不同步。3、实验二：主备库发生延迟情况备库暂时停止slave复制，来模拟主备库发生延迟。具体操作：1.1、备库操作：
stopslave;1.2、主库操作：
（1）查询当前时间mysql> select sysdate（）;+---------------------+|sysdate（） |+---------------------+|2015-12-07 14:22:15 |+---------------------+1row in set （0.00 sec）（2）插入数据insertinto temporal_test values（sysdate（）,sysdate（）,sysdate（）,sysdate（））;（3）查询插入结果mysql>select * from temporal_test;+------------+---------------------+----------+---------------------+|d | dt | t | ts |+------------+---------------------+----------+---------------------+|2015-12-07 | 2015-12-07 13:52:33 | 13:52:33 | 2015-12-07 13:52:33 || 2015-12-07 | 2015-12-07 14:23:17 | 14:23:17 | 2015-12-07 14:23:17|+------------+---------------------+----------+---------------------+2rows in set （0.00 sec）3.3、从库操作：（1）查询当前时间mysql> select sysdate（）;+---------------------+|sysdate（） |+---------------------+|2015-12-07 14:22:15 |+---------------------+1row in set （0.02 sec）（2）过一段时间后startslave;（3）查询插入结果mysql>select * from temporal_test;+------------+---------------------+----------+---------------------+|d | dt | t | ts |+------------+---------------------+----------+---------------------+|2014-01-10 | 2014-01-10 01:45:19 | 01:45:19 | 2014-01-10 01:45:19 || 2015-12-07 | 2015-12-07 14:24:38 | 14:24:38 | 2015-12-07 14:24:38|+------------+---------------------+----------+---------------------+2rows in set （0.00 sec）4、实验三：假设备库数据比主库多的情况，进行数据更新，将产生不一致。4.1、主库操作：创建表t,插入数据，查看结果mysql>create table t（id int,name varchar（20））;QueryOK, 0 rows affected （0.01 sec） mysql>insert into t values（1,"xiao"）;QueryOK, 1 row affected （0.00 sec） mysql>insert into t values（2,"xiao"）;QueryOK, 1 row affected （0.02 sec） mysql>insert into t values（3,"xiao"）;QueryOK, 1 row affected （0.01 sec） mysql>insert into t values（1,"xiao"）;QueryOK, 1 row affected （0.01 sec） mysql>select * from t;+------+------+|id | name |+------+------+| 1 | xiao || 2 | xiao || 3 | xiao || 1 | xiao |+------+------+4 rowsin set （0.00 sec）4.2、从库操作：查看结果，插入一条数据（使得备库比主库多一条数据）。mysql>select * from t;+------+------+|id | name |+------+------+| 1 | xiao || 2 | xiao || 3 | xiao || 1 | xiao |+------+------+4 rowsin set （0.00 sec） mysql>insert into t values（1,"huang"）;QueryOK, 1 row affected （0.00 sec） mysql>select * from t;+------+-------+|id | name |+------+-------+| 1 | xiao || 2 | xiao || 3 | xiao || 1 | xiao || 1 | huang |+------+-------+5 rowsin set （0.00 sec）4.3、主库操作：把id为1更新为id为10mysql>update t set id=10 where id=1;mysql>select * from t;+------+------+|id | name |+------+------+| 10 | xiao || 2 | xiao || 3 | xiao || 10 | xiao |+------+------+4 rowsin set （0.00 sec）4.4、从库操作：查询更新结果mysql>select * from t;+------+-------+|id | name |+------+-------+| 10 | xiao || 2 | xiao || 3 | xiao || 10 | xiao || 10 | huang |+------+-------+5 rowsin set （0.00 sec）主库更新了两条记录，备库更新了三条记录。从这个实验可以得出，如果主库与备库上下文不一样，那么相同的一条sql在主库与备库执行，产生的效果是不一样，带来了主备数据的不一样。5、实验四、使用UUID（）函数情况，也将使主备库数据不一致5.1、主库操作mysql> showvariables like "binlog_format";+---------------+-----------+| Variable_name |Value |+---------------+-----------+| binlog_format |STATEMENT |+---------------+-----------+1 row in set （0.00sec）
mysql> createtable test（id varchar（100））;Query OK, 0 rowsaffected （0.01 sec）
mysql> insertinto test values（uuid（））;Query OK, 1 rowaffected, 1 warning （0.00 sec）
mysql> select *from test;+--------------------------------------+| id |+--------------------------------------+| 156bb13f-9cb6-11e5-ab3f-000c29133345 |+--------------------------------------+1 row in set （0.00sec）5.2、从库操作mysql> showvariables like "binlog_format";+---------------+-----------+| Variable_name |Value |+---------------+-----------+| binlog_format |STATEMENT |+---------------+-----------+1 row in set （0.00sec）
mysql> select *from test;+--------------------------------------+| id |+--------------------------------------+| 1566e5cb-9cb6-11e5-be5b-000c297f9303 |+--------------------------------------+1row in set （0.00 sec）6、实验六、使用user（）函数情况，也将使主备库数据不一致6.1、主库操作mysql> createtable test（user varchar（100））;ERROR 1050（42S01）: Table "test" already existsmysql> createtable Atest（user varchar（100））;Query OK, 0 rowsaffected （0.05 sec）
mysql> insertinto Atest values（user（））;Query OK, 1 rowaffected, 1 warning （0.01 sec）
mysql> select *from Atest;+----------------+| user |+----------------+| root@localhost |+----------------+1 row in set （0.00sec）6.2、从库操作mysql> select *from Atest;+------+| user |+------+| |+------+1 row in set （0.00sec）
mysql> selectcount（*） from Atest;+----------+| count（*） |+----------+| 1 |+----------+1 row in set （0.00sec）本文永久更新链接地址