Mysql Master-slave replication principle

Mysql Master-slave replication principle

MySQL's master-slave replication is an asynchronous replication process (although it is generally felt to be real-time), the data will be copied from a MySQL database (what we call Master) to another MySQL database (what we call slave), The process of implementing the entire master-slave replication between master and slave is done by three threads.　　There are two threads (SQL thread and IO thread) on the slave side, and another thread (I/O thread) on the master side. To achieve MySQL master-slave replication, you must first open the master side of the Binlog recording function, otherwise it will not be implemented. Because the entire replication process is actually slave to get binlog logs from the Aster side, and then perform the various SQL operations in the same order to get the records in the Binlog log on slave

1) Execute the sart slave command on the slave server to turn on the master-slave copy switch and start the master-slave replication.

2) At this point, the IO thread of the slave server connects to the master server by requesting a copy user right that is already authorized on master, and requests a specified location from the execution of the Binlog log file (the log file name and location are the change that is performed when the master-slave Replication Service is configured
The Binlog log content is sent after the master command has been specified

3) After the master server receives a request from an IO thread from the slave server, the IO thread on which the copy is responsible reads the Binlog log information from the specified Binlog log file in batches according to the information requested by the IO thread of the slave server. The IO thread is then returned to the slave side. In addition to the Binlog log content, there is an IO thread recorded on the master server side of the returned information. The returned information is in addition to the next specified update location in Binlog.

4) When the IO thread of the slave server acquires the log content, log files, and location points sent by the IO thread on the master server, the Binlog log contents are written sequentially to the relay of the slave side itself. The terminal of the log (that is, the trunk log) file (mysql-relay-bin.xxx), and records the new Binlog file name and location to the Master-info file so that the next time you read the new Binlog log on the master, you can tell the master server to specify from new Binlog log File and location start to read new Binlog log content

5) Slave server-side SQL thread detects the new log contents of the IO thread in the local relay log in real time, then resolves the contents of the relay log file into SQL statements and executes the SQL statements in the order in which the SQL statements are parsed on the slave server itself. , and record the file name and location point of the current application's relay log in Relay-log.info

Mysql Master-slave replication principle