MaxScale tutorial with MariaDB replication

Preamble

In my investigations to implement a highly available MySQL I have tested the high available features of Connector/J, Galera cluster and HAProxy. The new product that is coming on the market is a MySQL proxy from MariaDB (formerly SkySQL) called MaxScale. Same as MySQL Proxy from Oracle (that is in alpha since so long), this proxy is a high level proxy able to understand the statements that are going through it. So a bit clever than HAProxy that has no understanding of the routed SQL statement.

From official documentation MaxScale can:

  • Load balance both connections, and individual requests.
  • Log requests, then forward them to database servers for handling.
  • Filter requests according to a set of rules, to implement access policies.
  • Allow for more interoperable, federated authentication methods beyond basic database accesscontrol to grant application access.
  • Scale large tables by implementing transparent sharding outside of the application.
  • Translate and mediate between alternative query languages and protocols, letting applications designed to use different database technologies access the same resources or letting applications use different back-end databases without recoding.
  • Monitor the availability, utilization, and load on back-end servers to prevent requests from going to overburdened or unavailable resources.

The feature that has kept the most my attention is the one able to automatically redirect to good server (master or slaves) a read-only or a read-write SQL statement. And to be honest this is a great feature HAProxy is lacking. It simply means you do not have to change your application to make it benefit from MySQL Replication. The feature can also redirect to same Galera node the read-write statements and avoid any conflict due to optimistic locking Galera cluster is using.

My testing has been done using three virtual machines with VirtualBox. All running Oracle Linux Server release 7.0 64 bits. server1.domain.com (192.168.56.101) will by my MySQL master server, server2.domain.com (192.168.56.102) my MySQL slave server and server3.domain.com (192.168.56.103) my MaxScale server.

I will be as well using MariaDB 10.0.16 from MariaDB foundation. MaxScale is coming from compiled binary (official repository) and is release 1.0.5.

MariaDB replication

I’m not entering too much in detail of MariaDB replication as it is pretty equivalent to Oracle MySQL Replication even if their implementation of GTID is different of the one of Oracle we have already seen in a previous blog post.

I have used below my.cnf file on both server (server_id to be changed) and the MOCA architecture layout already presented:

[mysqld]
user=mysql
basedir=/mysql/software/mysql01
datadir=/mysql/data01/mysql01
port=3316
pid_file=/mysql/software/mysql01/conf/mysql01.pid
log_error=/mysql/dump/mysql01/mysql01.err
default-storage-engine=innodb
tmpdir=/tmp
binlog_format=row
server-id=1
log_bin=/mysql/logs/mysql01/mysql-bin.log
socket=/mysql/software/mysql01/conf/mysql01.sock
sql_mode='TRADITIONAL,PIPES_AS_CONCAT'
slow_query_log_file=/mysql/dump/mysql01/mysql01-slow.log
slow_query_log=1
long_query_time=5

innodb_buffer_pool_size=50M
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=12M
innodb_log_file_size=50M
innodb_log_files_in_group = 4
innodb_thread_concurrency=16
innodb_rollback_on_timeout=1
innodb_lock_wait_timeout=600
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2

query_cache_size=100m

[mysql]
default-character-set=utf8
socket=/mysql/software/mysql01/conf/mysql01.sock

[client]
default-character-set=utf8
socket=/mysql/software/mysql01/conf/mysql01.sock

On both server I initialize MariaDB with:

[mysql@server1 mysql01]$ pwd
/mysql/software/mysql01
[mysql@server1 mysql01]$ ./scripts/mysql_install_db --basedir=/mysql/software/mysql01 --datadir=/mysql/data01/mysql01

And I create, on both, a replicationdb database with:

MariaDB [(none)]> create database replicationdb character set utf32 collate utf32_general_ci;
Query OK, 1 row affected (0.02 sec)

