Detailed explanation of the role and working principle of MySQL master-slave replication

1. What is master-slave replication?

Master-slave replication is used to establish a database environment that is exactly the same as the master database, called a slave database. The master database is generally a quasi-real-time business database. In the most commonly used MySQL database, single-item, asynchronous assignment is supported. During the migration process, one server acts as the master server and the other server acts as the slave server; the master server writes the update information to a special binary file.

An index of files is maintained to track log cycles. This log can record and send updates to the slave server. When a slave server connects to the master server, the slave server notifies the master server to read the location of the last successful update in the slave server's log file. The slave will then receive any updates that occur from that point on, and then lock and wait until the master notifies it of new updates.

2. The role of master-slave replication

The first is to ensure data security; do hot backup of data as a backup database. When the main database server fails, you can switch to the slave database to continue working and avoid data loss.

The second is to improve I/O performance. As the business volume in daily production increases, the frequency of I/O access becomes higher and higher, which cannot be met by a single machine. At this time, multi-library storage can effectively reduce the frequency of disk I/O access and improve the I/O performance of a single device.

The third is the separation of reading and writing, which enables the database to support greater concurrency; this is especially important in reporting. Because some report SQL statements are very slow, the table is locked, affecting the front-end service. If the front desk uses the master and the report uses the slave, then the report SQL will not cause the front desk to lock, thus ensuring the front desk speed.

3. The principle of master-slave replication

Files involved in master-slave replication

Main library: binlog

From the library:

• relaylog relay log
• master.info master database information file
• relaylog.info relaylog application information

Three threads involved in master-slave replication

Main library:

Binlog_Dump Thread:

From the library:

SLAVE_IO_THREAD

SLAVE_SQL_THREAD

The specific principle is shown in the figure:

1. Execute the change master to command from the database (the connection information of the master database + the starting point of the replication)
2. The above information will be recorded in the master.info file from the database
3. Execute the start slave command from the database to immediately start the two threads SLAVE_IO_THREAD and SLAVE_SQL_THREAD

4. From the database SLAVE_SQL_THREAD, read the information in the master.info file to obtain the IP, PORT, User, Pass, and binlog location information

5. The slave database SLAVE_IO_THREAD requests to connect to the master database. The master database provides a SLAVE_IO_THREAD to interact with the SLAVE_SQL_THREAD.

6.SLAVE_IO_THREAD requests the new binlog of the master database based on the location information of the binlog

7. The master database uses Binlog_DUMP_Thread to send the latest binlog to the SALVE_IO_THREAD of the slave database through network TP

8.SLAVE_IO_THREAD receives the new binlog, stores it in the TCP/IP cache, immediately returns ACK to the master, and updates master.info

9. SLAVE_IO_THREAD dumps the data in the TCP/IP cache to the disk relaylog.

10.SLAVE_SQL_THREAD reads the information in relay.info and obtains the location information of the relaylog that was applied last time

11.SLAVE_SQL_THREAD will replay the latest relaylog according to the last position and update the relay.info information again

12. The database will automatically purge the application relay for regular cleanup

Once the master-slave replication is successfully established, any new changes in the master database will be sent to the SLAVE_IO_THREAD through the slave_dump_THREAD, which enhances the real-time nature of the master-slave replication.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.