MySQL read-write separation

MySQL master-slave replication + proxy: Client Access Proxy Server, read, write request to proxy recognition, thereby reducing the complexity of the client program.

"192.168.4.10, 192.168.4.11 as MySQL master, slave server, is the backend of the entire service

Another 192.168.4.15 as a MySQL proxy server "

First, deploy Mysql-proxy proxy Server

[Email protected] ~]# RPM-IVH maxscale-2.1.2-1.rhel.7.x86_64.rpm

1. Modify the configuration file

[[Email protected] ~] #vim/etc/maxscale

[Server1]

Type=server

address=192.168.4.11 Master

port=3306

Protocol=mysqlbackend

23

[Server2]

Type=server

From address=192.168.4.12

port=3306

Protocol=mysqlbackend

Servers=server1,server2 Monitor two servers

User=scalemon Define monitoring users

passwd=123456 Define Password

monitor_interval=10000 mm

#[read-only Service] (53-59) Comment

=service #type

#router =readconnroute

#servers =server1

=myuser #user

=mypwd #passwd

#router_options =slave

[Read-write Service]

Type=service

Router=readwritesplit

Servers=server1,server2 Add Users

User=maxscale Add permission to read access to MySQL Library

passwd=123456

max_slave_connections=100%

[Maxadmin Service] defines the management services of the software

Type=service

Router=cli

#[read-only Listener] (86-90) Notes

#type =listener

#service =read-only Service

#protocol =mysqlclient

#port =4008

94 Services defined by Service=read-write service

port=4006 defined read-write port number

98 [maxadmin Listener] -defined monitoring

Type=listener

Service=maxadmin Service

101 protocol=maxscaled

102 Socket=default

103 port=4009 -defined management ports

Second, configure the read -write server (only the primary server licensing can be automatically synchronized from the server)

Add an Authorized user

Mysql> Grant Replication Slave,replication Client

On * *

→

-e [email protected] "%"

-Identified by "111111";

Create a routed user

Mysql> Grant Select on mysql.*

->to

->[email protected] '% '

->identified by "111111";

Create a Client Access user

Mysql> Grant All on * * to [e-mail protected] '% ' identified by "111111"

Third, the proxy server access to the native management service

Test

[Email protected] ~]# mysql-h192.168.4.11-uscalemon-p111111

[Email protected] ~]# mysql-h192.168.4.12-uscalemon-p111111

Start the service

[Email protected] box ~]# maxscale-f/etc/maxscale.cnf

[Email protected] ~]# maxadmin-uadmin-pmariadb-p4009

maxscale> list servers (monitor server status)

Servers.

-------------------+-----------------+-------+-------------+--------------------

Server | Address | Port | Connections | Status

-------------------+-----------------+-------+-------------+--------------------

Server1 |  192.168.4.11 |           3306 | 0 | Master, Running

Server2 |  192.168.4.12 |           3306 | 0 | Slave, Running

-------------------+-----------------+-------+-------------+--------------------

Client Access

mysql-h192.168.4.15-p4006-uyaya-p111111

MySQL [(None)]> SELECT @ @hostname;

+------------+

| @ @hostname | Read

+------------+

| mysql-12 |

+------------+

Insert a new record

MySQL [(None)]> insert into BBSDB.A values (111)

The client is currently accessing the record from the database server and is still able to insert it. It means success.

MySQL read-write separation