Then I have decided to use same account (repadmin) for MariaDB replication and MaxScale, only first grant is need for pure MySQL replication. Must be executed on all servers:

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repadmin'@'%' IDENTIFIED BY 'secure_password';
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> grant select on mysql.user to 'repadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> grant select on mysql.db to 'repadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]>  grant show databases on *.* to 'repadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> grant replication client on *.* to 'repadmin'@'%';
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Then on master server (server1.domain.com) I reset the binary log position to avoid replication of any unwanted SQL commands:

MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.03 sec)
 
MariaDB [(none)]> show variables like '%gtid%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| gtid_binlog_pos        |       |
| gtid_binlog_state      |       |
| gtid_current_pos       |       |
| gtid_domain_id         | 0     |
| gtid_ignore_duplicates | OFF   |
| gtid_seq_no            | 0     |
| gtid_slave_pos         |       |
| gtid_strict_mode       | OFF   |
| last_gtid              | 0-1-6 |
+------------------------+-------+
9 rows in set (0.00 sec)

Then on server2.domain.com I execute the following:

MariaDB [(none)]> show variables like '%gtid%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| gtid_binlog_pos        |        |
| gtid_binlog_state      |        |
| gtid_current_pos       |        |
| gtid_domain_id         | 0      |
| gtid_ignore_duplicates | OFF    |
| gtid_seq_no            | 0      |
| gtid_slave_pos         |        |
| gtid_strict_mode       | OFF    |
| last_gtid              | 0-2-11 |
+------------------------+--------+
9 rows in set (0.01 sec)
 
MariaDB [(none)]> change master to master_host='server1.domain.com', master_user='repadmin', master_password='secure_password',
    -> master_port=3316, master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.03 sec)
 
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: server1.domain.com
                  Master_User: repadmin
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 654
               Relay_Log_File: mysql01-relay-bin.000002
                Relay_Log_Pos: 941
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 654
              Relay_Log_Space: 1240
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-1-2
1 row in set (0.00 sec)

And that’s it, replication is working and you can start by creating a small test table on server1.domain.com that will be replicated to server2.domain.com:

MariaDB [(none)]> create table replicationdb.test1(id integer, descr varchar(50)) engine=innodb;
Query OK, 0 rows affected (0.05 sec)
 
MariaDB [(none)]> insert into replicationdb.test1 values(1,'One');
Query OK, 1 row affected (0.00 sec)
 
MariaDB [(none)]> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3316 |         1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)

That I can fetch on slave server:

MariaDB [(none)]> select * from replicationdb.test1;
+------+-------+
| id   | descr |
+------+-------+
|    1 | One   |
+------+-------+
1 row in set (0.00 sec)

Maxscale configuration

To install MaxScale I have used the provided binaries and so the official repository. I used RHEL 7 repository that is compatible with OEL 7. Once repository is configured and key imported the installation is straightforward:

[root@server3 ~]# yum -y install maxscale

When trying to not put password in clear in configuration file (/usr/local/skysql/maxscale/etc/MaxScale.cnf) I had an issue:

[root@server3 ~]# maxkeys /usr/local/skysql/maxscale/etc/.secrets
[root@server3 ~]# maxpasswd secure_password
Failed to encode the password

Please see link in references section, this can be solved by below trick:

[root@server3 ~]# mkdir -p /usr/local/skysql/MaxScale/etc/
[root@server3 ~]# cp /usr/local/skysql/maxscale/etc/.secrets /usr/local/skysql/MaxScale/etc/
[root@server3 ~]# maxpasswd secure_password
6D2D89DBF4C7D41BE4C2C262A092B41F

Then my configuration file is (the CLI part is for MaxScale admin), the detect_stale_master=1 is quite important if you want to handle a slave server failure:

[maxscale]
threads=4

