Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve the problem that you can't read after the separation of MySQL reading and writing". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Today, we will take a detailed look at the problem that can not be read after the read-write separation occurs during the master-slave synchronization delay, and then explain the cause of the problem, the solution strategy and the specific implementation of open source database middleware such as Sharding-jdbc, MyCat and MaxScale.

Can't read the problem after writing.

MySQL's classic one-master, two-slave three-node architecture is one of the mainstream data storage schemes used by most start-ups at the initial stage. The master node handles write operations and two slave nodes handle read operations, sharing the pressure on the master database.

However, sometimes you may encounter awkward situations where you can't read or read the old state immediately after performing a write operation. This is due to the possible delay in master-slave synchronization, performing a write operation on the master node, and then performing a read operation on the slave node, reading the previous state.

The figure above shows a schematic diagram of the sequence of operations in which such problems occur:

The client first writes to the primary node Master through the agent

The second step is to perform a read operation from node Slave A, at which time the synchronization between Master and Slave An is not completed, so the second step reads to the old state

When the fifth step reads again, the synchronization is complete, so the correct state can be read from Slave B.

Next, let's take a look at why this kind of problem occurs.

MySQL master-slave synchronization

Understand the reasons behind the problem, in order to better solve the problem. The process of MySQL master-slave replication is roughly shown in the following figure. This article only explains the process of synchronization. The establishment of synchronous connection and lost connection retransmission is not the key point. Students who are interested can understand it by themselves.

MySQL master-slave replication involves two master-slave nodes, with a total of four threads participating:

The Client Thread of the master node, the thread that processes the client request, performs the steps 1-5, 2-3 and 4-4 as shown in the figure to ensure data consistency and minimize loss. Dump Thread will be notified in the third step.

The Dump Thread of the master node, after receiving the Client Thread notification, is responsible for reading the local binlog data and sending the binlog data, the binlog file name and the location information of the current sending binlog to the slave node.

The IO Thread of the node is responsible for receiving the binlog data and related location information sent by Dump Thread and appending them to the local relay log and other files.

If new data is appended to the relay log from the SQL Thread of the node, then parse its contents (that is, parse the contents of the binlog file) into executable SQL statements, then execute it in the local data, and record the location of the relay log currently executed.

The above is the default asynchronous synchronous mode. We find that there are more than 10 steps between the successful submission of the master node and the completion of synchronization of the slave node, which involves a network transfer, multiple disk IO operations for reading and writing files, and the final CPU operation performed by SQL.

Therefore, when there is a problem in the network transmission between the master and slave nodes, or when the performance of the slave node is low, the synchronization between the master and slave nodes will be delayed, resulting in the problem that can not be read after writing mentioned at the beginning of the article. In high concurrency scenarios, it usually takes dozens of milliseconds or even hundreds of milliseconds to read the latest status from the slave node.

Common solution strategies

Generally speaking, there are roughly the following solutions to solve the problem of being unable to read after writing:

Forcibly take the main library

Judge that there is no delay between master and standby.

Equal main library sites or GTID scheme

Forcibly take the main library

Forcing the main library scheme is the easiest to understand and implement, and it is also the most commonly used scheme. As the name implies, it forces some read operations that must be read to the latest state to be performed by the master node, so that there will be no read problems after writing. The problem of this scheme is that part of the read pressure is given to the master node, which partly breaks the purpose of the separation of reading and writing, and reduces the expansibility of the whole system.

In general, mainstream database middleware provides a mechanism to force the main library to go. For example, in sharding-jdbc, Hint can be used to force routing of the main library.

Its principle is to add Hint before the SQL statement, and then the database middleware will recognize the Hint and route it to the master node.

Next, let's take a look at how to query from the library and avoid overdue reading, and analyze the advantages and disadvantages of each solution.

Judge that there is no delay between master and standby.

The second option is to use some values in the result of the show slave status statement to determine the delay time of master-slave synchronization:

> show slave status * * 1. Row * * Master_Log_File: mysql-bin.001822 Read_Master_Log_Pos: 290072815 Seconds_Behind_Master: 2923 Relay_Master_Log_File: mysql-bin.001821 Exec_Master_Log_Pos: 256529431 Auto_Position: 0 Retrieved_Gtid_Set : Executed_Gtid_Set:.

