Shulou(Shulou.com)06/01 Report--

This article is to share with you about how mysql master-slave replication works. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The principle of mysql master-slave replication: first, the master library sends update events to the slave library; then the update record is read from the library, and the update record is executed; finally, the content of the slave library is consistent with the master library.

The principle of mysql master-slave replication:

Why do you want to copy from master?

In a system with complex business, there is a scenario in which a sql statement needs to lock the table, resulting in the temporary inability to use the read service, which will affect the running business. Use master-slave replication, and let the master database be responsible for writing and the slave database for reading. In this way, even if the master database locks the table, the normal operation of the business can be ensured by reading the slave database.

As a hot backup of data, the main database can be replaced in time after downtime to ensure business availability.

An extension of the architecture. Due to the increasing volume of business, the access frequency of Iamp O is too high to be satisfied by a single machine. At this time, the storage of multiple databases can be done to reduce the access frequency of disk Iamp O and improve the performance of single machine.

The process of MySQL master-slave replication

Update events (update, insert, delete) of the main library db are written to binlog

The master library creates a binlog dump thread to send the contents of the binlog to the slave library

Start from the library and initiate a connection to the main library

After booting from the slave library, create an binlog O thread that reads the content of the main library and writes it to relay log

After starting from the library, create a SQL thread that reads from the relay log, executes the read update event from the Exec_Master_Log_Pos location, and writes the update content to the db of slave

Note: the above process is relative, not absolute.

The principle of MySQL master-slave replication

MySQL master-slave replication is an asynchronous replication process, the master library sends update events to the slave library, reads the update record from the slave library, and executes the update record to make the content of the slave library consistent with that of the master library.

Binlog:binary log, which holds binaries for all update event logs in the main library. Binlog is a file that keeps records of all changes in the database (database structure and content) from the moment the database service starts. In the master library, whenever an update event occurs, it is written to the binlog in turn, and then pushed to the data source from the library as a copy from the library.

Binlog output thread: whenever a slave library connects to the master library, the master library creates a thread and sends binlog content to the slave library. For each sql event to be sent to the slave library, the binlog output thread locks it. Once the event is read by the thread, the lock is released, even when the event is fully sent to the slave library.

In the slave library, when replication begins, the slave library creates the slave library Imax O thread and the slave library SQL thread for replication processing.

Slave START SLAVE O thread: after the execution of the slave statement, a slave database is created. The thread connects to the master library and requests the master library to send update records in binlog to the slave library. The updates sent by the binlog output thread of the main library are read from the library Iswap O thread and copied to the local file, including the relay log file.

SQL thread from the library: create a SQL thread from the library that reads and executes the update event written from the library Imax O thread to relay log.

To sum up, we can know:

For each master-slave replication connection, there are three threads. The master library with multiple slave libraries creates an binlog output thread for each slave library connected to the master library, each with its own I / O thread and SQL thread.

The slave library separates the read and write from the library when copying by creating two separate threads. Therefore, even if the thread responsible for execution runs slowly, the thread responsible for reading the update statement does not become slow as a result. For example, if the slave library hasn't been running for a while, its binlog thread can quickly read all SQL content from the main library, even though its SQL thread executes slowly. In this way, even if the slave library stops running before the SQL thread finishes executing all the read statements, the Ibig O thread at least fully reads everything and backs it up safely in the relay log local to the slave library, ready to execute the statement the next time the slave library starts.

Thank you for reading! The working principle of mysql master-slave replication is shared here. I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people 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.