• notice
  • Congratulations on the launch of the Sought Tech site

Detailed explanation mysql realizes the master-slave replication process

One.What is master-slave replication?

Transfer the DDL and DML operations in the master database to the slave database through the binary log (BINLOG), and then re-execute (redo) these logs; so that the data in the slave database is consistent with the master database.

Second, the role of master-slave replication

1.If there is a problem with the master database, you can switch to the slave database.

2, the read and write separation at the database level can be carried out,

3.Daily backups can be performed on the slave database

3.Copy process

Binary log: Binary log of the main database

Relay log: Relay log from the server

First step:The master writes the operation record serially to the binlog before each transaction update data is completed File.

The second step:salve opens an I/O Thread, which opens an ordinary connection in the master, the main job is binlog dump process.If the read progress has kept up with the master, it enters the sleep state and waits for the master to generate new events.The ultimate goal of the I/O thread is to write these events to the relay log.

The third step:SQL Thread will read the relay log and execute the SQL events in the log in sequence, thereby interacting with The data in the main database remains consistent.

Four.Specific operations of master-slave replication

I installed two msyql instances under different paths on the same windows.It is recommended that the installation versions of the master and slave mysql are the same, although my own is inconsistent.

1.Modify the configuration file my.ini of the master and slave database respectively


3306 is the default port number of mysql, here the master instance does not need to be modified; server- id is used to specify a unique id, and different mysql instances do not need to be repeated; binlog-do-db specifies the database to be replicated; log-bin is used to open the binary log file.


Because the master-slave database will run on the same computer in the future, the port needs to be set differently, here is 3307

replicate-do-db: The name of the database to be synchronized, which is consistent with the configuration on the master.

2.Create an account on the master specifically for replication: weidai/123456

This new account can be queried in the table mysql.user:

The first time I did this, I completed the creation of this account here, but when I actually copied it, I found that the copy was unsuccessful.Troubleshoot When it is wrong, it is found that the binlong generated by the master has no problem, and then check the status of the slave:

There is such an error at the end:

Using the Weidai account cannot connect to the master, so the binlog of the master should not be obtained, which causes the relay log to fail to be generated.

I checked the account and password repeatedly and found no problems, and then searched for related information, only to find that it was because the master lost one step when creating a new user:

After setting up a new user or changing a password, flush privileges must be used to refresh the MySQL system privilege table, otherwise access will be denied.This is the reason for the previous error.Another way is to restart the mysql server to make the new settings take effect.

3.Get the location of the data in the main database at the moment, which is mainly used to copy the starting position of the data after the data is started, but before the status value is obtained, the main database can no longer modify the data Operation, so you need to set the read lock to be valid first

4.The main database backs up data.There are many ways to back up.I won’t introduce them here.You can refer to my last article.After the backup is over, the read lock can be released and the main database can perform write operations.

5.Start the slave database and restore the data that was just backed up.At this time, the data of the master-slave database at the time of the backup is consistent.

6.Relevant configuration of replication behavior on the slave database

7.At this time, the configuration is complete, but the slave database cannot be synchronized yet, and the slave thread needs to be started

8.Create a table and add data in the master, and observe in the slave:

It can be seen that all the operations I perform in the master can be reflected in the slave.At this time, the slave is like a mirror of the master.

5.Interpretation of master-slave synchronization status

Use commands to view on slave:

Because the typesetting is too ugly, I organize it as follows:

Slave_IO_STATE: Waiting for master to send event











----------------------------------------------- -----------Gorgeous dividing line----------------------------------- --------



As mentioned earlier, these two threads are two very important threads involved in the replication process on the slave.YES means normal, NO means abnormal.

The Slave_IO thread mainly copies the binlong log content on the master to the relay log (Relay_log) of the slave.Generally, the probability of problems is not high.Most of the problems are due to permissions or network problems, which cause the connection to fail.master.As the error mentioned earlier.

The Slave_SQL thread is responsible for executing the SQL in the relay log, and the probability of error is relatively higher.For example, someone manually inserts some records in the slave library, causing a primary key conflict during the master-slave synchronization.

Slave_IO_STATE:Waiting for master to send event

This state indicates that the relay log synchronization is completed, waiting for the master to have a new event.


Technical otaku

Sought technology together

Related Topic


Leave a Reply