Seconds_behind_master, which indicates the number of seconds behind the master node. If this value is 0, there is no delay between master and slave.

Master_Log_File and Read_Master_Log_Pos represent the latest sites of the read main library, Relay_Master_Log_File and Exec_Master_Log_Pos, and represent the latest sites executed by the standby library. If the two sets of values are equal, it means that there is no delay between master and slave.

Auto_Position=1, which means that the GTID protocol is used, and the GTID collection Retrieved_Gtid_Set of all logs received by the slave database is equal to the completed GTID collection Executed_Gtid_Set, which means that there is no delay between master and slave.

Before performing the read operation, we first judge whether there is a delay between the master and slave according to the above method, and if there is a delay, wait until there is no delay. However, such schemes have the problem of false yang and false yin in judging whether there is a delay:

There is no delay in judgment, other delays. Because the above judgment is based on the state of the slave node, when the Dump Thread of the master node has not sent the latest status to the IO SQL of the slave node, the slave node may mistakenly judge that it and the master node have no delay.

It is determined that there is a delay, but the latest state of the read operation read has been synchronized. Because MySQL master-slave replication is going on all the time, there may be other irrelevant write operations while reading directly after writing. Although there is a master-slave delay, the synchronization of the first write operation has been completed, so the read operation can read the latest status.

For the first question, you need to use the master-slave replication semi-sync mode, which is the default asynchronous mode explained above. The flow of the semi-sync mode is shown in the following figure:

When the master node transaction commits, Dump Thread sends the binlog to the slave node

After receiving the binlog from the IO Thread of the node, it sends an ack back to the master node, indicating that it has received

After the Dump Thread of the primary node receives the ack, it notifies Client Thread, and then returns the successful response to the client.

In this way, after the write operation is executed, make sure that the slave node has read the binglog data sent by the master node, that is, Master_Log_File, Read_Master_Log_Pos or Retrieved_Gtid_Set, so that it can be compared with the relevant data to determine whether there is a delay.

Unfortunately, the above semi-sync mode only needs to wait for the ACK of one slave node, so the one-master and multi-slave mode will not work.

Although this solution has various problems, it is also applicable to scenarios where consistency requirements are not so high. For example, whether MyCat uses seconds_behind_master to lag behind the master node too much, and if it exceeds a certain threshold, it will be deleted from the list of valid nodes, and read requests will no longer be routed to it.

In the MySQLDetector class of MyCAT that listens to the status of the slave node and sends the heartbeat, it reads the seconds_behind_master of the slave node, and if its value is greater than the configured slaveThreshold, it prints the log and sets the delay time to the heartbeat information.

Next, we will introduce the solution to the second problem, which is to determine that there is a delay, but the specific latest state read by the read operation has been synchronized.

Equal GTID scheme

First of all, let's introduce GTID, that is, the global transaction ID, which is generated by a transaction when it is committed and is the unique identity of the transaction. It consists of the uuid of the MySQL instance and an integer maintained by the instance with an initial value of 1, which is incremented each time the instance commits a transaction.

MySQL provides a GTID-based command to execute on the slave node, waiting for the library to be synchronized to the corresponding GTID (the binlog file will contain GTID), or to return a timeout.

After executing the transaction, MySQL will give the GTID of the transaction to the client, and then the client can use this command to execute the slave library to perform the read operation, wait for the GTID, and then perform the read operation after it is successful; if the wait times out, go to the master library to perform the read operation, or change to another slave library to perform the above process.

MariaDB's MaxScale uses this scheme. MaxScale is a database intelligent agent service developed by MariaDB (also supports MySQL), which allows requests to be directed to one or more servers according to database SQL statements, and can set steering rules of various complexity.

MaxScale uses the above scheme in its readwritesplit.hh header file and the add_prefix_wait_gtid function in its rwsplit_causal_reads.cc file.

For example, the SQL that used to perform the read operation and the prefixed SQL are as follows:

When WAIT_FOR_EXECUTED_GTID_SET execution fails, the original SQL is no longer executed, but the SQL is sent to the master node for execution.

This is the end of the content of "MySQL separation of reading and writing, how to solve the problem of reading and writing". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.