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

This article mainly explains "the analysis of the inconsistency of master-slave synchronization in mysql". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the analysis of the inconsistency of master-slave synchronization in mysql.

1. Principle of master-slave synchronization delay in MySQL database.

A: when it comes to the principle of master-slave synchronization delay in MySQL database, we should start with the master-slave replication principle of mysql. The master-slave replication of mysql is a single-thread operation, and the master database generates binlog,binlog for all DDL and DML sequentially, so it is very efficient. The Slave_IO_Running thread of slave is very efficient to retrieve logs from the master database. The next step is that the Slave_SQL_ running thread of slave implements the DDL and DML operations of the master database in slave. The IO operation of DML and DDL is random, not sequential, and the cost is much higher. Other queries on slave may also generate lock contention. Because Slave_SQL_Running is also single-threaded, a DDL card owner needs to execute for 10 minutes. Then all subsequent DDL will wait for the DDL to finish execution before continuing to execute, which leads to the delay. A friend will ask: "the same DDL on the main library also needs to execute 10 points. Why is the slave delayed?" The answer is that master can be concurrent, but Slave_SQL_ running threads cannot.

2. How the master-slave synchronization delay of MySQL database is generated.

A: when the TPS concurrency of the main library is high, the number of DDL generated is more than a sql thread of slave can bear, then the delay occurs, and of course, lock waiting may occur with the large query statements of slave.

3. Master-slave synchronization delay solution for MySQL database

A: the simplest way to reduce slave synchronization latency is to optimize the architecture so that the DDL of the main library can be executed as quickly as possible. In addition, the main library is write, which is highly secure for data, such as sync_binlog=1,innodb_flush_log_at_trx_commit = 1, while slave does not need such high data security. You can set sync_binlog to 0 or close binlog,innodb_flushlog to 0 to improve the efficiency of sql execution. The other thing is to use a better hardware device than the main library as the slave.

Mysql-5.6.3 already supports multithreaded master-slave replication.

The concept of GTID

In the ordinary replication process, the slave library records and receives the event work progress of the master library binlog by recording the binlog file name and offset of the master library. The next time you start copying, inform the master library of this information so that the master library can start sending binlog events to the slave library from the correct location. However, GTID-based replication no longer needs to tell these things, and there is no need to specify the MASTER_LOG_FILE and MASTER_LOG_POS parameters when executing the CHANGE MASTER TO command. You only need to specify MASTER_AUTO_POSTION = 1, and the main library will decide where to start sending binlog events based on a GTID collection information sent from the library. It greatly simplifies the work of database administrators in replication.

GTID is the only identifier created when the database commits a transaction. The identifier is related to the transaction one by one.

The GTID consists of two parts, as follows:

GTID = source_id:transaction_id

Source_id is used to identify the database instance on which the transaction was executed. Uuid is used as the source_id.

Transaction_id is a sequence number, depending on the order in which the transaction was committed on the database. The serial number is initially 1.

In previous versions of MySQL5.6, synchronous replication was single-threaded and could only be performed one by one. Multi-threaded replication of multiple libraries was achieved in 5.6.

But it's important to note that. A library can only be copied by one thread. That is to say, if only one library is copied, then there is only one thread. There are 2 copied libraries. Then the number of threads can be increased to two.

The role of GTID can be summarized as follows:

1. Confirm the instance on which the transaction was originally committed according to GTID

The existence of 2.GTID facilitates Replication and failover.

At this point, I believe you have a deeper understanding of the "analysis of the inconsistency between master and slave synchronization in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.