[Replication Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=repadmin
passwd=6D2D89DBF4C7D41BE4C2C262A092B41F
detect_stale_master=1

[RW Split Router]
type=service
router=readwritesplit
servers=server1,server2
user=repadmin
passwd=6D2D89DBF4C7D41BE4C2C262A092B41F

[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=3316

[CLI]
type=service
router=cli

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603

[server1]
type=server
address=192.168.56.101
port=3316
protocol=MySQLBackend

[server2]
type=server
address=192.168.56.102
port=3316
protocol=MySQLBackend

Start MaxScale with:

[root@server3 ~]# systemctl start maxscale
[root@server3 ~]# ps -ef | grep maxscale | grep -v grep
root      3268     1  0 18:04 ?        00:00:00 /usr/local/skysql/maxscale/bin/maxscale

Then from MaxScale admin console you can confirm all is working fine (default password is skysql):

[root@server3 ~]# maxadmin -pskysql
MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.56.101  |  3316 |           0 | Master, Running
server2            | 192.168.56.102  |  3316 |           0 | Slave, Running
-------------------+-----------------+-------+-------------+--------------------
MaxScale> show servers
Server 0x20123a0 (server1)
        Server:                         192.168.56.101
        Status:                         Master, Running
        Protocol:                       MySQLBackend
        Port:                           3316
        Server Version:                 10.0.16-MariaDB-log
        Node Id:                        1
        Master Id:                      -1
        Slave Ids:                      2
        Repl Depth:                     0
        Number of connections:          1
        Current no. of conns:           0
        Current no. of operations:      0
Server 0x2012290 (server2)
        Server:                         192.168.56.102
        Status:                         Slave, Running
        Protocol:                       MySQLBackend
        Port:                           3316
        Server Version:                 10.0.16-MariaDB-log
        Node Id:                        2
        Master Id:                      1
        Slave Ids:
        Repl Depth:                     1
        Number of connections:          1
        Current no. of conns:           0
        Current no. of operations:      0

Remark:
As MaxScale server will be the entry point for any connections coming from your application. To avoid this server to become a Single Point Of Failure (SPOF) it is a good idea to make it highly available. Installing it in a virtual machine (few I/Os anyway) would be a typical architecture.

Maxscale testing

I first installed MariaDB client on my MaxScale server to perform few basic testing. In below example I initiate a connection on my MaxScale server (server3.domain.com) we see that a read only transaction goes automatically to MariaDB slave server (server2.domain.com):

[root@server3 ~]# mysql --host=server3.domain.com --port=3316 --user=yjaquier --password=secure_password
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3284
Server version: 5.5.41-MariaDB MariaDB Server
 
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> select variable_value from information_schema.global_variables where variable_name='hostname';
+--------------------+
| variable_value     |
+--------------------+
| server2.domain.com |
+--------------------+
1 row in set (0.00 sec)

While a read write transaction goes automatically to MariaDB master server (server1.domain.com):

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> select variable_value from information_schema.global_variables where variable_name='hostname';
+--------------------+
| variable_value     |
+--------------------+
| server1.domain.com |
+--------------------+
1 row in set (0.01 sec)
 
MariaDB [(none)]> commit;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> select variable_value from information_schema.global_variables where variable_name='hostname';
+--------------------+
| variable_value     |
+--------------------+
| server2.domain.com |
+--------------------+
1 row in set (0.00 sec)

If I stop slave server (server2.domain.com) master server (server1.domain.com) continue to serve read only and read write transactions (I noticed a bad behavior with MaxScale 1.0.4 !!):

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.56.101  |  3316 |           2 | Master, Stale Status, Running
server2            | 192.168.56.102  |  3316 |        9191 | Down
-------------------+-----------------+-------+-------------+--------------------

But you need to re-initiate the connection to benefit again from slave server once you have restored it. Already initiated connection are not impacted when slave server is stopped…

When master server is down existing sessions are killed and you cannot initiate a new one. Expected behavior I would say, even if some could argue that read only statements are still possible. A good idea would be then to put slave servers in read only to avoid any replication corruption…

[root@server3 ~]# maxadmin -pskysql list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.56.101  |  3316 |           1 | Down
server2            | 192.168.56.102  |  3316 |       13914 | Running
-------------------+-----------------+-------+-------------+--------------------
[root@server3 ~]# mysql --host=server3.domain.com --port=3316 --user=yjaquier --password=secure_password
ERROR 1045 (28000): failed to create new session

Remark:
If you have multiple slave servers that read only transactions are spread all over your slave servers to distribute the load…

References

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>