Detailed description of MySQL master-slave replication read/write splitting, detailed description of mysql master-slave read/write

Detailed description of MySQL master-slave replication read/write splitting, detailed description of mysql master-slave read/write

MySQL Master/Slave settings

MySQL master-slave replication, the read/write splitting settings are very simple:

Modify the my. cnf File

The master and slave settings are similar:

[mysqld]log-bin=mysql-bin server-id=222

log-bin=mysql-binEnable binary log.

server-id=222It indicates that the unique ID of the server is set. The default value is 1. Generally, the last segment of the IP address can be written as another, as long as it is not repeated with other mysql servers.

Here, some MySQL Defaultmy.cnfFile referenced/etc/mysql/conf.dAll cnf files in/etc/mysql/conf.dAdd a cnf file under the Directory and add the above content

Restart the mysql of the master and slave machines respectively.

/Etc/init. d/mysql restart

Create an account on the master and authorize the slave, and configure the slave.

Use the root user to log on to the master and slave machines respectively:

Mysql-uroot-p // enter the root password.

On the master machine

Create an account and authorize the Server Load balancer instance. Pay attention to the user name and password. You can set the password at will, but remember it, because slave will be used later.

GRANT REPLICATION SLAVE ON *.* to 'ryugou'@'%' identified by 'ryugou';

Generally, the root account is not used. @ '%' indicates that all clients may be connected. If the account and password are correct, the specific client IP address can be used here, such as 192.168.145.226, to enhance security.

On the slave machine

View master Status

show master status;

Enter

Copy codeThe Code is as follows:
Change master to master_host = 'xxx. xxx. xxx. XXX', master_user = 'ryugou', master_password = 'ryugou', master_log_file = 'mysql-bin.000004 ', master_log_pos = 120;

Master_log_file is the corresponding content on the master machine, and master_log_pos is the same. Check the content in the master state.

Master_user and master_password are the usernames and passwords set during authorization on the master machine.

Start slave server

start slave;

Check slave Server replication status

show slave status\G;

A list is displayed.

The status of Slave_IO_Running and Slave_ SQL _Running must be Yes.

Create a database on the master node and set user access permissions on the slave server.

When you create a database test on the master, you will find that the test is also created on the slave. Now, if you want to read/write splitting, a user gou on the slave can access it, but can only read

grant select on test.* to gou;

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.