Read and write separation of MySQL database

First, overview:

MySQL database master-slave configuration, the normal situation of all the database read and write operations on the main database, from the database only as a data backup use, obviously can not effectively use the server resources, so the need to achieve read and write separation is unavoidable.

Second, the topology diagram illustrates:

As shown, this article is to achieve the read MySQL database write operation (increase and deletion) on the master server (192.168.4.10) implementation, while the MySQL database read operation (query) on the slave server (192.168.4.20) completed.

If you create two database connection Connection during programmer programming, use one database Connectionfor the read operation of the database, and write to the database using a different database Connection. You can also implement a read-write separation of the database. However, it is obvious that this approach is not flexible enough and is not generally used in this way.

This article will use the Maxscale software to realize the read and write separation of MySQL database.Maxscale is made up ofhttp://www.skysql.com/the development of a MySQL database middleware, supporting high availability, load balancing, with good scalability, high-performance event-driven based on the agent and management functions of the middleware. The software is deployed on the MySQL proxy server (192.168.4.100) in the topology map, and the client's access requests point to the MySQL proxy server. The server will point the write operation to the database to 192.168.4.10, the read operation to the database points to 192.168.4.20.

Third, configuration instructions:

1. Maxscale Configuration

(1) Modify the configuration file:

[Email protected] ~]# VIM/ETC/MAXSCALE.CNF

A. Specify the database server
[Server1]
Type=server
address=192.168.4.10
port=3306
Protocol=mysqlbackend

[Server2]
Type=server
address=192.168.4.20
port=3306
Protocol=mysqlbackend

monitor_interval=10000//Active check interval, 10000 milliseconds = 10 seconds
C, specify which hosts the read-write separation performs between
[Read-write Service]
Type=service
Router=readwritesplit
Servers=server1,server2
User=maxscale #用于检查用户连接数据库时用的授权用户是否合法的用户名 (requires SELECT permission on MySQL database)
passwd=123456 #用于检查用户连接数据库时用的授权用户是否合法的用户名

max_slave_connections=100%
D. Define management Services and modalities (no change)
[Maxadmin Service]
Type=service
Router=cli
E, read-Only server configuration options (no, all commented out)
[Read-only Service]
#type =service
#router =readconnroute
#servers =server1
#user =myuser
#passwd =mypwd
#router_options =slave

#[read-only Listener]
#type =listener
#service =read-only Service
#protocol =mysqlclient
#port =4008
F, read-write separate listening port configuration
[Read-write Listener]
Type=listener
Service=read-write Service
Protocol=mysqlclient
PORT=4006//client read/write detach service access port
G, monitoring port configuration for Management Services
[Maxadmin Listener]
Type=listener
Service=maxadmin Service
Protocol=maxscaled
Socket=default
port=4009//Ports used by the management service

2. Add authorization to the primary database server

MySQL > Grant replication slave,replication Client on * * to [e-mail protected] "%" identified by "123456";
MySQL > Grant Select on mysql.* to [email protected] "%" identified by "123456";

3. Start the service on the proxy server and check the database server status

(1) Start-up service:

[Email protected] ~]# Maxscale--config=/etc/maxscale.cnf

(2) Check Status

[Email protected] ~]# maxadmin-uadmin-pmariadb-p4009
maxscale> list Servers
-------------------+-----------------+-------+-------------+--------------------
Server | Address | Port | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
Server1 |  192.168.4.11 |           3306 | 0 | Master, Running
Server2 |  192.168.4.12 |           3306 | 0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

4. Verification Test

using an Authorized user connection Proxy (192.168.4.100:4006) on 192.168.4.120, insert some data from the database server (192.168.4.20), which can be read by the client, when the client reads the data from the database (19 2.168.4.20)    On 192.168.4.120, using an authorized user to connect to the proxy server (192.168.4.100:4006), insert some data successfully, can be seen on the master-slave database, indicating that the client writes data on the primary database server (192.168.4.10).     

Read and write separation of MySQL database