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

1. What is Maxscale?

After configuring MySQL 's master-slave replication structure, we want to implement read-write separation, spread the reads to the slave server, and load balance multiple slave servers.

read-write separation and load balancing are the basic requirements of MySQL cluster, and Maxscale can help us to implement these functions conveniently.

2, the basic composition of Maxscale

Maxscale was developed by MySQL's brother company MariaDB, and is now very mature. Maxscale is a plug-in structure that allows users to develop plug-ins that fit their own.

Maxscale currently offers plug-in features in 5 categories:

• Authentication Plugin

  Provides login authentication function, Maxscale will read and cache the information in the user table in the database, when there is a connection, first verify from the cache information, if there is no such user, will update the information from the back-end database, verify again

• Protocol Plugin

  Includes the Client connection protocol, and the Protocol that connects the database

• Routing Plug-ins

  Decide how to forward the client's request to the backend database server, the function of read-write separation and load balancing is implemented by this module.

• Monitoring plugins

  Monitoring of individual database servers, such as a slow response to a database server, does not forward the request to it

• Log and Filter plugins

  Provides simple database firewall capabilities to filter and fault-tolerant SQL

3 . Installation and use of Maxscale

For example there are 3 database servers, which are a primary two-slave structure.

Process overview

(1) Configure the cluster environment.

(2) Download and install Maxscale

(3) Configure Maxscale, add each database information

(4) Start Maxscale to see if the database is connected correctly

(5) Client connection Maxscale, test

Detailed procedures

(1) Configuring a primary two-slave cluster environment

Prepare 3 servers, install MySQL, configure a primary two-slave replication structure.

(2) Installation Maxscale

It is best to install on another server, and if you have insufficient resources, you can put it with some MySQL.

Maxscale: Https://downloads.mariadb.com/files/MaxScale

Select the appropriate installation package based on your own server.

For the CentOS 7 example, the installation steps are as follows:

Yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 LIBEDIT-YRPM-IVH maxscale-1.4.3-1.centos.7.x86_ 64.rpm

(3) Configuration Maxscale

Before you start the configuration, you need to create two users for Maxscale in master for monitoring modules and routing modules.

Create a monitoring user

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

Create a routed user

mysql> create user [email protected] '% ' identified by ' 111111 ';mysql> grant Select on mysql.* to [email protected] '% ’;

After user creation is complete, configure

Vi/etc/maxscale.cnf

Locate the [Server1] section, modify the address and port in it, and point to the IP and ports of master.

Copy the entire chunk of [server1] 2 times, and change the address and port to [Server2] and [Server3], respectively, to Slave1 and slave2:

Locate the [MySQL Monitor] section, modify the servers to Server1,server2,server3, and modify the user and passwd information (scalemon,111111) for the previously created monitoring users.

Locate the [read-write Service] section, modify the servers to Server1,server2,server3, and modify the user and passwd information (maxscale,111111) for the previously created routing users.

Since we are using [read-write service], we need to delete another service [Read-only Services] and delete its entire contents.

The configuration is complete, save and exit the editor.

(4) Start Maxscale

Execute Start command

Maxscale–config=/etc/maxscale.cnf

See if the Maxscale response port is ready

Netstat-ntelp

• 4006 is the port used when connecting the Maxscale
• 6603 is the port of the Maxscale manager

Log in to Maxscale Manager to see the database connection status, the default user name and password is admin/mariadb.

Maxadmin–user=admin–password=mariadb

maxscale> list Servers

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

(5) test

Create a test user on master first

Mysql> Grant all privileges on * * to [e-mail protected] "%" identified by "111111";

Using the Mysql client to connect Maxscale

Mysql-h Maxscale ip-p 4006-u rtest-p111111

Perform an operation to view the database server name to know which database is currently in place:

When the transaction is turned on, it is automatically routed to master, and the normal query operation is on slave

The configuration of the Maxscale is complete.

4, Maxscale in slave after the failure of the treatment

