MySQL database master-slave separation configuration method, mysql database master-slave Separation

MySQL database master-slave separation configuration method, mysql database master-slave Separation
1. Introduction

Setting read/write splitting for the MySQL database enables write and read operations on the database to be executed on different servers, improving the concurrency and response speed. Currently, most websites use master-slave database separation and read/write separation to backup databases and reduce the read/write pressure on databases, but I have never practiced it myself. Today I have time to practice it and record the process.

2. Prepare the environment

I have prepared two servers: one is the local PC and the other is the remote server. I will not introduce the installation of Mysql database on the two servers. Here, we need to note that: it is best to ensure that the versions installed in MySQL are consistent. If the versions are different, there may be problems when reading the lower version to the higher version.

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

Slave local: 172.10.10.240 Win7 system, mysql version 5.6.35

3. Master Database Configuration

　　

Create a user root in the master database to read the execution logs of the master database.
To run the command in the mysql command line, log on 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:
Under mysqld, add

server-id=13log-bin=master-binlog-bin-index=master-bin.index

Restart MySQL

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

Remember File and Position, because it must be used when configuring slave services.

4. Configure the slave server

Modify my. cnf

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

Restart the database.

To connect to the primary database, enter the following command in the command line:

Change master to master_host = '2017. 10.10.69 ', # Master server Ipmaster_port = 3306, master_user = 'root', master_password = '000000', master_log_file = 'master-bin.000255', # master log file name master_log_pos = 123456; # Start position of Master log Synchronization

Check whether the execution is successful. If the execution fails, check the code to see where the error is written.
If the slave database runs normally, start the slave database slave and check the connection status.

// Run start slave and show slave status on the mysql command line. // view the slave connection status.

Status information:

Slave_IO_State: Waiting for master to send event Master_Host: Maid Master_User: test Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 connector: 672913 Relay_Log_File: slave-relay-bin.000044 Relay_Log_Pos: 504 connector: master-bin.000001 connector: yes // YES Slave_ SQL _Running: Yes // YES

If Slave_IO_Running and Salve_ SQL _Running are not the same as YES, the configuration is incorrect. Check the preceding steps.

 

5. Test

Add a piece of data to the primary database and check whether there is any data from the database.