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

This article will explain in detail how to achieve master-slave synchronization in MySQL. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope that after reading this article, you will have a certain understanding of relevant knowledge.

(1)statement: sql that records each piece of changed data;

Advantages: binlog file is small, I/O saving, high performance.

Disadvantages: Not all data changes will be written to the binlog file, especially using some special functions in MySQL (such as LOAD_FILE(), UUID(), etc.) and some uncertain statement operations, resulting in the problem that master-slave data cannot be copied.

(2)row: do not record sql, only record the change details of each row of data

Pros: Each row of data changes are recorded in detail, which also means that there are no problems that cannot be copied due to the use of special functions or other circumstances.

Disadvantages: Because row format records the details of changes in each row of data, it produces a lot of binlog content, poor performance, and increases the probability of master-slave synchronization delay.

(3)mixed: general statement modification uses statement format to save binlog, such as some functions, statement can not complete the master-slave copy operation, then use row format to save binlog, MySQL will distinguish the log form to be treated according to each specific sql statement executed, that is, choose one between Statement and Row.

â-o December (2)

The mysqlbinlog command looks at the following:

Binlog contents viewed by event type:

(iii)binlog event type

All operations recorded in MySQL binlog actually have corresponding event types. For example, DML operations in STATEMENT format correspond to QUERY_EVENT type, and DML operations in ROW format correspond to ROWS_EVENT type. If you want to know more, please refer to the official documentation. The contents of binlog are not described too much here. A brief introduction is to better understand the details of master-slave replication. Let's get to the point below.

4. MySQL master-slave replication principle

MySQL master-slave replication requires three threads: master(binlog dump thread), slave(I/O thread, SQL thread).

master

(1)binlog dump thread: When there is data update in the master library, the master library will write the event type of this update to the binlog file of the master library according to the set binlog format. At this time, the master library will create a log dump thread to notify the slave that there is data update. When the I/O thread requests the log content, the binlog name and the current update location will be transmitted to the slave I/O thread at the same time.

slave

(2)I/O Thread: This thread will connect to master, request a copy of the binlog file location from log dump thread, and save the requested binlog to local relay log. Relay log, like binlog log, also records data update events. It also generates multiple relay log( host_name-relay-bin.00001) files according to the increasing suffix name. Slave will use an index file ( host_name-relay-bin.index) to track the relay log file currently in use.

(3)SQL thread: After detecting that the relay log has been updated, this thread will read and perform redo operation locally, and execute the events that occurred in the main database again locally to ensure the synchronization of master-slave data. In addition, if all events in a relay log file are executed, the SQL thread automatically deletes the relay log file.

Below is a schematic diagram of the entire replication process:

IV. Master-slave synchronization delay

Master-slave replication of mysql is a single-threaded operation. The master library generates binlogs for all DDL and DML. Binlogs are written sequentially, so the efficiency is very high. The slave I/O thread fetches logs from the master library. The efficiency is also relatively high. However, the slave SQL thread implements the DDL and DML operations of the master library in the slave. The IO operations of DML and DDL are random, not sequential, and the cost is much higher. There may also be lock contention caused by other queries on the slave. Since SQL is also single-threaded, a DDL is stuck and needs to execute a long period of events. Subsequent DDL threads will wait for the DDL to execute, which leads to latency. When the TPS concurrency of the main library is high, the number of DDLs generated exceeds the range that a SQL thread of slave can bear, and delays occur. In addition, there may be lock waiting caused by large query statements of slave.

Since master-slave synchronization delays exist objectively, we can only design from our own architecture and try to make the DDL of the master library execute quickly. Here are a few common solutions:

The persistence layer of the business is implemented using a sub-library architecture, and mysql services can be extended in parallel to disperse pressure.

The infrastructure of the service adds a memcache or Redis cache layer between the business and mysql. Reduce the reading pressure of mysql;

Use better hardware devices than the master library as slaves;

sync_binlog is set to 0 on the slave side;

- logs-slave-updates Updates received by the slave server from the master server are not logged in its binary log.

Disable slave binlog

How to achieve master-slave synchronization in MySQL is shared here. I hope the above content can be of some help to everyone and learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.