Shulou(Shulou.com)06/01 Report--

This article is mainly about how to thoroughly solve the delay problem of master-slave replication in MySQL 5.7. you can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article can bring some practical help to solve the delay problem of master-slave replication in MySQL 5.7.This article can bring you some practical help.

1. Problem discovery

Sysbench tests 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 and find that the delay is serious.

Mysql > show slave status\ G

... Seconds_Behind_Master: 467...

2. Cause analysis.

A CVM opens N links to the client to connect, so there will be large concurrent update operations, but there is only one thread to read binlog from the CVM. When a SQL is executed on the CVM for a longer time or because a SQL has to lock the table, the SQL of the master CVM has a large backlog and has not been synchronized to the slave CVM. This leads to master inconsistency, that is, master-slave delay.

3. Solution: enable the new function of MySQL 5.7to copy 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; # size set mysql > start slave;mysql > show processlist as needed

4. Check the status after a period of time, and it has returned to normal

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

The complete solution to the delay problem of master-slave replication in MySQL 5.7 is to stop here. If you want to know about other related issues, you can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.