Mysql master-slave replication principle: First, the master library sends update events to the slave library; then the slave library reads the update record and executes the update record; finally, the contents of the slave library are consistent with the master library.

##Related free learning recommendations: mysql video tutorial

mysql master-slave replication principle:

Why do master-slave replication

• In complex business In the system, there is such a scenario. There is a SQL statement that requires locking the table, resulting in the temporary inability to use the read service. This will greatly affect the running business. Use master-slave replication to let the master database be responsible for writing and the slave database be responsible for reading. In this way, even if the master database locks the table, the normal operation of the business can be guaranteed by reading from the slave database.

• Do hot backup of data. After the main database goes down, the main database can be replaced in time to ensure business availability.

• Extension of the architecture. The business volume is getting larger and larger, and the frequency of I/O access is too high, which cannot be satisfied by a single machine. At this time, multi-database storage is used to reduce the frequency of disk I/O access and improve the I/O performance of a single machine.

MySQL master-slave replication process

Update events of the main database db (update, insert, delete) is written to the binlog

The main library creates a binlog dump thread and sends the contents of the binlog to the slave library

The slave library starts and initiates a connection to connect to the main library

After the slave library is started, create an I/O thread, read the binlog content from the main library and write it to the relay log

After the slave library is started, create an SQL thread and read from the relay log Content, execute the read update event starting from the Exec_Master_Log_Pos position, and write the updated content to the slave's db

Note: The above process is a relative process, not an absolute process

MySQL The principle of master-slave replication

MySQL master-slave replication is an asynchronous replication process. The master database sends update events to the slave database, and the slave database reads the update records and executes the update records, so that the slave database The content is consistent with the main library.

binlog: binary log, a binary file that saves all update event logs in the main library. Binlog is a file that saves all change records (database structure and content) of the database from the moment the database service is started. In the main library, as long as an update event occurs, it will be written to the binlog in sequence, and then pushed to the slave library as the data source for replication from the slave library.

Binlog output thread: Whenever a slave library connects to the main library, the main library will create a thread and send the binlog content to the slave library. For each SQL event that is about to be sent to the slave library, the binlog output thread will lock it. Once the event has been read by the thread, the lock will be released, even when the event is completely sent to the slave library, the lock will be released.

In the slave library, when replication starts, the slave library will create the slave library I/O thread and the slave library's SQL thread for copy processing.

Slave library I/O thread: When the START SLAVE statement starts executing from the slave library, the slave library creates an I/O thread, which connects to the main library and requests the main library to send the update records in the binlog to From the library. The slave library I/O thread reads the updates sent by the main library's binlog output thread and copies these updates to local files, including relay log files.

SQL thread from the library: Create a SQL thread from the library. This thread reads the update events written to the relay log by the I/O thread of the library and executes them.

To sum up, it can be seen that:

There are three threads for each master-slave replication connection. A main library with multiple slave libraries creates a binlog output thread for each slave library connected to the main library. Each slave library has its own I/O thread and SQL thread.

By creating two independent threads, the slave library separates the reading and writing of the slave library during replication. Therefore, even if the thread responsible for execution runs slower, the thread responsible for reading the update statement does not become slower. For example, if the slave library has not been running for a while, when it is started here, although its SQL thread executes slowly, its I/O thread can quickly read all binlog contents from the main library. In this way, even if the slave library stops running before the SQL thread finishes executing all the read statements, the I/O thread has at least fully read all the contents and safely backed up them in the local relay log of the slave library. , ready to execute statements the next time the slave library is started.

The above is the detailed content of What is the principle of mysql master-slave replication?. For more information, please follow other related articles on the PHP Chinese website!