Title: Improving performance, building MySQL read-write separation environment (2)
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:
Here we prepare two machines:
- Host: 192.168.248.128
- Slave: 192.168.248.139
There are three steps to configure the host, which are relatively easy:
1. Authorize to slave server
GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.248.139' identified by '123'; FLUSH PRIVILEGES;
This means configuring the slave login user name rep1, password 123, and must login from 192.168.248.139 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.
The revised document reads as follows:
[mysqld] log-bin=/var/lib/mysql/binlog server-id=128 binlog-do-db = cmdb
The following picture:
- 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;
At this point, the host configuration is complete.
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:
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='192.168.248.128',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
View slave status after boot:
show slave status\G;
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! ___________