Table of contents
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
