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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.