Detailed example of how MySQL implements master-slave replication process (picture)

This article mainly introduces the implementation process of MySQL master-slave replication in detail, which has certain reference value. Interested friends can refer to it

1. What is master-slave Replication

Transmit the DDL and DML operations in the primary database to the secondary database through binary logs (BINLOG), and then re-execute (redo) these logs; thus making the data from the secondary database consistent with the primary database The database remains consistent.

2. The role of master-slave replication

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

2. Read and write separation can be performed at the database level.

3. Daily backup can be performed on the secondary database.

3. Replication process

Binary log: Binary log of the master database

Relay log: Relay log of the slave server

Step one: master writes the operation record serially to the binlog file before each transaction update data is completed.

Second step: salve opens an I/O Thread. This thread opens a normal connection on the master and its main job is the binlog dump process. If the reading progress has caught up with the master, it enters sleep state and waits for the master to generate new events. The ultimate purpose of the I/O thread is to write these events to the relay log.

Step 3:SQL Thread will read the relay log and execute the SQL events in the log sequentially to be consistent with the data in the main database .

4. Specific operations of master-slave replication

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

#1. Modify the configuration files my.ini of the master and slave databases respectively

master

3306 is the default port number of mysql, which does not need to be modified in the master instance; server-id is used to specify a unique ID, and different mysql instances do not need to be repeated; binlog-do-db needs to be copied if specified database; log-bin is used to open binary log files.

salve

Since the master-slave database will be run on the same computer later, the port needs to be set to different Same, here is 3307

replicate-do-db: the name of the database that needs to be synchronized, consistent with the configuration on the master.

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

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

When I first operated, I completed the creation of this account here, but when I actually copied it, I found that there was no copy. Successfully, when troubleshooting the error, it was found that there was no problem with the binlong generated by the master, and then checked the status of the slave:

There is such an error line at the end:

Using the Weidai account cannot connect to the master, so the binlog of the master should not be obtained, resulting in the relay log not being generated.

I repeatedly checked the account and password and found no problems. Then I searched for relevant information and found out that it was because there was a missing step when the master created a new user:

Set up a new user or change it After entering the password, you need to use flush privileges to refresh MySQL's system permissions related tables, otherwise access will be denied. This is why the previous error occurred. Another way is to restart the mysql server to make the new settings take effect.

3. Obtain the position of the data in the master database at this moment. It is mainly used to copy the starting position of the data after starting from the data. However, before obtaining this status value, the master The database can no longer have data modification operations, so it is necessary to set the read lock to be valid

4. The main library performs data backup. There are many methods of backup. I will not introduce them here. You can refer to my previous article. After the backup is completed, the read lock can be released and the main library can perform write operations.

5. Start the slave database and restore the data just backed up. At this time, the data of the master and slave databases at the backup time point are consistent.

6. Configuration related to replication behavior on the slave database

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

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

Yes It can be seen that 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 the command on the slave to view:

Due to typesetting It’s too ugly, so I’ve sorted it out as follows:

Slave_IO_STATE:Waiting for master to send event
Master_host:127.0.0.1
Master_user:weidai
Master_port:3306
connnect_retry:60
Master_log_file:mysql-bin.000005
Read_Master_log_pos:1662
Relay_log_file:AE6Z*****-relay-bin.000002
Relay_log_pos:1415
Slave_IO_Running:yes
Slave_SQL_Running:yes

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

Slave_IO_Running:yes

Slave_SQL_Running:yes

These two threads are preceded by Mentioned, 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 slave's relay log (Relay_log). Generally, the probability of problems is small. Most problems are caused by permissions or network issues. Unable to connect to master. Just like the error mentioned earlier.

The Slave_SQL thread is responsible for executing the SQL in the relay log, and the probability of errors is relatively high. If someone manually inserts some records into the slave database, a primary key conflict will occur during master-slave synchronization.

Slave_IO_STATE:Waiting for master to send event

This status indicates that the relay log synchronization is completed and waiting for new events to be generated by the master.

The above is the detailed content of Detailed example of how MySQL implements master-slave replication process (picture). For more information, please follow other related articles on the PHP Chinese website!