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

23rwd

在主备复制架构下，mysql5.6默认的复制的方式是SBR（基于SQL语句的复制），如果主从库的上下文不一致（如时间不一样、主从库发生了延迟），使用了sysdate()函数，UUID()函数,user()函数，将出现主库与备库数据不一致。假设备库数据比主库多的情况，进行数据更新，也将产生不一致。

结论：

从下面的实验可以看出，主备复制，复制的方式不推荐使用statement,使用statement,在上下文不一致的情况，会造成主备复制，产生数据不一致。

1、实验环境：










2、实验一：主从库时间不一样的情况

    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)

    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、备库操作：

stopslave;

    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为10

mysql>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 exists

mysql> 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)