Several replication methods of MySQL master slave replication


  • Asynchronous replication
  • Multithreaded replication
  • Enhanced semi synchronous replication

Asynchronous replication

MySQL replication is asynchronous by defaultMaster slave replication requires at least two MySQL services, which can be distributed on different servers or on the same server.

MySQL master-slave asynchronous replication is the most common replication scenario. The integrity of the data depends on whether the binlog of the master database is lost. As long as the binlog of the master database is not lost, even if the master database is down, we can manually synchronize some of the lost data to the slave database through binlog.

be careful: when the master database is down, DBA can manually access the main database binlog through mysqlbinlog tool, extract the missing log and synchronize it to the slave database; it can also automatically extract the missing data to complete the slave database by configuring the highly available MHA architecture, or enable global transaction identifiers (gtid) to automatically extract the missing binlog to the slave database.

MySQL records transactions (or SQL statements) in binlog, that is, for engines that support transactions (such as InnoDB), binlog needs to be written when each transaction is committed; for engines that do not support transactions (such as MyISAM), binlog needs to be written when each SQL statement is completed. To ensure the security of binlog, MySQL introduces sync_ Binlog parameter to control the frequency of binlog refresh to disk.

show variables like 'sync_binlog';

  • By default, sync_ Binlog = 1, which means that MySQL needs to refresh binlog to disk before the transaction is committed. In this case, even if the operating system of the database host crashes or the host suddenly loses power, the system will lose the transactions in the prepared state at most. Set sync_ Binlog = 1 to ensure data security as far as possible.
  • sync_ Binlog = 0 means that MySQL does not control the refresh of binlog, but the file system controls the refresh of file cache.
  • sync_ Binlog = n, if n is not equal to 0 or 1, the refresh method is the same as sync_ Binlog = 1 is similar, except that the refresh rate will be extended to after n binlog commit groups.

The above is the traditional asynchronous replication. Before the parallel replication technology of MySQL 5.7 (also known as multi-threaded replication), the most criticized problem was efficiency. The slave latency is a persistent problem. Although schema level parallel replication has appeared before, the actual effect is not good.

Multithreaded replication

In mysql5.7, a newMultithreaded replicationThis technology solves the problem that the slave database cannot be applied concurrently when the data under the same schema of the master master is changed. At the same time, the advantages of binlog group submission are fully exploited to ensure the concurrent application of relay log by slave database.

In MySQL 8.0, multithreading replication has been updated and the concept of writeset has been introduced. In previous versions, if the same session of the main database executes transactions of different related objects in sequence, for example, the data of update a table is executed first, and then the update is executed For table B data, after binlog is copied to the slave database, the two transactions cannot be executed in parallel. The arrival of writeset breaks through this limitation.

Enhanced semi synchronous replication

The replication described above is an asynchronous operation. It is inevitable that there will be a certain delay between the data of the master database and the slave database. There is a hidden danger in this case: when a transaction is written to the master database and submitted successfully, but the slave database has not obtained the binlog log log of the master database, the master database crashes due to disk damage, memory failure, power failure and other reasons, resulting in the loss of binlog of the transaction on the master database The library loses this transaction, resulting in a master-slave inconsistency.

In order to solve this problem, we introduced semi synchronous replication from MySQL 5.5. The technology at this time is called traditional semi synchronous replication for the time being. After the technology developed to MySQL 5.7, it has evolved into enhanced semi synchronous replication (also known as lossless replication). During asynchronous replication, the master database can successfully return to the client after executing the commit operation and writing the binlog log log, without waiting for the binlog log log to be delivered to the slave database, as shown in the figure.

In semi synchronous replication, in order to ensure that every binlog transaction on the master database can be reliably copied to the slave database, the master database does not timely feed back to the front-end application users each time the transaction is successfully committed, but waits for at least one slave database (see parameter RPL for details)_ semi_ sync_ master_ wait_ for_ slave_ Count) after the binlog transaction is also received and the relay log is successfully written, the main database returns the commit operation to the client successfully (whether it is traditional semi synchronous replication or enhanced semi synchronous replication, the purpose is the same, but there is a slight difference between the two methods, which will be explained below)

Semi synchronous replication ensures that after the transaction is successfully committed, there are at least two log records, one on the binlog log log of the master database, and the other on the relay log of at least one slave database, thus further ensuring the integrity of the data.

In traditional semi synchronous replication, the master database writes data to binlog, and after the commit operation, it always waits for the ack of the slave database, that is, the slave database writes to relay After logging, the data is dropped to the disk and returned to the master database to inform the master database that the front-end application operation is successful. In this case, a problem will arise. In fact, the master database has committed the transaction to the transaction engine layer, and the application can see that the data has changed, but it is just waiting to return. If the master database is down at this time, it is possible that the slave database has not been able to write rela Y log, the master-slave database inconsistency will occur. To solve this problem, the enhanced semi synchronous replication is fine tuned. After the master database writes data to binlog, it starts to wait for the ACK from the slave database until at least one slave database writes the relay After logging, the data is dropped to the disk, and then the message is returned to the main database to inform the main database that the commit operation can be performed, and then the main database starts to commit to the transaction engine layer. The application can see that the data has changed. The general process of enhanced semi synchronous replication is shown in the figure below.

In the semi synchronous replication mode, if the slave database is down or the network is delayed when the binlog log log is transferred to the slave database, the transaction on the master database will wait for a period of time (the length of time is determined by the parameter RPL)_ semi_ sync_ master_ If binlog fails to be sent to the slave database successfully within this period of time, MySQL automatically adjusts the replication mode to asynchronous mode, and the transaction returns the commit result to the client normally.

Semi synchronous replication largely depends on the network conditions between the master and slave databases. The smaller the round-trip delay RTT, the better the real-time performance of the slave database is. Generally speaking, the faster the network between the master and slave libraries, the more real-time the slave databases are.

be careful: round trip time (RTT) is an important performance index in computer networks. It represents the total time from the sending end to receiving the confirmation from the sending end.

Organized from:
Deep understanding of MySQL database development, optimization, management and maintenance

Please indicate the original link:

If this article is helpful to you, please clickrecommendThank you.

Recommended Today

Summarize a wave of redis interview questions and collect them!

The interview questions are as follows: Redis persistence mechanism Cache avalanche, cache penetration, cache preheating, cache update, cache degradation and so on What are hot data and cold data What are the differences between Memcache and redis? Why is single threaded redis so fast The data types of redis, the usage scenarios of each data […]