Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL primary and secondary replicate data inconsistencies     - Nginx log cutting and MySQL script regular backup script (Server)

- SQL in the specific internal Oracle process (Database)

- Cacti monitoring service Nginx (Linux)

- Ubuntu 14.10 How to install office suite Calligra Suite 2.8.7 (Linux)

- Upload the project to GitHub, synchronous remote repository Github (Linux)

- Why do you need close contact Rust 1.0 (Programming)

- Java memory area and memory overflow exception (Programming)

- Integrated security administrator Linux accident management (Linux)

- Node.js development environment deployment (Server)

- How to install Nginx on FreeBSD 10.2 as an Apache reverse proxy (Server)

- Kafka + Log4j log implement centralized management (Server)

- How to upgrade the kernel to 3.13.7 on Ubuntu / Linux Mint (Linux)

- Linux dual physical network card set to a virtual NIC (Linux)

- How to track performance issues when using the Unity Game Development Android (Programming)

- KVM installation under CentOS 5.5 (Linux)

- Shell scripts to copy all directories under the current directory of a certain type of file to the same directory (Linux)

- Linux package manager - yum (Linux)

- Linux operating system security settings initial understanding (Linux)

- CentOS 5.5 install ntop (Linux)

- Linux command -nohup & (Linux)

 
         
  MySQL primary and secondary replicate data inconsistencies
     
  Add Date : 2017-01-08      
         
         
         
  In standby replication architecture, mysql5.6 default replication mode is SBR (based on SQL statement replication), if the primary context from inconsistent database (such as time is not the same, the main delays occurred from the library), using sysdate ( ) function, UUID () function, user () function, will appear the main library and library equipment data inconsistencies. Fake device library data than the main library and more cases, data updates, will also be inconsistent.

in conclusion:

As can be seen from the following experiments standby Copy to copy the way not recommended statement, using the statement, in the context of the case of inconsistency will cause the active and standby replicate, resulting in inconsistent data.

1, the experimental environment:

2. Experiment 1: Master from the library time is not the same situation

1.1, the main library operations:


(1) Review time

mysql> select sysdate ();

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

| Sysdate () |

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

| 2015-12-07 13:46:55 |

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

1row in set (0.00 sec)

(2) See which way to copy

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| Binlog_format | STATEMENT |

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

1row in set (0.00 sec)

STATEMENT representation based replication sql statement.

(3) create a table

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

(4) inserting data

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

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

(5) Results

 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 Preparation of the library operation:


(1) query the current time

mysql> select sysdate ();

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

| Sysdate () |

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

| 2014-01-10 01:39:41 |

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

1row in set (0.00 sec)

(2) See which way to copy

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| Binlog_format | STATEMENT |

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

1row in set (0.00 sec)

(3) Results

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)

  Conclusion: AMB architecture mysql, replication using statement, time synchronization must be carried out, there would not synchronized standby database.

3, second experiment: the case of delayed standby database

Standby database temporarily stop slave replication to simulate primary and secondary libraries delayed.

Specific actions:

1.1 Preparation of the library operation:


stopslave;

1.2, the main library operations:


(1) query the current time

mysql> select sysdate ();

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

| Sysdate () |

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

| 2015-12-07 14:22:15 |

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

1row in set (0.00 sec)

(2) insert data

insertinto temporal_test values (sysdate (), sysdate (), sysdate (), sysdate ());

(3) insert the query results

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, from the library operation:

(1) query the current time

mysql> select sysdate ();

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

| Sysdate () |

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

| 2015-12-07 14:22:15 |

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

1row in set (0.02 sec)

(2) over a period of time

startslave;

(3) insert the query results

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, the third experiment: fake device library data than the main library and more cases, data updates, will produce inconsistent.

4.1, the main library: Create table t, inserted into the data, 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, from the library: Review the results, insert a piece of data (library equipment makes one more data than the main library).

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, the main library operations: to update the id id 1 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, from the library Action: Update Query Results

mysql> select * from t;

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

| Id | name |

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

| 10 | xiao |

| 2 | xiao |

| 3 | xiao |

| 10 | xiao |

| 10 | huang |

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

5 rowsin set (0.00 sec)

Main library updates two records, prepared by the library to update the three records. From this experiment it can be concluded if the primary database and standby database context is not the same, then the same one sql in the main library and library equipment to perform, the effect is not the same, bringing standby data is not the same.


5. Experiment 4, using the UUID () function, the standby database will also allow data inconsistency

5.1, the main library 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, from the library operation

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 user () function, the standby database will also allow data inconsistency

6.1, the main library operations

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, from the library operation

mysql> select * from Atest;

+ ------ +

| User |

+ ------ +

| |

+ ------ +

1 row in set (0.00sec)

 


mysql> selectcount (*) from Atest;

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

| Count (*) |

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

| 1 |

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

1 row in set (0.00sec)
     
         
         
         
  More:      
 
- How to publish projects to the Jcenter repository using Gradle in Android Studio (Programming)
- Oracle restrict certain IP, the malicious user actions on important table (Database)
- Usage sed some of the parameters (Linux)
- Linux operating process information (Linux)
- Bash common several configuration files (Linux)
- Some common regular expressions (Linux)
- MySQL time field based partitioning scheme summary (Database)
- Linux, ls command to achieve (Linux)
- About Python default character set (Linux)
- jdbc Oracle database connection string writing pluggable (Database)
- Linux Shell Scripting (Programming)
- Using Python and OpenCV detecting image barcode (Programming)
- Spring AOP for logging (Programming)
- Awk include binding capacity larger than the specified size of all files directory (Linux)
- Python variable type (Programming)
- Spark and Hadoop comparison (Server)
- Linux Kernel 4.2 Installation Instructions (Linux)
- C # mobile side and PC-side data exchange (Database)
- CentOS7 install MySQL5.6.22 (Linux)
- Usage logs Python library (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.