The Maxscale can realize the read and write separation and load balancing of MySQL, so what will Maxscale do when slave fails?

For example, there are 3 database servers, one primary two from the structure, the database names are master, slave1, Slave2, respectively.

Now we are experimenting with the following two scenarios:

(1) when one slave server (slave1 or slave2) fails, review how the Maxscale is responding, and when the failed server is back online

(2) when two slave servers (slave1 and slave2) fail, see how the Maxscale is responding, and when the failed server comes back online

Get ready

In order to view the status of Maxscale in more depth, you need to open the Maxscale log:

Modifying a configuration file

Vi/etc/maxscale.cnf

Find the [Maxscale] section, where you can use global settings to add logs.

Configuration

log_info=1logdir=/tmp/

By turning on the log_info level, you can see the routing log for Maxscale.

After modifying the configuration, restart Maxscale.

Experimental process

1. case of single slave failure

The initial state is everything is OK.

Stop slave2 replication and log in to slave2 MySQL execution.

mysql> stop Slave;

View Maxscale Server Status

The slave2 has lapsed.

Viewing log information

Cat/tmp/maxscale1.log

Tail Display:

2016-08-15 12:26:02 Notice:server changed state:slave2[172.17.0.4:3306]: Lost_slave

Tip Slave2 has been lost.

To view client query results:

The query operation has all been transferred to SLAVE1.

As you can see, after a slave failure, Maxscale automatically excludes the request from being forwarded to it.

below to see the situation after slave2 again on the line.

Log in to Slave2 MySQL execution

mysql> start slave;

View Maxscale Server Status

The slave2 has lapsed.

Viewing log information

Cat/tmp/maxscale1.log

Tail Display:

2016-08-15 12:26:02 Notice:server changed state:slave2[172.17.0.4:3306]: Lost_slave

Tip Slave2 has been lost.

To view client query results:

The query operation has all been transferred to SLAVE1.

As you can see, after a slave failure, Maxscale automatically excludes the request from being forwarded to it.

below to see the situation after slave2 again on the line.

Log in to Slave2 MySQL execution

mysql> start slave;

View Maxscale Server Status

resumed its normal state and re-identified the slave2.

To view the log information, show:

2016-08-15 12:32:36 Notice:server changed state:slave2[172.17.0.4:3306]: New_slave

To view client query results:

Slave2 can also accept query requests normally.

Through the experiment can be seen, in the partial slave failure, Maxscale can be automatically identified, and remove the routing list, when the recovery is back online, Maxscale can also be automatically added to the route, the process is transparent.

2. all slave failure conditions

Log in to slave1 and slave2 , respectively, and execute the command to stop copying.

mysql> stop Slave;

View Maxscale Server Status

found that the roles of each server are not recognized.

To view logs:

From the log, Maxscale found that 2 slave and master were lost, and then error: no master.

The client also failed when connecting to Maxscale.

Indicates that the entire database service is invalidated after all failures from the server cause master to be unrecognized.

Can I make master available for all slave failures? This will at least provide the database service as expected.

This requires modifying the configuration of the Maxscale, telling Maxscale that we need a stable master.

Processing process

Restore two slave first, let the cluster return to normal state, landing two slave mysql.

mysql> start slave;

Modify the Maxscale configuration file to add a new configuration.

Vi/etc/maxscale.cnf

Find the [MySQL Monitor] section and add:

Detect_stale_master=true

Save the exit, and then restart Maxscale.

Verify

Stop two slave and view the Maxscale server status.

As you can see, although the slave is not recognized, Master is still in, and the hint is in a stable state

Client execution Request:

The client can connect to the Maxscale, and the request is forwarded to master, stating that all requests are supported by Master when slave all fails.

When the two slave is restored, the overall status is automatically restored, and when the request is executed from the client, it can go to slave.

Summary

Through the test found that in the case of partial slave failure, for the client is completely transparent, when all slave failure, after a simple configuration, Maxscale can also be handled very well.

Source: Performance and Architecture subscription number (Id:yogoup) Du Yishu

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