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

This article shows you the principle analysis of MySQL master-slave replication, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

How is master-slave replication realized? The update statement records binlog, which is a logical log. With this binlog, the slave server will get the binlog file of the master server, then parse the SQL statements in it, and execute it again on the slave server to keep the master-slave data consistent.

This involves three threads that connect to the master to get the binlog and parse the binlog to write to the relay log, which is called the Ihand O thread. There is a log dump thread on the Master node that is used to send binlog to slave. The SQL thread from the library is used to read relay log and write data to the database.

After making the master-slave replication scheme, we only write the data to the master node, while the read request can be shared to the slave node. We call this scheme the separation of reading and writing.

Read-write separation can reduce the access pressure of database server to some extent, but special attention should be paid to the consistency of master-slave data. What if we write in master and immediately go to slave to query, and the data of slave has not been synchronized at this time? So, based on the principle of master-slave replication, we need to understand, where is master-slave replication slow?

Single thread

In early MySQL, the SQL thread for slave was single-threaded. Master can support parallel execution of SQL statements. The maximum number of connections configured is the maximum number of SQL parallel execution at the same time. On the other hand, the SQL of slave can only be executed in a single thread. In the case of a large amount of concurrency in the master database, the synchronization data will definitely be delayed. Why can't the SQL Thread on the slave library be executed in parallel? For example, the main library executes multiple SQL statements, first the user makes a comment, then modifies the content, and finally deletes the comment. The order of execution of these three statements on the slave library must not be reversed.

Insert into user_comments (10000009); update user_comments set content = 'very good' where id = 10000009; delete from user_comments where id = 10000009

How to solve this problem? How to reduce the delay of master-slave replication?

Async and full synchronization

First of all, we need to know that during master-slave replication, MySQL is replicated asynchronously by default. That is, for the primary node, the binlog is written, the transaction ends, and it is returned to the client. For slave, receiving the binlog is done. Master doesn't care whether the slave data has been written successfully or not.

If you want to reduce latency, can you wait for all transactions from the library to finish before returning to the client? This method is called full synchronous replication. After the data is written from the library, the master database will be returned to the client.

Although this method can ensure that the data has been synchronized successfully before reading, you should expect that the transaction execution time will be longer, which will lead to the performance degradation of the master node. Is there a better way? It reduces the latency of slave writes without significantly increasing the time it takes for master to return to the client.

Semi-synchronous replication

Between asynchronous replication and fully synchronous replication, there is also a way of semi-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 to receive the binlog from the library and write it to the relay log before returning to the client. Master doesn't wait long, but when it returns to the client, the data is about to be written successfully, because it only has the last step left: reading the relay log and writing to the slave library.

If we want to use semi-synchronous replication in the database, we must install a plug-in, which is contributed by an engineer at Google. This plug-in is already available in the mysql plug-ins directory: the cd / usr/lib64/mysql/plugin/ master library and slave library are different plug-ins that need to be enabled after installation:

-- Master library executes INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; set global rpl_semi_sync_master_enabled=1; show variables like'% semi_sync%';-- slave library executes INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; set global rpl_semi_sync_slave_enabled=1; show global variables like'% semi%'

Compared with asynchronous replication, semi-synchronous replication improves the security of data, at the same time, it also causes a certain degree of delay, it needs to wait for a slave to write to the relay log, here there is an additional process of network interaction, so semi-synchronous replication is best used in low-latency networks.

This is to ensure the writing of slave data from the point of view of the connection between the master library and the slave library.

Another idea is that if you want to reduce the delay of master-slave synchronization and reduce the waiting time caused by SQL execution, is there a way for multiple SQL statements to be executed in parallel on the slave library instead of queuing?

Multi-library parallel replication

How to achieve parallel replication? Imagine that if three statements are executed in three databases and operate on their respective databases, will there be no concurrency problem? There is no requirement for the order of execution. Of course, so if you are operating on three databases, the SQL threads of the slave libraries of the three databases can execute concurrently. This is the multi-library parallel replication supported in MySQL version 5.6.

But in most cases, we are in the case of multiple tables in a single database, how to achieve parallel replication in a database? In other words, we know that the database itself supports multiple transactions operating at the same time; why can these transactions be executed in parallel on the main library without problems?

Because they themselves do not interfere with each other, such as these transactions operate on different tables, or operate on different rows, there is no competition for resources and data interference. Then transactions executed in parallel on the master library can certainly be executed in parallel on the slave library, right? For example, if there are three transactions on master that operate on three tables at the same time, can these three transactions be executed in parallel on slave?

5 GTID replication of asynchronous replication

Https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html so, we can divide the transactions executed in parallel on the master library into a group and number them, and the transactions of this group can also be executed in parallel on the slave library. This number, we call it GTID (Global Transaction Identifiers), this kind of master-slave replication, we call it GTID-based replication.

If we want to use GTID replication, we can turn it on by changing the configuration parameters, which is off by default:

Show global variables like 'gtid_mode'

Whether it is to optimize the connection between master and slave, or to let the slave library execute SQL in parallel, it is from the data library level to solve the problem of master-slave replication delay.

The above content is the principle analysis of MySQL master-slave replication. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.