Improving Performance, Building MySQL Read-Write Separation Environment (2)


Title: Improving performance, building MySQL read-write separation environment (2)
tags: MySQL
categories: MySQL
abbrlink: 60ae3a4d

date: 2019-05-13 10:12:36

Last article talked about how to install MySQL 5.7 in CentOS 7, which is usually installed in the virtual machine, packed and duplicated, so that we can have two MySQLs and start today’s master-slave construction.


I have a simple picture here to show you how MySQL master and slave work:

Improving Performance, Building MySQL Read-Write Separation Environment (2)

Here we prepare two machines:

  • Host:
  • Slave:

Host configuration

There are three steps to configure the host, which are relatively easy:

1. Authorize to slave server

GRANT REPLICATION SLAVE ON *.* to 'rep1'@'' identified by '123';

This means configuring the slave login user name rep1, password 123, and must login from address. After successful login, you can operate any table in any library. If you don’t need to restrict the login address, you can change the IP address to one.%

2. Modify the main library configuration file, open the binlog, and set the server-id. Every time you modify the configuration file, you need to restart the MySQL service before it takes effect.

vi /etc/my.cnf

The revised document reads as follows:

binlog-do-db = cmdb

The following picture:

Improving Performance, Building MySQL Read-Write Separation Environment (2)

  • Log-bin: Synchronized log paths and file names, be sure to note that this directory is authorized to be written by MySQL (I’m lazy here, just under the dataDir below).
  • Binlog-do-db: The name of the database to be synchronized. When the slave is connected to the host, only the database configured here will be synchronized, and the rest will not be synchronized.
  • Ser-id: MySQL’s unique identifier in the master-slave environment, give an arbitrary number, pay attention not to duplicate with the slave.

Restart MySQL Server after Configuration:

systemctl restart mysqld

3. View the current binary log name and offset of the primary server. The purpose of this operation is to restore data from this point after starting from the database:

show master status;

Improving Performance, Building MySQL Read-Write Separation Environment (2)

At this point, the host configuration is complete.

Slave configuration

The configuration of the machine is also relatively simple. Let’s look at it step by step:

1. Add the following configuration to / etc / my. cnf:

Improving Performance, Building MySQL Read-Write Separation Environment (2)

Note that you only need to configure server-id from the slave.

Note: If the slave machine is replicated from the host, that is, we get MySQL instances by replicating the CentOS virtual machine, then the UUID of the two MySQLs is the same (normal installation will not be the same), then it needs to be manually modified, and the location of the modification is in the/var/lib/mysql/auto.cnfNote that you can change a few characters here at will, but you can’t be too casual, such as changing the length of uuid.

2. Use commands to configure slaves:

change master to master_host='',master_port=3306,master_user='rep1',master_password='123',master_log_file='binlog.000001',master_log_pos=120;

Here, the host address, port and user name and password of the slave host are configured. Note that the last two parameters should be consistent with those in the master.

3. Start the slave process

start slave;

View slave status after boot:

show slave status\G;

Improving Performance, Building MySQL Read-Write Separation Environment (2)

4. View the status of slave

Mainly the following two values are YES, which means that the configuration is correct:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

At this point, the configuration is completed, the host creates the library, adds data, and synchronizes automatically from the opportunity.

If one of the two is not YES, it means that the master-slave environment failed to build, at this time you can read the log, see the cause of the error, and then solve the specific problem.


This article mainly talked to everyone about MySQL’s Master-Slave environment. These steps have been repeated over and over again.

Pay attention to the public number boy, focus on Spring Boot + micro services, share regular video tutorials, pay attention to Java, get the Java dry goods Matsumoto carefully prepared for you! ___________

Improving Performance, Building MySQL Read-Write Separation Environment (2)