MariaDB MaxScale: A smart proxy for MySQL

Director

Installation and Configuration

MaxScale is available from the MariaDB website as a free download [3]. The manufacturer also distributes the product as open source software; the entire source code of MaxScale is available from GitHub [4]. The open source version has all of the existing features, so an enterprise version is not necessary.

Moreover, it is not necessary to compile MaxScale from the sources: In the user portal [5], you will find instructions for using MaxScale packages for CentOS, SUSE, Ubuntu, and Debian. After you enable the corresponding package source, all you need to do on Ubuntu 14.04 is enter

apt-get install maxscale

to complete the installation.

After the installation, you need to configure. The good news is that MaxScale comes with a single configuration file, and about 60 lines are sufficient for a working installation. The example in Listing 1 is designed for a Galera cluster, which distributes incoming requests to the back ends with the help of the readconnroute router. The individual parameters are practically self-explanatory, but you will probably want to check the MaxScale documentation for the router_options values [6].

Listing 1

MaxScale with Galera Back End

01 [maxscale]
02 threads=4
03
04 [Galera Router]
05 type=service
06 router=readconnroute
07 router_options=synced
08 servers=backend1, [...]
09 user=maxscale
10 passwd=secret
11
12 [Galera Listener]
13 type=listener
14 service=Galera Router
15 protocol=MySQLClient
16 address=10.42.0.1
17 port=3306
18
19 [Galera Monitor]
20 type=monitor
21 module=galeramon
22 servers=backend1, [...]
23 user=maxscale
24 passwd=secret
25
26 [Debug Listener]
27 type=listener
28 service=Debug Interface
29 protocol=telnetd
30 port=4442
31
32 [backend1]
33 type=server
34 address=10.42.0.2
35 port=3006
36 protocol=MySQLBackend
37
38 [CLI]
39 type=service
40 router=cli
41
42 [CLI Listener]
43 type=listener
44 service=CLI
45 protocol=maxscaled
46 address=localhost
47 port=6603

The user specified for user and password must actually exist in the database. Without it, MaxScale has no way of monitoring the Galera cluster back ends. The reward for all this effort is an instance of MaxScale that distributes incoming requests to the same extent across three back ends, which is the simplest MaxScale setup: a load balancer.

Router Parameters

Using router_options, users can determine the principle by which MaxScale selects a back end for a request. In the case of readconnroute, this is done exclusively on the basis of the state of the existing back ends. The synced option in line 10 tells MaxScale that this is a Galera cluster and that a back end can only be a target if it is a synchronized cluster member.

The router_options possibilities for a master-slave setup are master and slave. The running option, on the other hand, basically allows any host as a target if MaxScale can connect to its database. An optional parameter, weightby, allows you to prioritize your machines by implementing weighting in the best load-balancer style.

The configuration for a Galera cluster shown here ultimately amounts to a classic load-balancing scenario. However, in contrast to MaxScale, a typical load balancer would hardly be in a position to coordinate its distribution mechanism based on the state of the MySQL instances on the back ends.

Read-Write Splits

Whereas Galera is only gradually asserting itself in many companies, MySQL master-slave setups are commonplace. The readwritesplit MaxScale router is useful in these cases: It analyzes MySQL queries and forwards read and write queries to different back ends.

The example in Listing 2 contains a sample configuration for a master-slave setup. Write access is sent to the master and read queries to the slave. It lists only the relevant parts for the setup – everything else can be taken from the sample configuration for the Galera setup in Listing 1.

Listing 2

MaxScale for a Master-Slave Setup

01 [ReadWrite Split Router]
02 type=service
03 router=readwritesplit
04 servers=backend1, [...]
05 user=maxscale
06 passwd=secret
07 max_slave_connections=100%
08 max_slave_replication_lag=21
09
10 [ReadWrite Split Listener]
11 type=listener
12 service=ReadWrite Split Router
13 protocol=MySQLClient
14 port=3306
15 address=10.42.0.1

The max_slave_connections and max_slave_replication_lag parameters are important. By specifying 100% for the first parameter (line 7), MaxScale can use any MySQL slave. If you were to enter 80% here, MaxScale would use only four of five slaves. The 21 for the second parameter (line 8) sets the time in seconds that a slave may lag behind its master until MaxScale ignores it.

Out of the box, the readwritesplitrouter comes with a fixed table of query types for MySQL, which are divided into reads and writes, although users can define additional filters. The regex filter lets you classify queries for other types of splitting – other than read-write splitting – with the use of regular expressions.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus