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)

本文永久更新連結地址：