Detailed explanation of MySQL master-slave replication process

1. What is master-slave replication?

The DDL and DML operations in the master database are transferred to the slave database through the binary log (BINLOG), and then these logs are re-executed (re-done); thus, the data in the slave database is kept consistent with the master database.

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. It can perform read-write separation at the database level.

3. Daily backup 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

Step 1: Before each transaction completes updating data, the master writes the operation record serially into the binlog file.

Step 2: The salve starts an I/O Thread, which opens a normal connection on the master. Its main task is the binlog dump process. If the reading progress has caught up with the master, it goes into sleep mode 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.

Step 3: SQL Thread reads the relay log and executes the SQL events in the log sequentially to keep consistent with the data in the main database.

4. Specific operations of master-slave replication

I installed two instances of msyql in different paths on the same windows. It is recommended that the installation versions of the master and slave MySQL be consistent, although mine are 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, which should not be repeated in different MySQL instances. binlog-do-db specifies the database to be copied. log-bin is used to open the binary log file.

salve

Since the master and slave databases will be running on the same computer, the ports need to be set differently, here is 3307

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

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

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

When I operated for the first time, I completed the creation of this account here, but when I actually copied it, I found that the copy was not successful. When I checked the error, I found that the binlong generated by the master was fine, and then I checked the status of the slave:

There is an error line at the end:

It is not possible to connect to the master using the weidai account, so the master's binlog is not obtained, resulting in the inability to generate relay logs.

I checked the account and password repeatedly but found no problem. Then I looked for relevant information and found that it was because I missed a step when creating a new user in the master:

After setting up a new user or changing the password, you need to use flush privileges to refresh the MySQL system privilege related tables, 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 current data position in the master database. This is mainly used to copy the starting position of the data after the slave data is started. However, before obtaining this status value, the master database cannot modify the data, so you need to set the read lock to be valid first.

4. The main database performs data backup. There are many ways to back up data. 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 database can perform write operations.

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

6. Related 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 new data in the master, and observe in the slave:

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 to view it on the slave:

Because the layout is too ugly, I 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

connect_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

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 caused by permission or network problems, which lead to the inability to connect to the master. Just like the error mentioned above.

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

Slave_IO_STATE: Waiting for master to send event

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

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.