Tutorial on how to configure MySQL master-slave separation

We know that many websites now adopt master-slave separation and read-write separation. In this article, we mainly introduce the master-slave separation setting for MySQL database. I hope everyone will have a clearer understanding of this.

1. Introduction

Setting up read-write separation in MySQL database allows write operations and read operations on the database to be executed on different servers, improving concurrency and response speed. Today's websites are generally larger and adopt database master-slave separation and read-write separation, which not only serves as a backup but also reduces the pressure of reading and writing the database. I have always heard of these, but I have never practiced them myself. Today Have time to practice it and record the process.

2. Environment preparation

I have prepared two servers, one is a local PC and the other is a remote server. Mysql database is installed on the two machines respectively. I will not introduce the MySQL installation. , what needs to be noted here is: it is best to have the same version of MySQL installed. If it is inconsistent, there may be problems when the lower version reads from the higher version, so it is best to keep it consistent.

Main database master server: 172.10.10.69 centos 7 Linux system, mysql version 5.6.35

Slave local machine: 172.10.10.240 Win7 system, mysql version 5.6.35

3. Main library configuration

Create a user root in the main library to read the execution log of the main library from the library.
Need to be executed in the mysql command line, you need to log in to the command line first

1. GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.10.10.240' IDENTIFIED BY '123456'; 2. flush privileges;
Modify the my.cnf file:
Add

server-id=13log-bin=master-binlog-bin-index=master-bin.index under [mysqld]

Restart MySQL

Enter the mysql command line, enter show master status; view information

Remember File and Position, because they are used when configuring the slave service.

4. Configure the slave server

Modify my.cnf

server-id=2relay-log=slave-relay-binrelay-log-index=slave-relay-bin .index

Restart the database.

Connect to the master database and enter the command on the command line:

change master to master_host='192.10.10.69', #Master server Ipmaster_port=3306,master_user='root',master_password= '123456', master_log_file='master-bin.000255', #Master log file name master_log_pos=1575; #Master log synchronization starting position

Pay attention to whether the execution is successful. If the execution fails, check the code carefully. Look where I wrote it wrong.
If the execution is normal, start the slave slave and check the connection status.

//You need to execute start slave on the mysql command line; show slave status; //View slave connection status

Status information:

Slave_IO_State: Waiting for master to send event Master_Host: 45.78.57.4 Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 672913 Relay_Log_File: slave-relay-bin.000044 Relay_Log_Pos: 504 Relay_Master_Log_File: master-bin.0000 01 Slave_IO_Running: Yes //Must be YES Slave_SQL_Running: Yes //Must be YES

If Slave_IO_Running and Salve_SQL_Running are not YES at the same time, the configuration is incorrect, check the above steps.

5. Test

Add a piece of data in the master database and check whether there is data in the slave database.

The above is the basic configuration tutorial of MySQL master-slave separation. I hope it can help everyone.

Related recommendations:

Detailed introduction about master-slave separation

Detailed explanation of the master-slave separation example code of Mysql database

Detailed explanation of MySQL's master-slave replication, read-write separation, backup and recovery

The above is the detailed content of Tutorial on how to configure MySQL master-slave separation. For more information, please follow other related articles on the PHP Chinese website!