Detailed explanation of MySQL master-slave replication read-write separation construction

MySQL master-slave setup

MySQL master-slave replication and read-write separation settings are very simple:

Modify the configuration my.cnf file

The master and slave settings are similar:

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

log-bin=mysql-bin means: enable binary log.

server-id=222 means setting the unique ID of the server. The default value is 1, which is usually the last segment of the IP address. You can write it to something else as long as it is not repeated with other MySQL servers.

Here, some MySQL default my.cnf files reference all cnf files in the /etc/mysql/conf.d path. Therefore, you can also add a cnf file in the /etc/mysql/conf.d directory and add the above content

Restart mysql on the master and slave machines respectively

/etc/init.d/mysql restart

Create an account on the master and authorize the slave, and also make relevant configurations on the slave

Log in to the master and slave machines respectively as root user:

mysql -uroot -p //After entering, you will be asked to enter the root password. Entering the root password will log you in.

On the master machine

Create an account and authorize the slave machine. Pay attention to the username and password. You can set them at will, but be sure to write them down because the slave will need them later.

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

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

On the slave machine

Check the master status

show master status;

Enter in the mysql terminal

master_log_file is the corresponding content on the master machine, and so is master_log_pos. Pay attention to the content on the master status.

master_user and master_password are the username and password set during authorization on the master machine.

Start the slave server

start slave;

Check the status of the slave replication function

show slave status\G;

A list will appear

Mainly check that the status of Slave_IO_Running and Slave_SQL_Running must both be Yes.

The master creates the database and sets user access rights from the server

Create a database test on the master, and you will find that test is also created on the slave. Now you want to separate read and write. A user named gou on the slave machine can access it, but can only read.

grant select on test.* to gou;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.