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

This article mainly gives you a brief description of the detailed steps for troubleshooting mysql semi-synchronous replication problems. 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 you some practical help in troubleshooting mysql semi-synchronous replication problems.

1. Problem background

By default, online mysql replication is asynchronous, so in extreme cases, when switching between master and slave, there is a certain probability that the slave database has less data than the master database. Therefore, after the switch, we will roll back and repair the data through the tool to ensure that the data is not lost. Semi-synchronous replication requires the main database to perform every transaction, and at least one standby database is required to be received successfully before the execution is completed, so the strong consistency of the primary and standby database can be maintained. In order to ensure the strong consistency of master and backup database data and reduce data loss, try to turn on the semi-synchronous (semi-sync) feature of mysql replication in the production environment. During the actual operation, it is found that most instances of semi-synchronization can run normally, but a small number of instances cannot be opened all the time (can only be run in ordinary replication mode). What is even more bizarre is that two instances of the same host, one can be enabled and the other cannot. The final positioning of the problem is also very simple, but it took some effort to troubleshoot it, and the whole troubleshooting process will be described below.

two。 Principle of semi-synchronous replication

The master / slave database of mysql is consistent through the binlog log. The master database executes the transaction locally, and the binlog log is returned to the user when the binlog log is set. The slave database synchronizes the operation of the master database by pulling the binlog log of the master database. By default, there is no strict synchronization between the main database and the standby database, so there is a certain probability that the data of the standby database and the primary database are not equal. The emergence of semi-synchronous feature is to ensure the consistency of master and standby data at any time. As opposed to asynchronous replication, every transaction required by semi-synchronous replication requires at least one standby database to be successfully received before it is returned to the user. The implementation principle is also very simple: after the local execution of the master database, wait for the response message of the slave database (including the binlog (file,pos) received by the latest slave database), receive the response message of the slave database, and then return it to the user, so that a transaction is really completed. On the main database instance, there is a special thread (ack_receiver) that receives the response message from the standby database and notifies the master database of the logs that have been received by the master database, so that execution can continue. For the specific implementation of semi-synchronous, you can refer to another article, mysql semi-synchronous (semi-sync) source code implementation.

3. Analysis of problems

After a brief introduction to the principle of semi-synchronous replication, let's look at the specific problems. When the master / slave database turns on the semi-synchronous switch, the status variable "Rpl_semi_sync_master_status" of the problem instance is always OFF, indicating that the replication has been running in the state of normal replication.

(1)。 Modify the rpl_semi_sync_master_timeout parameter.

Among the semi-synchronous replication parameters, a rpl_semi_sync_master_timeout parameter is used to control the time for the master database to wait for the slave database response message. If this value is exceeded, the slave database has not been received (the slave database may be dead, or the slave database execution may be very slow, which is much different from that of the master database). In this case, replication will be switched to normal replication to avoid long waiting for execution transactions of the master database. Online this value is 50ms by default, simply wonder if this value is too small, so change it to 10s, but the problem is still puzzled.

(2)。 Print log

The simplest and stupidest way to troubleshoot a problem is to log in to see which part of the problem has gone wrong. The master database and standby database have rpl_semi_sync_master_trace_level and rpl_semi_sync_slave_trace_level parameters respectively to control semi-synchronous replication and print logs. Set the two parameter values to 80 (6416) to record detailed log information, as well as incoming and outgoing function calls.

Master:2016-01-04 18:00:30 13212 [Note] ReplSemiSyncMaster::updateSyncHeader: server (- 1721062019), (mysql-bin.000006, 500717950) sync (1), repl (1) 1721062019-01-04 18:00:40 13212 [Warning] Timeout waiting for reply of binlog (file: mysql-bin.000006, pos: 500717950), semi-sync up to file Position 0.2016-01-04 18:00:40 13212 [Note] Semi-sync replication switched OFF.slave:2016-01-04 18:00:30 38932 [Note]-- > ReplSemiSyncSlave::slaveReply enter2016-01-04 18:00:30 38932 [Note] ReplSemiSyncSlave::slaveReply: reply (mysql-bin.000006, 500717950) 18:00:30 38932 [Note] 1024 So this bug is not very easy to appear, but the problem is common.

(7) extension of the problem

After the problem was located, another problem bothered me for a long time. Because there are three listening parts in the mysql kernel, 1 is the listening port select,2 is the thread pool listening epoll,3 is semi-synchronous select listening. The thread of slave binlog dump is an ordinary worker thread, and the socket of the worker thread will be monitored by epoll, so that the socket of binlog dump will be listened to by both semi-synchronous select listening and thread pool epoll listening, isn't that messy? Later, after looking at the code carefully, we found that the epoll listening of the thread pool uses EPOLLONESHOT mode. After receiving a message, it will be unbound and needs to be re-registered, so the same handle will not be listened to by both listening mechanisms at the same time.

At this point, the process of troubleshooting the problem is over, and the conclusion is relatively simple, but it does take some effort to locate the problem. Because select is a relatively general multiplexing mechanism for multiplex IO, children's shoes that are useful to the select function may need to pay attention to its limitations.

The detailed steps of troubleshooting mysql semi-synchronous replication will 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.