MaxScale: A middleware tool for MySQL read-write separation and load balancing

1. What does MaxScale do?

After configuring the master-slave replication structure of MySQL, we hope to realize read-write separation, distribute read operations to slave servers, and achieve load balancing for multiple slave servers.

Separate read and write and load balancing is the basis of the needs of MySQL Cluster, MaxScale will be helping us to easily implement these features.

Insert picture description here

2. The basic structure of MaxScale

MaxScale was developed by MariaDB, the sister company of MySQL, and has now developed very mature. MaxScale is a plug-in structure, allowing users to develop their own plug-ins.

The plug-in functions currently provided by MaxScale are divided into 5 categories:

Authentication plugin
provides the login authentication function, MaxScale reads and cache information in the database user table, when a connection comes in, starting with cache information for verification, if not this user will be updated information from the back-end database, Verify again

Protocol plug
including protocol client connection protocols, and connection to the database

Plug-routing
to determine how to forward the client's request to the back-end database server, separate read and write and load balancing function is implemented by this module

Monitoring Plug-in
monitor for each database server, for example, find a database server response is slow, then no request to forward the

Log and filter plug-ins
provide a simple database firewall functions, can be filtered and fault-tolerant SQL

3. Installation and use of MaxScale

For example, there are 3 database servers in a structure of one master and two slaves.

3.1 Process overview

(1) Configure the cluster environment
(2) Download and install MaxScale
(3) Configure MaxScale, add each database information
(4) Start MaxScale, check whether the database is correctly connected
(5) Client connect to MaxScale, test

3.2 Detailed process

(1) Configure a cluster environment with one master and two slaves
Prepare 3 servers, install MySQL, and configure a replication structure with one master and two slaves.

(2) It is
best to install MaxScale on another server. If resources are insufficient, you can put it together with a MySQL.


MaxScale download link:

Choose the appropriate installation package according to your own server.

Taking centos 7 as an example, the installation steps are as follows:

wget https://downloads.mariadb.com/MaxScale/centos/7/x86_64/maxscale-2.3.7.centos.7.tar.gz
tar xzvf maxscale-2.3.7.centos.7.tar.gz
mv maxscale-2.3.7.centos.7 /usr/local/maxscale
ln -s /usr/local/maxscale/bin/max* /usr/bin/
#创建maxscale目录
mkdir -p /data/maxscale/data
mkdir -p /data/logs/maxscale

(3) arranged MaxScale
Before starting configuration, necessary masterto create two users is MaxScale, a monitoring module and a routing module.

Create monitoring user

create user [email protected]'192.168.%' identified by "111111";
grant replication slave, replication client on *.* to [email protected]'192.168.%';

Create routing user

create user [email protected]'192.168.%' identified by "scaleroute";
grant select on mysql.* to [email protected]'192.168.%';

After the user is created, start configuration

cat >>/usr/local/maxscale/etc/maxscale.cnf <<EOF
[maxscale]
threads=1
log_info=1
logdir=/data/logs/maxscale
datadir=/data/maxscale/data
[server1]
type=server
address=192.168.1.131
port=3306
protocol=MySQLBackend

[server2]
type=server
address=192.168.1.132
port=3306
protocol=MySQLBackend

[server3]
type=server
address=192.168.1.133
port=3306
protocol=MySQLBackend

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2,server3
user=maxscale_monitor
passwd=111111
monitor_interval=10000

[Read-Write Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=scaleroute
passwd=scaleroute
max_slave_connections=100%

[Read-Only Service]
type=service
router=readconnroute
servers=server1,server2,server3
user=scaleroute
passwd=scaleroute
router_options=slave


[MaxAdmin Service]
type=service
router=cli


[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default

EOF

Configuration is complete!

(4) Create a startup user and authorize

groupadd maxscale
useradd -s /sbin/nologin -M -g maxscale maxscale
chown -R maxscale. /data/logs/maxscale/ /data/maxscale/ /usr/local/maxscale/

(5) Start MaxScale to
execute the start command

maxscale --config=/usr/local/maxscale/etc/maxscale.cnf -U maxscale

Check if MaxScale's response port is ready

Insert picture description here

Such as startup error

maxscale: error while loading shared libraries: libgnutls.so.28: cannot open shared object file: No such file or directory

Solution

yum install gnutls  -y

(6) Log in to the maxscale manager and check the status information of the back-end database

maxadmin -S /var/run/maxscale/maxadmin.sock list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.1.131   |  3306 |           0 | Master, Running
server2            | 192.168.1.132   |  3306 |           0 | Slave, Running
server3            | 192.168.1.133   |  3306 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------

As you can see, MaxScale has been connected to the master and slave.

(7) Test
First create a test user on the master

mysql> grant ALL PRIVILEGES on *.* to [email protected]"192.168.%" Identified by "111111";

Use Mysql client to connect to MaxScale

mysql -h MaxScale所在的IP -P 4006 -u bertram -p111111
....
测试读写操作

It can be seen from the maxscale log that the write operation is in the master, and the read operation is in the slave.

Insert picture description here

Reference: Article