MySQL master-slave synchronization principle and application

1. Master-slave synchronization principle

Master-slave synchronization architecture diagram (asynchronous synchronization)

This is the most common master-slave synchronization architecture.

Master-slave synchronization process (asynchronous synchronization)

• The main database writes data changes to the binlog file
• Initiate a dump request from the library I/O thread
• The master I/O thread pushes binlog to the slave
• Write the local relay log file from the library I/O thread (same format as binlog )
• The slave SQL thread reads relay log and re-executes it serially to obtain the same data as the master database.

What is binlog?

Every time the main database commits a transaction, it records the data changes in a binary file called binlog . Note: Only write operations are recorded in binlog , read-only operations (such as select and show statements) are not.

Three formats of binlog

Statement format: binlog records the actual executed SQL statements
Row format: binlog records the data before and after the change (involving all columns), such as update table_a set col1=value1 , col2=value2 ... where col1=condition1 and col2=condition2 ...
Mixed format: statement format is selected by default, and row format is used only when necessary

Binlog Format Comparison

• Statement level: The advantage is that the binlog file is small, but the disadvantage is that the slow SQL of the master database will also appear again on the slave database, and some functions that depend on the environment or context may produce inconsistent data
• Row level: The disadvantage is that the file is large (if a statement involves multiple rows, it will be enlarged n times), the advantage is that there is no slow SQL problem mentioned above, and it does not depend on the environment or context
• In order to obtain the before and after change data, canal recommends using row level

Two ways of master-slave synchronization

• Asynchronous synchronization: The default mode may cause data loss during master-slave switching. Because whether the master database is commit has nothing to do with the master-slave synchronization process and is not perceived.
• Semi-synchronous: A high-availability solution supported by newer mysql versions. It requires at least one slave (the default is 1, and the specific number can be specified) to ack the write to relay log before the master commit and returns the result to client .

Master-slave synchronization process (semi-synchronous)

• When connecting to the master, the slave indicates that it supports semi-synchronous replication
• The master database also needs to support semi-synchronous replication. Before commit a transaction, the master database will block and wait for at least one slave database to write ack to relay log until it times out.
• If the blocking wait times out, the master database temporarily switches back to asynchronous synchronization mode. When at least one slave database's semi-synchronization catches up with the progress, the master database switches back to semi-synchronization mode.

Semi-synchronous application scenarios

High-availability backup: Semi-synchronous replication ensures the consistency between the slave and master databases. When the master database fails, switching to the slave database will not cause data loss. In order to ensure stability (so as not to drag down the main database due to slow semi-synchronization), it generally does not bear business traffic, ack as quickly as possible, and is only used for synchronization and backup.

2. Master-slave synchronization application scenario

Common scenarios : asynchronous synchronization of online slaves and semi-synchronization of high-availability backups

Large data acquisition requirements with high consistency requirements

Large data retrieval may cause the CPU usage of the slave database to soar and ack to slow down. The number of acks required for semi-synchronization can be set to 1. Under normal circumstances, the high-availability backup can ack quickly, so the master database will commit and return, and it does not matter if the large data retrieval and replication are slower. In this way, the main database and business will not be affected by the slow ack of large data retrieval.

This is the end of this article about the master-slave synchronization principle and application of MySQL. For more relevant content about the master-slave synchronization principle and application of MySQL, 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!