MySQL主备复制数据不一致的情况
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为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)
本文永久更新链接地址：
评论暂时关闭