Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to build a read-write separation environment in MySQL. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Host configuration

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

1. Authorize to slave server

GRANT REPLICATION SLAVE ON *. * to 'rep1'@'192.168.248.139' identified by' 123 investors flush PRIVILEGES

This means that the configuration slave login user name is rep1 and the password is 123, and you must log in from the address 192.168.248.139. After successful login, you can manipulate any table in any library. Among them, if you do not need to restrict the login address, you can change the IP address to one%.

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

Vi / etc/my.cnf

The contents of the modified file are as follows:

[mysqld] log-bin=/var/lib/mysql/binlogserver-id=128binlog-do-db = cmdb

As shown below:

Log-bin: the log path and file name of synchronization. Be sure to note that if MySQL has permission to write to this directory (I am lazy here, I put it directly 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. Server-id: the unique symbol of MySQL in the master-slave environment. Give an arbitrary number and be careful not to repeat it with the slave machine.

Restart the MySQL server after configuration:

Systemctl restart mysqld

3. Check the current binary log name and offset of the master server. The purpose of this operation is to recover the data from this point after starting from the slave database:

Show master status

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 in / etc/my.cnf:

Note that the slave only needs to be configured with server-id.

Note: if the slave is copied from the host, that is, we obtain the MySQL instance by copying the CentOS virtual machine, and the uuid of the two MySQL is the same (the normal installation will not be the same). You need to modify it manually. The location is / var/lib/mysql/auto.cnf. You can modify a few characters here at will, but not too casually, such as modifying the length of the uuid.

two。 Use the command to configure the slave:

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

The host address, port, and username and password of the slave login host are configured here. Note that the last two parameters should be consistent with those in master.

3. Start the slave process

Start slave

Check the slave status after startup:

Show slave status\ G

4. View the status of slave

If both of the following values are YES, the configuration is correct:

Slave_IO_Running: YesSlave_SQL_Running: Yes on how to build a read-write separation environment in MySQL is shared here. I hope the above content can be helpful to you and learn more. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.