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

This article will explain in detail how to achieve asynchronous replication in MySQL. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

I. the derivative history of MYSQL replication architecture

In 2000, Replication was introduced in MySQL version 3.23.15. As a quasi-real-time synchronization method, Replication has been widely used. The implementation of Replicaton at this time involves two threads, one in Master and one in Slave. Slave's I apply O and SQL functions are used as a thread to get event from Master and then directly apply without relay log. In this way, the speed of reading event will be slowed down by Slave replay. When there is a large delay between master and slave, a large number of binary log will not be backed up to Slave.

In 2002, MySQL version 4.0.2 separated event reading and execution on the Slave side into two threads (IO thread and SQL thread), while introducing relay log. The IO thread reads the event and writes to the relay log,SQL thread reads the event from the relay log and then executes. In this way, even if the SQL thread executes slowly, the binary log of Master will be synchronized to Slave as much as possible. When Master goes down and you switch to Slave, there will be no significant data loss.

This method of asynchronous replication was used until MySQL version 5.5 in 2010. The transaction execution of the main database will not control the synchronization progress of the library. If the standby database lags behind and the primary database is unfortunately crash, it will result in data loss. So semi-synchronous replication was naturally introduced in MySQL 5.5. the main library needs to ensure that at least one is received from the library and written to the relay log before replying to the transaction committed by the client.

In 2016, MySQL introduced an entirely new technology called InnoDB Group Replication in 5.7.17. At present, the official MySQL 5.7.17 full synchronization technology based on Group replication has come out, which brings more data consistency protection.

The following figure corresponds to several replication types of MySQL, which are asynchronous, semi-synchronous and full-synchronous

II. Asynchronous replication (Asynchronous replication)

1. Logically

The default replication of MySQL is asynchronous. After executing the transaction committed by the client, the master database will immediately return the result to the client and does not care whether it has been received and processed by the slave database, so there will be a problem. If the master crash is dropped, the committed transaction on the master may not be transferred to the slave database. If the slave is forcibly promoted at this time, the data on the new master may be incomplete.

two。 Technically

The master library writes transactional Binlog events to the Binlog file. At this time, the master library will only notify the Dump thread to send these new Binlog, and then the master library will continue to process the commit operation, and at this time there is no guarantee that these Binlog will be passed to any slave library node.

3. Schematic diagram

(1) execute the sart slave command on the Slave server to turn on the master-slave replication switch and start the master-slave replication.

(2) at this point, the I / O thread of the Slave server connects to the master server through a request for replication user rights authorized on the master, and requests that the contents of the binlog log be sent from the specified location where the log file name and location are specified by the change master command when the master-slave replication service is configured.

(3) after the Master server receives the request from the IO thread of the Slave server, the IO thread responsible for replication on it reads the binlog log information after the specified location of the specified binlog log file in batches according to the information requested by the IO thread of the Slave server, and then returns it to the IO thread on the Slave side. In addition to the contents of the binlog log, there are IO threads recorded on the Master server side. The information returned is except for the next specified update location in the binlog.

(4) when the IO thread of the Slave server obtains the log contents, log files and location points sent by the IO thread on the Master server, the binlog log contents will be written to the end of the Relay Log (relay log) file (Mysql-relay-bin.xxx) of the server itself, and the new binlog file name and location will be recorded in the master-info file. So that the next time you read the new binlog log on the master side, you can tell the Master server to start reading the new binlog log from the specified file and location of the new binlog log.

(5) the Slave thread on the server side detects the new log contents of the IO thread in the local Relay Log in real time, then parses the contents of the Relay LOG file into sql statements in time, and executes such sql statements on its own Slave server in the order in which the SQL statements are parsed, and records the file name and location point of the current application relay log in relay-log.info.

3. Full synchronous replication (Fully synchronous replication)

1. Logically

When the master library executes a transaction, all slave libraries execute the transaction before returning to the client. Because you need to wait for all slaves to complete the transaction before returning, the performance of fully synchronous replication is bound to be seriously affected.

two。 Technically

When the master commits the transaction, all slave nodes must receive, APPLY, and commit the transaction before the master thread can continue to do subsequent operations. However, the disadvantage is that the time for the main library to complete a transaction will be lengthened and the performance will be degraded.

3. Schematic diagram

IV. Semi-synchronous replication (Semisynchronous replication)

1. Logically

It is between full synchronous replication and full asynchronous replication. The master database only needs to wait for at least one slave node to receive and Flush Binlog to the Relay Log file, and the master database does not need to wait for all slave libraries to give feedback to the master database. At the same time, this is only a feedback received, not feedback that has been fully completed and submitted, thus saving a lot of time.

two。 Technically

Between asynchronous replication and full synchronous replication, the main library does not return to the client immediately after executing the transaction committed by the client, but waits for at least one received from the library and written to the relay log before returning to the client. Compared with asynchronous replication, semi-synchronous replication improves the security of data, and it also causes a certain degree of delay, which is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used in low-latency networks.

3. Schematic diagram

Master writes each transaction to binlog (sync_binlog=1), passes it to slave to flush to disk (sync_relay=1), and the main library commits the transaction (commit). Master waits for the slave feedback to receive the relay log, and only after receiving the ACK does the master feedback the commit OK result to the client.

On how to achieve asynchronous replication in MySQL to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.