Summary of several replication methods for MySQL master-slave replication

Asynchronous replication

MySQL replication is asynchronous by default. Master-slave replication requires at least two MYSQL services. These MySQL services 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 the BINLOG of the master database not being lost. As long as the BINLOG of the master database is not lost, even if the master database crashes, we can still manually synchronize the lost data to the slave database through BINLOG.

Note: When the master database is down, the DBA can manually access the master database binlog through the mysqlbinlog tool, extract the missing logs and synchronize them to the slave database; you can also configure a high-availability MHA architecture to automatically extract the missing data to complete the slave database, or enable Global Transaction Identifiers (GTID) to automatically extract the missing binlog to the slave database.

MySQL records transactions (or SQL statements) in BINLOG, which means that 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 is required when each SQL statement is executed. To ensure the security of Binlog, MySQL introduces the sync_binlog parameter to control the frequency of BINLOG flushing to disk.

show variables like 'sync_binlog'; 

• By default, sync_binlog=1 means that before a transaction is committed, MySQL needs to flush BINLOG to disk. In this way, even if the database host operating system crashes or the host suddenly loses power, the system will lose at most transactions in the prepared state. Set sync_binlog=1 to ensure data security as much as possible.
• sync_binlog=0 means that MySQL does not control the refresh of binlog, and the file system itself controls the refresh of the file cache.
• sync_binlog=N, if N is not equal to 0 or 1, the refresh method is similar to sync_binlog=1, except that the refresh frequency will be extended to after N binlog submission groups.
  

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

Multithreaded replication

MySQL 5.7 introduces a new multi-threaded replication technology that solves the problem that slaves cannot apply data concurrently when data under the same schema of the master changes. It also fully utilizes the advantages of binlog group submission and ensures the ability of slaves to concurrently apply Relay Log.

In MySQL 8.0, multi-threaded replication has undergone a technical update and the concept of writeset has been introduced. In previous versions, if the same session of the master database executes transactions of multiple different related objects in sequence, for example, Update A table data is executed first, and then Update B table data is executed. After BINLOG is copied to the slave database, these two transactions cannot be executed in parallel. The arrival of writeset breaks this limitation.

Enhanced semisynchronous replication

The replication described above is an asynchronous operation. There will inevitably be a certain delay between the data of the master and slave databases. This poses a hidden danger: when a transaction is written to the master database and submitted successfully, but the slave database has not yet obtained the BINLOG log of the master database, the master database unexpectedly crashes due to disk damage, memory failure, power outage, etc., resulting in the loss of the BINLOG of the transaction on the master database. At this time, the slave database will lose this transaction, resulting in inconsistency between the master and the slave.

To solve this problem, semi-synchronous replication was introduced starting from MySQL 5.5. The technology at that time was temporarily called traditional semi-synchronous replication. After the technology was developed to MySQL 5.7, it has evolved into enhanced semi-synchronous replication (also called lossless replication). In asynchronous replication, the master database can successfully return to the client after executing the Commit operation and writing the BINLOG log, without waiting for the BINLOG log to be transmitted 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 replicated to the slave database, the master database does not promptly feedback to the front-end application user each time a transaction is successfully committed. Instead, it waits for at least one slave database (see parameter rpl_semi_sync_master_wait_for_slave_count for details) to also receive the BINLOG transaction and successfully write it to the relay log. Only then will the master database return a Commit operation success to the client (whether it is traditional semi-synchronous replication or enhanced semi-synchronous replication, the purpose is the same, but the two methods have a difference in one place, which will be explained below)

Semi-synchronous replication ensures that after a transaction is successfully committed, there are at least two log records, one in the BINLOG log of the master library and the other in the relay log of at least one slave library, thereby further ensuring the integrity of the data.

In traditional semi-synchronous replication, after the master database writes data to BINLOG and executes the Commit operation, it will wait for the ACK from the slave database. That is, after the slave database writes the Relay Log and stores the data on the disk, it returns a message to the master database, notifying the master database that it can return the front-end application operation successfully. This will cause a problem, that is, the master database has actually committed the transaction to the transaction engine layer, and the application can already see that the data has changed, but is just waiting for a return. If the master database crashes at this time, it is possible that the slave database has not yet been able to write the Relay Log, which will cause inconsistency between the master and slave databases. Enhanced semi-synchronous replication is designed to solve this problem. After the master database writes data to BINLOG, it starts waiting for the slave database's response ACK until at least one slave database writes to the Relay Log and stores the data on disk. Then it returns a message to the master database, notifying it that it can perform the Commit operation. The master database then starts to commit to the transaction engine layer, and the application can see that the data has changed. The general process of enhanced semi-synchronous replication is shown in the following figure.

In semi-synchronous replication mode, if the slave database crashes or the network is delayed when transmitting BINLOG logs to the slave database, the BINLOG logs are not transmitted to the slave database in time. At this time, the transaction on the master database will wait for a period of time (the length of time is determined by the number of milliseconds set by the parameter rpl_semi_sync_master_timeout). If BINLOG logs cannot be successfully sent to the slave database during this period of time, MySQL automatically adjusts the replication to asynchronous mode, and the transaction returns the submission result to the client normally.

Semi-synchronous replication depends largely 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. In layman's terms, the faster the network between the master and slave databases, the more real-time the slave databases are.

Note: Round-Trip Time (RTT) is an important performance indicator in computer networks. It indicates the total time from the start of data transmission to the receipt of confirmation from the receiver (this may be a bit confusing, we can understand it as the first two handshakes of the TCP three-way handshake).

Summarize

This is the end of this article about MySQL master-slave replication. For more information about MySQL master-slave replication, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!