MySql master-slave replication and problems encountered

Introduction

MySQL master-slave replication is an asynchronous replication process, and the underlying layer is based on the built-in binary log function of the Mysql database. It means that one or more MySQL databases (slave, that is, from the library) copy the log from another MySQL database (master, that is, the main library), and then parse the log and apply it to itself, and finally realize the data from the database and the master library. The data remains consistent. MySQL master-slave replication is a built-in function of the MySQL database without the need for third-party tools.

The MySQL replication process is divided into three steps:

  1. The master records changes to the binary log (binary log)

  1. The slave copies the master’s binary log to its relay log (relay log)

  1. The slave redoes events in the relay log and applies the changes to its own database

Operation

Precondition

Prepare two servers with mysql installed and started in advance (I created one using a virtual machine here, and prepared the other by cloning. Note that there are pits here!)

Main library: Master

From the library: Slave

Configure the main library

  1. Step 1: Modify the configuration file

I use the rpm installation method to install mysql. There is a configuration file my.cnf under etc. Modify the configuration file

vim /etc/my.cnf

#Enter the following command

log-bin=mysql-bin #[must] enable binary log
server-id=100 #[Must] The unique ID of the server must be different from each subsequent slave library, not fixed

Exit: wq save

  1. Step 2: Restart the mysql service

systemctl restart mysqld

  1. Step 3: Log in to the mysql database and execute the following sql

mysql -uroot -p + password #login mysql
GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by '123456' #sql statement

The function of the above sql statement is to create a user xiaoming with a password of 123456, and grant REPLICATION SLAVE permission to user xiaoming. It is often used to establish user permissions that need to be used when replicating, that is, the slave must be authorized by the master to be a user with this permission to replicate through this user.

  1. Step 4: Log in to the database, execute the following sql, and record the values of File and Position

show master status;

So far, Msater configuration is complete, do not perform any operations

Configure slave library

  1. Step 1: Modify the configuration file

The first step is the same as configuring the master library, but only need to configure a unique id, and ensure that the id is inconsistent with the master

  1. Step 2: Restart the mysql service (same as the second step of configuring the main library)

  1. Step 3: Log in to the mysql database and execute the following sql

changemasterto master_host='xxx.xxx.xxx.xxx', master_user='xiaoming', master_password='123456', master_log_file='mysql-bin.000004', master_log_pos=356;

master_host: the ip of the master

master_user: configure the user name registered in the third step of the host

master_password: Configure the password of the user registered in the third step of the host

master_log_file, master_log_pos: Configure the value of File and Position recorded in the fourth step of the host

Then start the slave

start slave

  1. Step 4: Log in to the mysql database, execute the following sql, and check the status of the slave database

show slave status\G

Points to pay attention to

If Slave_IO_State shows Waiting for master to send event, Slave_IO_Running and Slave_SQL_State are both Yes, it means success. You can check whether replication will be implemented in slave by creating a database table in master.

The above is fully configured by me, and then I will talk about the problems I encountered

Problems encountered in configuration

After I configured it step by step, I found that Slave_IO_Running is in the No state when I checked the status of the slave library. After checking the information, I found that it was the pit I buried for myself. Because my slave is copied from the master, the ip and uuid of the master and slave are the same. I modified the ip but not the uuid, which caused this problem.

Slave_IO_Running:No

modify uuid

You can refer to this blog post, remember to restart the slave after modification, and check the status again to find that it is ok

https://blog.csdn.net/syq1791899128/article/details/123704576

Just when I thought it was ok, I found that Slave_SQL_State became No…

Slave_SQL_Running:No

After consulting the following information, it may be caused by the following problems:

  1. The program may have performed a write operation on the slave

  1. It may be caused by the transaction rollback after the slave machine is restarted.

I judged that the slave may have just been restarted, which caused the transaction to roll back, so I did the following:

stop slave; #stop slave
setGLOBAL SQL_SLAVE_SKIP_COUNTER=1; #In the maintenance of the master-slave database, it is necessary to skip a command that cannot be executed
starts slave; #Start slave

Afterwards, the status query can be performed to achieve master-slave replication.

During the learning process, I found that there may be an error, and record it together:

Other errors

‘Could not find first log file name in binary log index file’

Cause: In the third step of configuring the slave library, the values of master_log_file and master_log_pos are inconsistent with the values of File and Position recorded in the fourth step of the master, which may be caused by carelessness or some operation.

Solution:

  • First go to the master to check the values of master_log_file and master_log_pos

  • shut down slave

  • Enter the following sql

CHANGEMASTERTO MASTER_LOG_FILE='mysqld-bin.000003',MASTER_LOG_POS=154;
#The master_log_file and master_log_pos here must be consistent with the master

  • start slave

Done successfully!

Excerpted from the ppt of Mr. Heima’s class, it is only used as his own study notes, if there is any mistake, please point it out