What is Mysql read-write separation? How to set up the master-slave database?

This article brings you what is Mysql read-write separation? How to set up the master-slave database? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

What is read-write separation?

Mysql has nothing more than four functions: add, delete, modify, and read. Separate operations of adding, deleting, modifying, and reading. This will help improve system performance.
Try to think about it, moving in, returning, changing and selling goods has been changed to only selling goods at the front door, and others go to the back door. Isn’t this very clear?
Often things that look high-end are quite simple to make.

The following is written for beginners, they are all very intuitive operations.

1. Configuration:
Mysql with the same configuration on both servers, such as mysql5.7
PS: If conditions are limited, one can be used. You only need to install two mysql services (different ports such as 3306, 3307), but this is a bit contrary to the original intention of efficiency

2. Code:
1. Generally direct At the code layer, the reading and writing of mysql operation classes such as update, insert and select * from are routed and separated to correspond to different servers.
2. Middleware agent, that is, without changing the code, the middleware will Read and write requests are sent to the master-slave server.

Now let’s talk about how to configure the master-slave server:

1. Open port: Most hosts default to Mysql port 3306, so first configure the two machines Open port 3306 in the security group or firewall and restart.
2. The port is opened, now configure two servers (the same is true for one host and two Mysql servers):

The following is the main IP: 192.168.0.1, slave ip: 192.168.0.2, create synchronization account spec, password spec_password as an example:

Main server (addition, deletion and modification):

#1.Mysql配置改以下并重启服务(命令行里直接重启:service mysql restart)
[mysqld]
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index

#2.命令行mysql执行或phpmyadmin里执行:
GRANT REPLICATION SLAVE ON *.* TO 'spec'@'192.168.0.2' IDENTIFIED BY 'spec_password';
flush privileges;

In this way, the main server is completed.

Configuration from the server:

#1.Mysql配置改以下并重启服务(命令行里直接重启:service mysql restart)
[mysqld]
server-id=2
relay-log-index=slave-bin.index
relay-log=slave-bin

#2.命令行mysql执行或phpmyadmin里执行:
change master to master_host='192.168.0.1',master_port=3306,master_user='spec',master_password='spec_password',master_log_file='master-bin.000001',master_log_pos=0;
start slave

Generally speaking, the configuration is complete.

3. Test:

Query statement from the server: show slave status
If Slave_IO_State=Waiting for master to send event, it will be successful.
The rest of the status is usually due to the following reasons:

1. The master-slave security group or firewall does not open port 3306
2. Ping failure between master and slave.
3. The above spec account password error

From now on, any addition, deletion or modification on the master server will be synchronized to the slave server.

4. Suggestions:

1. Remember that the slave server only operates reads, not additions, deletions, or modifications. Maintain data synchronization, so that when one server fails, another one can take over (in fact, the master server Some of the latest data will definitely be lost when it goes down).
2. The bandwidth of the slave server must be >= the master server to minimize the synchronization delay. If it is a small water pipe, the synchronization will naturally be slower.

The above is the detailed content of What is Mysql read-write separation? How to set up the master-slave database?. For more information, please follow other related articles on the PHP Chinese website!