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

I. Overview:

After the configuration of the master-slave structure of the MySQL database, under normal circumstances, all the read and write operations of the database are on the master database, and the slave database is only used as a data backup, obviously can not effectively use the server resources, then the need to achieve read-write separation is inevitable.

2. Description of the topology diagram:

As shown in the figure above, the purpose of this paper is to implement the write operation (addition, deletion and modification) of reading MySQL database on the Master server (192.168.4.10), while the read operation (query) of MySQL database is completed on the Slave server (192.168.4.20).

If you create two database connection Connection when the programmer is programming, one database Connection is used for the read operation of the database in the program, and another database Connection is used for the write operation of the database, the read and write separation of the database can also be realized. However, it is obvious that this implementation is not flexible enough, so it is generally not used.

In this paper, we will use MaxScale software to realize the read-write separation of MySQL database. MaxScale is a MySQL database middleware developed by http://www.skysql.com/, which supports high availability, load balancing, good scalability, high performance event-driven middleware with both agent and management functions. The software will be deployed on the MySQL proxy server (192.168.4.100) in the topology diagram. The client's access request will point to the MySQL proxy server, which will point the write operation to the database to 192.168.4.10 and the read operation to the database to 192.168.4.20.

3. Configuration instructions:

1. MaxScale configuration

(1) modify the configuration file:

[root@100 ~] # 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

B. Specify the database server to monitor

[MySQL Monitor]

Type=monitor

Module=mysqlmon

Servers=server1,server2

User=scalemon # proxy server monitoring service user name to connect to the database server (authorization is required on the database server)

Passwd=123456 # password of the proxy server monitoring service connecting to the database server

Monitor_interval=10000 / / active check interval, 10000 milliseconds = 10 seconds

C. Specify which hosts to perform read-write separation

[Read-Write Service]

Type=service

Router=readwritesplit

Servers=server1,server2

User=maxscale # user name used to check whether the authorized user is valid when connecting to the database (requires select permission to the mysql database)

Passwd=123456 # user name used to check whether the authorized user is legitimate when connecting to the database

Max_slave_connections=100%

D, define management services and methods (no need to change)

[MaxAdmin Service]

Type=service

Router=cli

E, read-only server configuration options (no, comment out all)

[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, listening port configuration for read-write separation

[Read-Write Listener]

Type=listener

Service=Read-Write Service

Protocol=MySQLClient

Port=4006 / / client read-write separation service access port

G. Monitor port configuration of management service

[MaxAdmin Listener]

Type=listener

Service=MaxAdmin Service

Protocol=maxscaled

Socket=default

Port=4009 / / Port used by the management service

2. Add authorization to the primary database server

Mysql > grant replication slave,replication client on *. * to scalemon@ "%" identified by "123456"

Mysql > grant select on mysql.* to maxscale@ "" identified by "123456"

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

(1) start the service:

[root@100] # maxscale-- config=/etc/maxscale.cnf

(2) check status

[root@100] # 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

Use an authorized user to connect to the proxy server (192.168.4.100) on 192.168.4.120, and Insert some data from the database server (192.168.4.20), which can be read by the client, which means that the client is reading data from the database (192.168.4.20). Using the authorized user to connect to the proxy server (192.168.4.100 Insert 4006) on 192.168.4.120, some of the data in the Insert is successful, which can be seen on the master and slave database, indicating that the client is writing data on the master database server (192.168.4.10).

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.