1. An overview of the
MySQL as a common database in back-end development, we use it a lot. An in-depth understanding of MySQL can make us more comfortable in the process of using MySQL for development. In the pursuit of high performance and high availability of MySQL, it is unavoidable to build MySQL clusters. Whether setting up hot backup for MySQL for high availability or setting up read and write copies of MySQL for high performance (as shown in the following figure), it is inevitable to use the MySQL replication function to realize data synchronization between two instances. It can be said that replication is the cornerstone of MySQL’s high availability, so what function points does it need to satisfy? How to do that? What are the pros and cons of the current implementation? Let’s take a look at the world of MySQL replication
Points of concern for replication implementations
- Data consistency (if the data is different between two instances, all other features are useless)
- Data synchronization speed (if data synchronization between two instances is too slow for hot spare switchover)
- Impact on normal requests (too costly if enabling replication causes the corresponding time of normal requests to double or longer)
2. Development history
2.1 a time line
| version | time | function |
|---|---|---|
| 3.23.15 | 2001 | Start supporting replication |
| 4.0.2 | 2002 | I/O threads are separated from SQL threads |
| 5.1.5 | 2006 | Binlog supports line mode |
| 5.5.0 | 2009 | Semi sync replication |
| 5.6.0 | 2011 | Delayed replication |
| 5.6.3 | 2011 | Library-based parallel replication |
| 5.6.5 | 2012 | GTID support |
| 5.7.2 | 2013 | Lossless replication |
| 5.7.5 | 2014 | Mutli source replication |
| X 5.7. | 2015 | Parallel replication based on group commit |
| 5.7.17 | 2016 | MySQL Group replication |
| 8.0.1 | 2017 | MGR& Parallel replication based on recordsets |
| 5.7.22 | 2018 | Recordset-based parallel replication |
2.2 Function Classification
Based on the various functions introduced by the timeline of replication development, and the time points that replication functions need to focus on, I made a simple classification of function points.
Data synchronization format
- Row-based replication
- Statement based replication
- Mixed copy
Data Synchronization mode
- Basic mode
- Library-based parallel replication
- Parallel replication based on group commit
- Recordset-based parallel replication
Timing of data synchronization
- Asynchronous replication
- Full synchronous replication
- Semi-synchronous replication
- Nondestructive copy
Other features
- Delayed replication
- Global Transaction ID
- Multi Source Replication
- MySQL Group Replication
3. Pre-basic knowledge
Before we begin, we need to prepare some preliminary knowledge to better understand the implementation details of the replication feature.
3.1 the MySQL architecture
The overall architecture of MySQL is divided into two layers, one layer is the Server layer containing connectors, query cache (to be cancelled), analyzer, optimizer, and executor, and the other layer is the engine layer providing pluggable engines (InnoDB, MYISAM). What we need to focus on here is that the Server layer is responsible for writing BinLog, while InnoDB in the lower engine layer writes UndoLog and RedoLog.
3.2 Commit Two-stage process
Two-phase commit is used in MySQL transaction implementation.
-
Execution Stage (in memory, involving various locks required by the transaction)
-
Prepare Stage (engine layer write RedoLog)
-
Binlog Stage (Server layer says BingLog)
-
Innodb Commit Stage (write Commit log)
3.3 group submitted
Logs such as binLog or redoLog need to be persisted to disk in a transaction, but synchronization to disk is a time-consuming operation. If each transaction performed a synchronization, there would be a significant cost to performance, hence the group commit technique. Group commit merges multiple flush operations into one. If the time cost of 10 transactions queuing up to flush is 10, then the time cost of flushing all 10 transactions at one time is approximately 1.
Group submitted implementation details can refer to this article: developer.aliyun.com/article/617…
4. Basic model
-
The primary node must enable binary logging to record any events that modify database data.
-
Start a Thread from a node (I/O Thread) that acts as a client of mysql and requests events from the binary log file of the primary node through the mysql protocol
-
The master node starts a dump Thread to check the events in its binary log against the requested location. If there is no request location parameter, the master node sends the first event in the first log file to the slave node one by one.
-
The data received from the primary node is placed in a Relay log file. And record the location within the specific binary log file that the request was sent to on the master node (there can be multiple binary files in the master node, as explained later).
-
Start another Thread (SQL Thread) from the node, read events from the Relay log, and execute them locally again.
In the original replication implementation version, I/O thread and SQL thread are one thread. Later, I/O thread and SQL thread are separated, but the I/O thread and SQL thread are still single thread. The Master instance executes concurrently with multiple threads, while the Slave executes only with a single thread, resulting in severe master-slave latency when Master concurrency is high in this mode.
5. Data synchronization format
There are three types of data synchronization:
-
Statement based replication
-
Row-based replication
-
Mixed copy
5.1 Statement based Replication
The principle of statement-based replication is that the slave database replicates from the master database based on the SQL statements that produce changes. Prior to MySQL5.1.4 was the only supported mode for binlog and replication and was the default format in MySQL5.5.
advantages
-
Proven and mature technology
-
Less data generated
For example, delete all the records of a table, say 10000 rows, based on the statement only need to write one SQL, row-based need to write the changes of each row
- Because the binLog file records all changes, it can be used to audit the database
disadvantages
- Data inconsistencies may occur based on statement replication.
For example, if the UUID function is executed on the primary library, the data generated by each execution is different, resulting in inconsistencies between the primary and secondary data. There are some other situations that you can refer to the MySQL official documentation.
- Complex statements need to be parsed and optimized before Slave execution.
5.2 Row-based Replication
Row-based replication does not copy SQL statements, but copies rows for insert, delete, or update operations.
advantages
-
All changes can be replicated, which is the safest replication mode.
-
The row-based implementation is the same technique as most other databases.
-
Fewer row locks are required on the Master, resulting in higher concurrency.
disadvantages
-
More data needs to be written
-
Large SQL generates a large number of row record changes, resulting in slow replication
-
You can’t see from the binLog which specific statements were executed (there are currently tools that can translate row records)
-
Concurrent inserts to MyISAM tables do not support row-based mode.
5.3 Replication Based on Mixed Mode
The principle behind mixed-mode replication is simple: Statement-based replication is used normally, and row-based replication is switched over for insecure statements.
5.4 Replication mode currently in mainstream use
The most commonly used pattern is still the row copy pattern, although the hybrid pattern can combine the advantages of both line – and statement-based patterns. I understand because in our daily use, MySQL replication is not only used for replication between master and slave instances, but also for data synchronization between different storage media. For example, we sometimes synchronize MySQL BinLog to ES for searching, and MySQL to Kafka for streaming computing. So you need a uniform format.
6. Data synchronization mode
There are four types of data synchronization:
-
Basic mode
-
Library-based parallel replication
-
Parallel replication based on group commit
-
Recordset-based parallel replication
6.1 Basic Mode
The basic mode is the basic model mentioned in Section 4. The problems of the basic model and the disadvantages of the basic model are also mentioned, that is, the I/O threads and SQL threads of the Slave instance are single threads. When the master concurrency is high, There will be a large master-slave delay with the Master.
6.2 Library-based Parallel Replication
The principle of library-based parallel replication is very simple: when changes are executed on the Slave, changes from different libraries can be executed concurrently. Because MySQL does not have cross-library transactions and there is no competition between different libraries, it can be directly executed concurrently.
Library-based parallel replication does not solve many problems, because nowadays, with high concurrency, we often put important libraries on a single instance, which is when library-based parallel replication fails to work. There is still a high risk of master-slave delays.
6.3 Parallel Replication Based on Group Submission
Understanding group commit based parallel replication requires the COMMIT two-phase process and group commit mentioned earlier. That is, when the binLog is written to the disk on Master, it is in the binLog Stage of the two-phase commit process. At this stage, lock contention is complete, so binLog transactions written concurrently on Master can be executed concurrently on Slava. When binLog is written, a last_COMMIT field is written to the row change record, which is the commit number of the last group commit.
Such as:
Initialize last_commit = 0
First group submission:
Transaction one and transaction two are committed concurrently, writing two records.
Last_commit = 0
Last_commit = 0
Last_commit = 1 after the first group commit is written
Second Group submission:
Transactions three and four are committed concurrently, writing two records.
Last_commit = 1
Last_commit = 1
When executed on the Slave, transaction 1 and transaction 2 (last_COMMIT) can be executed concurrently. After transactions one and two are completed, transactions three and four can be executed concurrently.
Any questions about concurrent replication based on group commits?
While group commit solves some of the problems in high concurrency scenarios, there are still some problems left unresolved. For example, if we need to build a slave library from zero, this is when some SQL on the master library may not have concurrent contention and can be executed concurrently. However, because it is executed over different time periods, it is not in the same transaction group. This results in sequential execution only. So is there any way to solve this problem? There is, of course, the recordset-based parallel replication mentioned below.
6.4 Recordset-based Parallel Replication
Recordset-based parallel replication does not change at the Slave execution stage, but rather changes the last_COMMIT implementation when binLog is written to the Master. As you can see, when the Master writes to the BinLog, there is a Hash table and then a value of m_writeset_history_start. When our transaction writes a change, an algorithm computs a hash value based on the index, primary key, and other data involved in the current transaction. If not, use m_writeset_history_start as the value of last_COMMIT.
As shown in figure:
(select last_commit = 1, last_commit = 1, last_commit = 1
Last_commit = 1 (select last_commit = 1, last_commit = 1)
Last_commit = 1 (select last_commit = 1, last_commit = 1)
This allows all three transactions to be executed concurrently from the library even if they are not in the same group commit transaction.
6.5 Which one should I use?
See replication performance comparison: blogs.oracle.com/mysql/post/…
7. Timing of data synchronization
There are four types of timing for data synchronization:
-
Asynchronous replication
-
Full synchronous replication
-
Semi-synchronous replication
-
Nondestructive copy
7.1 Asynchronous Replication
- Principle: In asynchronous replication, the master writes data to the binlog and synchronizes it. The slave requests the binlog and writes the data to the relay log and flush the disk
- Advantages: The replication performance is the best
- Disadvantages: The slave may lose transactions if the master fails
7.2 Synchronous Replication
- Principle: In full synchronous replication, the master writes data to the binlog and syncs, and all slave requests to the binlog are written to the relay log and flush disk, and the logs are replayed and committed
- Advantages: Data is not lost
- Disadvantages: Blocking the master session, poor performance, very network dependent
7.3 Semi-Synchronous Replication
- Principle: In semi-synchronous replication, the master writes data to binlog, sync, commit, and wait for ACK. When at least one slave request bilog is written to relay log and flush disk, ack is returned.
- Advantages: Risk of data loss (low)
- Disadvantages: Blocking master sessions, poor performance, very network dependent,
- Important: Since the master waits until the last commit phase is completed, other sessions of the master can see the commit transaction. Therefore, data on the master is inconsistent with that on the slave. After the master crashes, data on the slave is lost
7.4 Lossless Replication
- In semi-synchronous replication, the master writes data to binlog and syncs, and waits for ACK. When at least one slave request bilog is written to relay log and flush disk, ack is returned.
- Advantages: Zero data loss (if the data is always Lossless Replication) and good performance
- Disadvantages: blocks the master session and is very network dependent
- Important: Since the master waits for the second phase of the three-segment commit after the Sync binlog is completed, other sessions of the master cannot see the commit transaction, so the data on the master is the same as that on the slave. After the master crashes, the slave does not lose data
8. Other functions
There are a few other features that haven’t found their classification here:
-
Delayed replication
-
Global Transaction ID
-
Multi Source Replication
-
MySQL Group Replication
8.1 Delayed Replication
You can customize the replication delay time for the secondary database. For example, if you set the replication delay to one day, you can keep the copy of the database from one day ago to quickly roll back the database status
8.2 Global Transaction ID
Before GTID, master/slave replication depended on binLog offset to determine the progress of replication. However, since the binLog offset of the master and slave libraries may be different, it is difficult to determine the current replication progress when a master/slave switchover occurs. With GTID, transactions can be globally unique and easily switched.
GTID formats:
GTID = source_id:transaction_id (transaction_id cannot be 0, it is a positive integer)
Generation and life cycle of GTID:
-
A transaction executes on the Master, generates a GTID, and writes to the Master.
-
The Master binary log is propagated to other Slave machines and then saved in the relay log. GTID is then read as gtid_next
-
Ensure that the Slave has not executed the same GTID before, nor has it executed the same GTID simultaneously
-
GTID Writes the Slave binary log
8.3 Multi Source Replication
A Slave can copy data from multiple masters, covering replica machine costs.
MySQL 8.4 Group Replication
No studies yet
9. Reference materials
MySQL architecture: zhuanlan.zhihu.com/p/338685772
Group commit: developer.aliyun.com/article/617…
3.23 documents: download. Nust. Na/pub6 / mysql /…
The concrete implementation: download. Nust. Na/pub6 / mysql /…
5.6 copy the relevant official documents: dev.mysql.com/doc/refman/…
5.7 Copying Official Documents:
Replication: dev.mysql.com/doc/refman/…
Group the Replication: dev.mysql.com/doc/refman/…
8.0 Copy relevant official documents:
Replication:dev.mysql.com/doc/refman/…
About the binLog synchronous format specification (based on 5.1 user manual) : download. Nust. Na/pub6 / mysql /…
About binLog line models and statement of the advantages and disadvantages of contrast (based on the user manual) 5.1: the download. Nust. Na/pub6 / mysql /…
In Master in Mysql 5.5 downtime is how to switch (not yet introduced GTID) : download. Nust. Na/pub6 / Mysql /…
About half a synchronous submit in section 5.5: docs.oracle.com/cd/E19957-0…
Mysql 5.6 support of Replication:dev.mysql.com/doc/refman/…
Mysql 5.6 supports GTID:dev.mysql.com/doc/refman/…
Mysql 5.6 based on the parallel library submission: dev.mysql.com/doc/refman/…
Mysql 5.7 Master after downtime of master-slave switch: dev.mysql.com/doc/refman/…
The evolution of the parallel copying: mysql.taobao.org/monthly/201…
Mysql > select * from WorkLog;
WL# : 6813 MTS: ordered commits (sequential consistency) : dev.mysql.com/worklog/tas…
WL#6314:MTS:Prepared transactions slave parallel applierhttps://dev.mysql.com/worklog/task/?id=6314
WL# : 7165 MTS: Optimizing the scheduling (MTS) by increasing the parallelization window on master:dev.mysql.com/worklog/tas…
@ l # 9556: Writeset – -based MTS dependency tracking on master:dev.mysql.com/worklog/tas…