Analysis and solution of MySQL database master-Slave synchronization delay

First, MySQL database master-slave replication principle

MySQL master-slave replication is actually based on binary logs, and the principle can be represented by a graph:

Divided into four steps to go:

1. The main library writes all the logs generated by DDL and DML into Binlog;

2. The main libraries into a log dump thread, which is used to read binlog from the library I/O thread;

3. From the I/O thread of the library, request the Binlog of the main library and write the resulting binlog log to the relay log file;

4. The SQL thread from the library reads the logs from the relay log file into specific actions, replaying the DDL and DML operation events of the main library.

About DDL and DML

The SQL language is divided into four categories: Query Language DQL, Control Language DCL, manipulate language DML, define language DDL.

DQL: Can be simply understood as a SELECT statement;

Dcl:grant, rollback, and commit a class of statements;

DML: A statement that can be understood as a type of create;

Ddl:insert, UPDATE, and DELETE statements are all;

Ii. problems existing in the master-slave replication

1. The data may be lost after the main library is down;

2. master-Slave synchronization delay.

Third, MySQL database master-Slave synchronization delay causes

Cause analysis

MySQL's master-slave replication is a single-threaded operation, and the main library writes into Binlog for all the DDL and DML-generated logs, and is highly efficient because binlog is sequential. The slave SQL thread thread replays the DDL and DML operation events of the main library in slave. The IO operations of DML and DDL are immediate, not sequential, and much more expensive. On the other hand, because SQL thread is also single-threaded, when the main library is concurrently high, the number of DML generated exceeds the speed that slave's SQL thread can handle, or the delay occurs when there is a large query statement in slave that creates a lock wait.

Common causes: Master load is too high, slave load is too high, network delay, machine performance is too low, MySQL configuration is unreasonable.

Four, master-slave delay Troubleshooting method

Judge by monitoring the value of the Seconds_behind_master parameter output from the show slave status command:

NULL, indicating that any one of the Io_thread or Sql_thread has failed;

0, the value is zero, indicating that the master-slave copy is good;

A positive value indicates that the master/slave has been delayed, the larger the number, the more severe the delay from the library.

V. Solutions

Resolve Data loss issues:

1. Semi-synchronous replication

Starting with MySQL5.5, MySQL has supported semi-synchronous replication, where semi-synchronous replication is between asynchronous replication and synchronous replication, and the main library does not immediately return results to the client after executing a transaction, waiting for at least one receipt from the library and writing to the relay log to return the results to the client. Semi-synchronous replication improves data security relative to asynchronous replication, and it also creates a time-consuming delay for TCP/IP round trips.

2. Main Library Configuration Sync_binlog=1,innodb_flush_log_at_trx_commit=1

The default value of Sync_binlog is that 0,mysql does not synchronize Binlog to disk, and its value indicates how many Binlog to synchronize the disk per write.

A innodb_flush_log_at_trx_commit of 1 indicates that each transaction commit or out-of-transaction instruction needs to flush the log to disk.

Note: When the above two values are set to 1 o'clock at the same time, write performance is subject to a certain limit, only scenarios that require high data security are recommended, such as money-related order payment services, and system I/O capabilities must be supported!

Troubleshoot problems with replication delays from libraries:

1. Optimize your network

2. Upgrading slave hardware configuration

3. Slave adjust parameters, close Binlog, modify Innodb_flush_log_at_trx_commit parameter values

4. Upgrading MySQL version to 5.7, using parallel replication

More optimization measures welcomed the broad masses of Bo friends to add and correct ...

Analysis and solution of MySQL database master-Slave synchronization delay