How MySQL's master-slave replication is implemented

Preface

MySQL's master-slave replication is the MySQL itself with a feature, do not need additional third-party software can be implemented, its replication is not a copy file to achieve, but the use of the Binlog log file inside the SQL command to implement the master-slave replication, It can be understood that as I have executed a SQL command on the master side, the salve end will be executed again to achieve the effect of master-slave replication.

MySQL master-slave replication architecture diagram

• One Master one from

Master-slave-replication-01

• A master more from

master-slave-replication-02

• Cascade

master-slave-replication-03

• Double Master one from

master-slave-replication-04

• Mutual Master from

master-slave-replication-05

• Annular

master-slave-replication-06

Master-Slave replication principle

MySQL master-slave replication is an asynchronous replication process, the database from a master to the slave database, the master and slave to achieve the entire master-slave replication process is done by three threads, including two threads (SQL thread and IO thread) on the slave side, Another thread (IO thread) is on the master side.

Master-slave replication flowchart

Process Description:

MySQL master copy before we need to start the master database and then start the Salve database, and then execute in the Salve database, start slave; after the completion of the process is as follows:

1. The Salve IO thread reads the main library information that is configured in the Mastr.info file, such as the user name, password, port, and Binlog index location of the master database;
2. When you get the information, you're going to link Master's main repository io threads with information.
3. When the IO thread of the main library first checks that the configuration information passed by slave is correct, if it is correct, take the Binlog index position slave and compare it with the last index position in the Binlog file of the master library, and if you are caught in the same waiting state, Wait for master's Binlog index location to update;
4. If it's not the same, send slave. All SQL statements in the back of the Binlog index position, including the index location of the last SQL statement, sends an IO thread to slave;
5. After the slave IO thread Gets the information, the Binlog index passed by master is updated in the Master.info file of slave.
6. Then write the SQL statement from master to the relay file, and then continue to loop through the second step;
7. Slave SQL thread will continue to observe the relay log file for any changes, if not, keep listening;
8. If there is a change in the relay, the content that gets the change is converted to an SQL statement, and the SQL statement is executed in the Salve database.

How MySQL's master-slave replication is implemented