Completely resolve the latency issue with MySQL 5.7 Master-slave replication

1. Problem finding
Sysbench test MySQL with the following configuration

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.1.221 --mysql-port=3306 --mysql-user=root --mysql-password=MySQL5.7 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=10000 --threads=50 --time=60 --db-driver=mysql --report-interval=10 run >sysbench.log

Check the status of slave after a period of time to find delay severity
Mysql> Show Slave Status\g

...Seconds_Behind_Master: 467...

2. Cause analysis
A server open n links to the client to connect, so there will be large concurrent update operations, but from the inside of the server read Binlog only one, when a SQL to execute from the server for a little longer or because of a SQL to be locked table will lead to the main server SQL backlog, is not synchronized to the slave server. This leads to the inconsistency of the Lord, which is the master-slave delay.

3. Workaround, turn on MySQL 5.7 new feature replication multithreading

mysql> show variables like ‘slave_parallel%‘;+------------------------+----------+| Variable_name          | Value    |+------------------------+----------+| slave_parallel_type    | DATABASE || slave_parallel_workers | 0        |+------------------------+----------+mysql> set global slave_parallel_type=‘logical_clock‘;mysql> set global slave_parallel_workers=100;   #大小根据需要设置mysql> start slave;mysql> show processlist;

4, after a period of time to check the status, has returned to normal

mysql> show slave status\G...Seconds_Behind_Master: 0...

Reference Document: Master-slave replication latency solution under MySQL 5.7

Completely resolve the latency issue with MySQL 5.7 Master-slave replication