Inconsistency of master-slave replication data in MySQL

Inconsistency of master-slave replication data in MySQL

In the master-slave replication architecture, the default replication method of mysql5.6 is SBR (SQL statement-based replication ), if the context of the Master/Slave database is inconsistent (for example, the time is different and the Master/Slave database has a delay), The sysdate () function, UUID () function, and user () function are used, the data of the master database is inconsistent with that of the slave database. When the data in the false device library is more than that in the master database, data is updated, which also produces inconsistency.

Conclusion:

From the experiment below, we can see that statement is not recommended for master-slave replication. statement is used. If the context is inconsistent, it will cause master-slave replication and data inconsistency.

1. experiment environment:

2. Experiment 1: When the Master/Slave database time is different

1.1 master database operations:

(1) view time

Mysql> select sysdate ();

+ --------------------- +

| Sysdate () |

+ --------------------- +

| 13:46:55 |

+ --------------------- +

1row in set (0.00 sec)

(2) Check the method used for replication.

Mysql> show variables like 'binlog _ format ';

+ --------------- + ----------- +

| Variable_name | Value |

+ --------------- + ----------- +

| Binlog_format | STATEMENT |

+ --------------- + ----------- +

1row in set (0.00 sec)

STATEMENT indicates SQL STATEMENT-based replication.

(3) create a table

Createtable temporal_test (d DATE, dt DATETIME, t TIME, ts TIMESTAMP );

(4) Insert data

Mysql> insert into temporal_test values (sysdate (), sysdate ());

QueryOK, 1 row affected, 2 warnings (0.01 sec)

(5) query results

Mysql> select * from temporal_test;

+ ------------ + --------------------- + ---------- + --------------------- +

| D | dt | t | ts |

+ ------------ + --------------------- + ---------- + --------------------- +

| 13:52:33 | 13:52:33 | 13:52:33 |

+ ------------ + --------------------- + ---------- + --------------------- +

1row in set (0.00 sec)

1.2 slave database operations:

(1) query the current time

Mysql> select sysdate ();

+ --------------------- +

| Sysdate () |

+ --------------------- +

| 01:39:41 |

+ --------------------- +

1row in set (0.00 sec)

(2) Check the method used for replication.

Mysql> show variables like 'binlog _ format ';

+ --------------- + ----------- +

| Variable_name | Value |

+ --------------- + ----------- +

| Binlog_format | STATEMENT |

+ --------------- + ----------- +

1row in set (0.00 sec)

(3) query results

Mysql> select * from temporal_test;

+ ------------ + --------------------- + ---------- + --------------------- +

| D | dt | t | ts |

+ ------------ + --------------------- + ---------- + --------------------- +

| 01:45:19 | 01:45:19 | 01:45:19 |

+ ------------ + --------------------- + ---------- + --------------------- +

1row in set (0.00 sec)

Conclusion: Using the mysql master-slave architecture and the replication method using statement requires time synchronization. Otherwise, the master-slave database is not synchronized.

3. Experiment 2: latency of the master and slave Databases

Slave database temporarily stops slave replication to simulate the delay of the master and slave databases.

Specific operations:

1.1 slave database operations:

Stopslave;

1.2 master database operations:

(1) query the current time

Mysql> select sysdate ();

+ --------------------- +

| Sysdate () |

+ --------------------- +

| 14:22:15 |

+ --------------------- +

1row in set (0.00 sec)

(2) Insert data

Insertinto temporal_test values (sysdate (), sysdate ());

(3) query the insert result

Mysql> select * from temporal_test;

+ ------------ + --------------------- + ---------- + --------------------- +

| D | dt | t | ts |

+ ------------ + --------------------- + ---------- + --------------------- +

| 13:52:33 | 13:52:33 | 13:52:33 |

| 14:23:17 | 14:23:17 | 14:23:17 |

+ ------------ + --------------------- + ---------- + --------------------- +

2 rows in set (0.00 sec)

3.3 slave database operations:

(1) query the current time

Mysql> select sysdate ();

+ --------------------- +

| Sysdate () |

+ --------------------- +

| 14:22:15 |

+ --------------------- +

1row in set (0.02 sec)

(2) after a period of time

Startslave;

(3) query the insert result

Mysql> select * from temporal_test;

+ ------------ + --------------------- + ---------- + --------------------- +

| D | dt | t | ts |

+ ------------ + --------------------- + ---------- + --------------------- +

| 01:45:19 | 01:45:19 | 01:45:19 |

| 14:24:38 | 14:24:38 | 14:24:38 |

+ ------------ + --------------------- + ---------- + --------------------- +

2 rows in set (0.00 sec)

4. Experiment 3: If the false device library has more data than the master database, data updates will produce inconsistencies.

4.1 master database operation: Create Table t, insert data, and view results

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 slave database operation: view the result and insert a data record (so that the slave database has one more data record than the master database ).

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, 'huangt ');

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 master database operation: Update id 1 to 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 slave database operation: query update results

Mysql> select * from t;

+ ------ + ------- +

| Id | name |

+ ------ + ------- +

| 10 | xiao |

| 2 | xiao |

| 3 | xiao |

| 10 | xiao |

| 10 | huang |

+ ------ + ------- +

5 rowsin set (0.00 sec)

The master database updates two records, and the slave database updates three records. From this experiment, we can conclude that if the context of the master database is different from that of the slave database, the same SQL statement will be executed in the master database and the slave database, resulting in different results.

5. Experiment 4. Using the UUID () function also causes data inconsistency between the master and slave databases.

5.1 master database operations

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 slave database operations

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. Experiment 6. Using the user () function also causes data inconsistency between the master and slave databases.

6.1 master database operations

Mysql> createtable test (user varchar (100 ));

ERROR 1050 (42S01): Table 'test' already exists

Mysql & gt; 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 slave database operations

Mysql> select * from Atest;

+ ------ +

| User |

+ ------ +

|

+ ------ +

1 row in set (0.00sec)

 

Mysql> selectcount (*) from Atest;

+ ---------- +

| Count (*) |

+ ---------- +

| 1 |

+ ---------- +

1 row in set (0.00sec)

This article permanently updates the link address: