ProxySQL high availability tutorial with MariaDB replication

Posted on , updated on by Yannick Jaquier
MariaDB

Table of contents

Preamble

In a recent webinar I attended I have seen mention of a Maxscale alternative called ProxySQL. Maxscale is a proven working high level proxy but ProxySQL author (René Cannaò) is quite dithyrambic on his product mainly on performance related benchmarks. So better I give a try to it but I will not make any comment on performance. Aim of the post is a simple ProxySQL implementation in a MariaDB replication architecture.

Testing has been done using three virtual machines running Oracle Enterprise Linux 7.2 64 bits:

  • server2.domain.com (192.168.56.102) is MariaDB 10.1.14 64 bits master server.
  • server3.domain.com (192.168.56.103) is MariaDB 10.1.14 64 bits slave server running two MariaDB slave instances.
  • server4.domain.com (192.168.56.104) is ProxySQL node with MysQL 5.7.13 64 bits for client binary.

The Java test application is running under Eclipse Java EE IDE for Web Developers version Neon Release (4.6.0) with MariaDB connector/J 1.4.6 and Oracle MySQL Connector/J 5.1.39.

The MariaDB replication architecture is made of:

  • server2.domain.com on port 3316 as master instance
  • server3.domain.com on port 3316 as first slave instance
  • server3.domain.com on port 3326 as second slave instance

I’m not re-entering in MariaDB replication implementation as we have already seen it.

ProxySQL release available at the time of this article is 1.2.0j. Last but not least ProxySQL can be used for query caching and query rewrite !!

ProxySQL configuration

To install ProxySQL either you compile it or you download a rpm for your release. I have used the one of Centos 7 (proxysql-1.2.0-1-centos7.x86_64.rpm) that is working fine on OEL 7 available at https://github.com/sysown/proxysql/releases.

[root@server4 tmp]# yum install proxysql-1.2.0-1-centos7.x86_64.rpm
Loaded plugins: ulninfo
Examining proxysql-1.2.0-1-centos7.x86_64.rpm: proxysql-1.2.0-1.x86_64
Marking proxysql-1.2.0-1-centos7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package proxysql.x86_64 0:1.2.0-1 will be installed
--> Finished Dependency Resolution
 
Dependencies Resolved
 
===========================================================================================================================================================================================================
 Package                                    Arch                                     Version                                      Repository                                                          Size
===========================================================================================================================================================================================================
Installing:
 proxysql                                   x86_64                                   1.2.0-1                                      /proxysql-1.2.0-1-centos7.x86_64                                    11 M
 
Transaction Summary
===========================================================================================================================================================================================================
Install  1 Package
 
Total size: 11 M
Installed size: 11 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : proxysql-1.2.0-1.x86_64                                                                                                                                                                 1/1
  Verifying  : proxysql-1.2.0-1.x86_64                                                                                                                                                                 1/1
 
Installed:
  proxysql.x86_64 0:1.2.0-1
 
Complete!

Start it:

[root@server4 ~]# service proxysql start
Starting ProxySQL: Main init phase0 completed in 3.9e-05 secs.
Main init global variables completed in 0.000236 secs.
Main daemonize phase1 completed in 3.2e-05 secs.
DONE!

I expected a graphical interface to customize options but ProxySQL is fully command line, not an issue ! ProxySQL information is stored is a MySQL back end database so any modification will be done through SQL language. To connect to ProxySQL back-end database you need a MySQL client. Either you install the one of your Linux distribution via standard repository or you install your own one. I have chosen to use MySQL 5.7.13 installed in /mysql/software/mysql01 but new security rules has made things a little bit harder:

[mysql@server4 ~]$ /mysql/software/mysql01/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: socket layer receive error

I initially tried the skip-ssl option that is working but deprecated:

[mysql@server4 ~]$ /mysql/software/mysql01/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 --skip-ssl
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 5.1.30 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

The working up-to-date option is ssl-mode:

[mysql@server4 ~]$ /mysql/software/mysql01/bin/mysql -u admin -padmin -h 127.0.0.1 -P6032 --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

Insert your MariaDB replication topology, load it live and save it to disk with:

mysql> insert into mysql_servers(hostgroup_id, hostname, port) values (0,'192.168.56.102',3316);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into mysql_servers(hostgroup_id, hostname, port) values (1,'192.168.56.103',3316);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into mysql_servers(hostgroup_id, hostname, port) values (1,'192.168.56.103',3326);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.01 sec)
 
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from disk.mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)

Second step is to create an applicative account on ProxySQL. This account must also be created on the MariaDB replication architecture. On master instance, account creation will be replicated on slaves:

grant all privileges on replicationdb.* to 'test'@'%' identified by 'secure_password';

And do it in ProxySQL repository database:

mysql> insert into mysql_users (username,password) values ('test','secure_password');
Query OK, 1 row affected (0.00 sec)
 
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from disk.mysql_users;
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password        | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| test     | secure_password | 1      | 0       | 0                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
+----------+-----------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)
 
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)

Humm passwords are stored in clear, it’s a planned feature to encrypt them… Note the default hostgroup set to 0 (master server).

Let’s do a test of a read-only SQL command:

[root@server4 ~]# /mysql/software/mysql01/bin/mysql --user=test --password=secure_password --host=127.0.0.1 --port=6033 -e 'SELECT @@hostname,@@port' --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+
| @@hostname         | @@port |
+--------------------+--------+
| server2.domain.com |   3316 |
+--------------------+--------+

If you execute above command multiple times only master server is chosen. Read/write split is not implemented by default and if you want a Maxscale behavior you have to create query routing rules…

I have also activated ProxySQL backend servers monitoring:

mysql> SELECT * FROM global_variables where variable_name like 'mysql-monitor%';
+----------------------------------------+---------------------------------------------------+
| variable_name                          | variable_value                                    |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history                  | 600000                                            |
| mysql-monitor_connect_interval         | 120000                                            |
| mysql-monitor_connect_timeout          | 200                                               |
| mysql-monitor_ping_interval            | 60000                                             |
| mysql-monitor_ping_max_failures        | 3                                                 |
| mysql-monitor_ping_timeout             | 100                                               |
| mysql-monitor_read_only_interval       | 1000                                              |
| mysql-monitor_read_only_timeout        | 100                                               |
| mysql-monitor_replication_lag_interval | 10000                                             |
| mysql-monitor_replication_lag_timeout  | 1000                                              |
| mysql-monitor_username                 | monitor                                           |
| mysql-monitor_password                 | monitor                                           |
| mysql-monitor_query_variables          | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status             | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS    |
| mysql-monitor_query_interval           | 60000                                             |
| mysql-monitor_query_timeout            | 100                                               |
| mysql-monitor_timer_cached             | true                                              |
| mysql-monitor_writer_is_also_reader    | true                                              |
+----------------------------------------+---------------------------------------------------+
18 rows in set (0.01 sec)
 
mysql> update global_variables set variable_value='secure_password' where variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
 
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
 
mysql> save mysql variables to disk;
Query OK, 64 rows affected (0.02 sec)

I create the ProxySQL monitor account on master server, it will be automatically replicated to slaves:

MariaDB [(none)]> grant replication client on *.* to 'monitor'@'%' identified by 'secure_password';
Query OK, 0 rows affected (0.00 sec)

After a small period you can control it runs successfully with:

mysql> select * from monitor.mysql_server_replication_lag_log order by time_start desc limit 10;
+----------------+------+------------------+--------------+----------+-------+
| hostname       | port | time_start       | success_time | repl_lag | error |
+----------------+------+------------------+--------------+----------+-------+
| 192.168.56.102 | 3316 | 1467715539097299 | 810          | NULL     | NULL  |
| 192.168.56.103 | 3316 | 1467715539097299 | 621          | 0        | NULL  |
| 192.168.56.103 | 3326 | 1467715539097299 | 1022         | 0        | NULL  |
| 192.168.56.102 | 3316 | 1467715529097105 | 780          | NULL     | NULL  |
| 192.168.56.103 | 3316 | 1467715529097105 | 816          | 0        | NULL  |
| 192.168.56.103 | 3326 | 1467715529097105 | 1024         | 0        | NULL  |
| 192.168.56.102 | 3316 | 1467715519096898 | 786          | NULL     | NULL  |
| 192.168.56.103 | 3316 | 1467715519096898 | 655          | 0        | NULL  |
| 192.168.56.103 | 3326 | 1467715519096898 | 1111         | 0        | NULL  |
| 192.168.56.102 | 3316 | 1467715509096195 | 885          | NULL     | NULL  |
+----------------+------+------------------+--------------+----------+-------+
10 rows in set (0.00 sec)
 
mysql> select * from monitor.mysql_server_ping_log order by time_start desc limit 10;
+----------------+------+------------------+-------------------+------------+
| hostname       | port | time_start       | ping_success_time | ping_error |
+----------------+------+------------------+-------------------+------------+
| 192.168.56.102 | 3316 | 1467715518924380 | 721               | NULL       |
| 192.168.56.103 | 3316 | 1467715518924380 | 525               | NULL       |
| 192.168.56.103 | 3326 | 1467715518924380 | 701               | NULL       |
| 192.168.56.102 | 3316 | 1467715458924137 | 682               | NULL       |
| 192.168.56.103 | 3316 | 1467715458924137 | 517               | NULL       |
| 192.168.56.103 | 3326 | 1467715458924137 | 703               | NULL       |
| 192.168.56.102 | 3316 | 1467715398923945 | 685               | NULL       |
| 192.168.56.103 | 3316 | 1467715398923945 | 531               | NULL       |
| 192.168.56.103 | 3326 | 1467715398923945 | 843               | NULL       |
| 192.168.56.102 | 3316 | 1467715338923725 | 678               | NULL       |
+----------------+------+------------------+-------------------+------------+
10 rows in set (0.00 sec)
 
mysql> select * from monitor.mysql_server_connect_log order by time_start desc limit 10;
+----------------+------+------------------+----------------------+---------------+
| hostname       | port | time_start       | connect_success_time | connect_error |
+----------------+------+------------------+----------------------+---------------+
| 192.168.56.102 | 3316 | 1467715518941492 | 1524                 | NULL          |
| 192.168.56.103 | 3316 | 1467715518941492 | 2105                 | NULL          |
| 192.168.56.103 | 3326 | 1467715518941492 | 2232                 | NULL          |
| 192.168.56.102 | 3316 | 1467715398941430 | 1734                 | NULL          |
| 192.168.56.103 | 3316 | 1467715398941430 | 2450                 | NULL          |
| 192.168.56.103 | 3326 | 1467715398941430 | 1357                 | NULL          |
| 192.168.56.102 | 3316 | 1467715278941119 | 1437                 | NULL          |
| 192.168.56.103 | 3316 | 1467715278941119 | 1937                 | NULL          |
| 192.168.56.103 | 3326 | 1467715278941119 | 4414                 | NULL          |
| 192.168.56.102 | 3316 | 1467715158941062 | 1806                 | NULL          |
+----------------+------+------------------+----------------------+---------------+
10 rows in set (0.00 sec)

I have not well understood if mysql_replication_hostgroups table must be configured, even without it, it should work:

mysql> insert into mysql_replication_hostgroups values(0,1);
Query OK, 1 row affected (0.01 sec)
 
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
 
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+
| writer_hostgroup | reader_hostgroup |
+------------------+------------------+
| 0                | 1                |
+------------------+------------------+
1 row in set (0.00 sec)
 
mysql> select * from disk.mysql_replication_hostgroups;
+------------------+------------------+
| writer_hostgroup | reader_hostgroup |
+------------------+------------------+
| 0                | 1                |
+------------------+------------------+
1 row in set (0.01 sec)

If you hit strange behavior, like I had, showing duplicated nodes across hostgroup:

mysql> SELECT * FROM mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 0            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 0            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
5 rows in set (0.00 sec)
 
mysql> select * from disk.mysql_server;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.01 sec)

You have to set your slaves read only with:

MariaDB [(none)]> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

For below error message when using MySQL Connector/J 5.1.39:

Error message: Unknown system variable 'language'

You have to set ProxySQL MySQL version above 5.1.31 with something like (mysql-server_version is just a display for ProxySQL):

mysql> set mysql-server_version='5.7.13';
Query OK, 1 row affected (0.00 sec)
 
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
 
mysql> save mysql variables to disk;
Query OK, 64 rows affected (0.01 sec)

ProxySQL testing

As stated in ProxySQL website to simulate read/write split you have to create query routing rules, just using what is given in ProxySQL web site:

mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values (1,'^SELECT.*FOR UPDATE$',0,1);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values (1,'^SELECT',1,1);
Query OK, 1 row affected (0.00 sec)
 
mysql> select rule_id, match_pattern,destination_hostgroup, apply from mysql_query_rules;
+---------+----------------------+-----------------------+-------+
| rule_id | match_pattern        | destination_hostgroup | apply |
+---------+----------------------+-----------------------+-------+
| 1       | ^SELECT.*FOR UPDATE$ | 0                     | 1     |
| 2       | ^SELECT              | 1                     | 1     |
+---------+----------------------+-----------------------+-------+
2 rows in set (0.00 sec)
 
mysql> save mysql query rules to disk;
Query OK, 0 rows affected (0.00 sec)
 
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

This example query routing rules listed in official documentation are routing select queries (read-only) to hostgroup 1 (slave servers) and select for update queries (read-write) to master server. Queries not passing any of the rules goes to default hostgroup that is master server. With a classical MySQL client connection it gives:

[root@server4 ~]# /mysql/software/mysql01/bin/mysql --user=test --password=secure_password --host=127.0.0.1 --port=6033 -e 'SELECT @@hostname,@@port' --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+
| @@hostname         | @@port |
+--------------------+--------+
| server3.domain.com |   3326 |
+--------------------+--------+
[root@server4 ~]# /mysql/software/mysql01/bin/mysql --user=test --password=secure_password --host=127.0.0.1 --port=6033 -e 'SELECT @@hostname,@@port' --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+--------+
| @@hostname         | @@port |
+--------------------+--------+
| server3.domain.com |   3316 |
+--------------------+--------+

To further test it I have used the typical Java application I have used in many other blog posts. Here I initiate a connection and loop to perform a read-write and a read-only query. Obviously I am not disconnecting and reconnecting after each loop mainly because a standard Java application would not do it.

To be honest I have faced lots of issue making this Java application work and round robin onto my slave servers, fortunately I have been helped by René directly at this forum thread https://groups.google.com/forum/#!topic/proxysql/Rzwd55xIQGk. First René explained that due to @ symbol ProxySQL multiplexing is disabled to ensure consistency on users’ variables. So he suggested to change my test query by something like:

SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='hostname') `hostname`,(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='port') `port`;

Then, directed by René, I realized that I stupidly changed the value of mysql-multiplexing to false (default value is true), so changed it back to original value:

mysql> set mysql-multiplexing='true';
Query OK, 1 row affected (0.01 sec)
 
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)
 
mysql> save mysql variables to disk;
Query OK, 64 rows affected (0.01 sec)

Finally below Java code (you can de-comment the Connector/J you wish to use, Java code is compliant with both):

package jdbcdemo7;
 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import org.mariadb.jdbc.Driver;
//import com.mysql.jdbc.Driver;
 
public class jdbcdemo7 {
	public static void main(String[] args) throws Exception {
		Driver driver = new Driver();
		Properties props = new Properties();
		ResultSet rs;
		String variable_value;
		Connection conn = null;
		String JDBC_URL = "jdbc:mysql://address=(protocol=tcp)(host=192.168.56.104)(port=6033)";
 
		props.put("useSSL", "false");
		props.put("user", "test");
		props.put("password", "secure_password");
 
		System.out.println("\n------------ MariaDB Connector/J and ProxySQL Testing ------------\n");
 
		System.out.println("Trying connection...");
		try {
			conn = driver.connect(JDBC_URL, props);
		}
			catch (SQLException e) {
			System.out.println("Connection Failed!");
			System.out.println("Error cause: "+e.getCause());
			System.out.println("Error message: "+e.getMessage());
			return;
		}
 
		System.out.println("Connection established...");
 
		for(int i=1; i <= 50; i++) {
			System.out.println("\nQuery "+i+": ");
			// Read write query that can be performed ONLY on master server
			System.out.println("Read Write query...");
			try {
				rs = conn.createStatement().executeQuery("select (select variable_value from information_schema.global_variables where variable_name=\'hostname\') || \' on port \' || (select variable_value from information_schema.global_variables where variable_name=\'port\') variable_value for update");
				while (rs.next()) {
					variable_value = rs.getString("variable_value");
					System.out.println("variable_value : " + variable_value);
				}
			}
			catch (SQLException e) {
				System.out.println("Read/write query has failed...");
			}
 
 
				// Read Only statement (that can also be done on master server if all slaves are down)
			System.out.println("Read Only query...");
 
			try {
				rs = conn.createStatement().executeQuery("select (select variable_value from information_schema.global_variables where variable_name=\'hostname\') || \' on port \' || (select variable_value from information_schema.global_variables where variable_name=\'port\') variable_value");
				while (rs.next()) {
					variable_value = rs.getString("variable_value");
					System.out.println("variable_value : " + variable_value);
				}
			}
			catch (SQLException e) {
				System.out.println("Read only query has failed...");
			}
 
			Thread.sleep(1000);
		}
		conn.close();
	}
}

To provide under Eclipse below nice result:

proxysql01
proxysql01

Few statistics tables are also available:

mysql> select * from stats_mysql_connection_pool;
+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 192.168.56.102 | 3316     | ONLINE | 0        | 0        | 23     | 0       | 1012    | 73908           | 61508           | 0          |
| 1         | 192.168.56.103 | 3316     | ONLINE | 0        | 0        | 32     | 0       | 307     | 18160           | 10868           | 0          |
| 1         | 192.168.56.103 | 3326     | ONLINE | 0        | 0        | 40     | 0       | 480     | 28544           | 17024           | 0          |
+-----------+----------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)
 
mysql> select hostgroup, sum(queries), sum(bytes_data_sent), sum(bytes_data_recv) from stats_mysql_connection_pool group by hostgroup;
+-----------+--------------+----------------------+----------------------+
| hostgroup | SUM(Queries) | SUM(Bytes_data_sent) | SUM(Bytes_data_recv) |
+-----------+--------------+----------------------+----------------------+
| 0         | 667          | 48373                | 26196                |
| 1         | 664          | 40256                | 23712                |
+-----------+--------------+----------------------+----------------------+
2 rows in set (0.00 sec)

For the complete list of statistics tables use:

mysql> show tables from stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| stats_mysql_query_rules        |
| stats_mysql_commands_counters  |
| stats_mysql_processlist        |
| stats_mysql_connection_pool    |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_global             |
+--------------------------------+
7 rows in set (0.00 sec)

ProxySQL high availability testing

One slave dead

If I shutdown one of the slave above Java application is not throwing any error and continue to run transparently on only remaining slave.

mysql> SELECT * FROM stats_mysql_connection_pool;
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status  | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 192.168.56.102 | 3316     | ONLINE  | 0        | 1        | 1      | 0       | 28      | 6317            | 1192            | 587        |
| 1         | 192.168.56.103 | 3316     | ONLINE  | 0        | 1        | 1      | 0       | 18      | 4086            | 648             | 530        |
| 1         | 192.168.56.103 | 3326     | SHUNNED | 0        | 0        | 1      | 12      | 8       | 1816            | 288             | 901        |
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)

Once the dead slave is back to live ProxySQL use it automatically.

Two slaves dead

If I shutdown my two slaves then read only queries are no more possible:

mysql> SELECT * FROM stats_mysql_connection_pool;
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status  | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 192.168.56.102 | 3316     | ONLINE  | 0        | 1        | 1      | 0       | 92      | 21549           | 3496            | 919        |
| 1         | 192.168.56.103 | 3316     | SHUNNED | 0        | 0        | 1      | 18      | 76      | 17252           | 2700            | 896        |
| 1         | 192.168.56.103 | 3326     | SHUNNED | 0        | 0        | 2      | 84      | 13      | 2951            | 432             | 901        |
+-----------+----------------+----------+---------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)

With few ProxySQL commands it would be possible to correct this and allocate master server in hostgroup 1 to allow it to resolve read only queries.

When at least one of the slaves is back to life application return in normal running state…

Master server dead

Of course read write queries are no more possible when master server is down ! statistics are a bit strange:

mysql> SELECT * FROM stats_mysql_connection_pool;
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 192.168.56.102 | 3316     | OFFLINE_HARD | 0        | 0        | 1      | 0       | 127     | 29532           | 4940            | 622        |
| 1         | 192.168.56.103 | 3316     | ONLINE       | 0        | 1        | 2      | 131     | 84      | 19068           | 2988            | 759        |
| 1         | 192.168.56.103 | 3326     | ONLINE       | 0        | 1        | 3      | 198     | 22      | 4994            | 756             | 660        |
| 1         | 192.168.56.102 | 3316     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 0          |
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
4 rows in set (0.00 sec)

In this situation either you promote a slave to new master role and ProxySQL must be tuned, nothing complex here. Or you are able to restart your master server, most probable situation.

When my master server was again alive I have seen that ProxySQL has added it to read only group (hostgroup 1) and now read only queries are also served by it. I have resolved this weird situation with:

mysql> delete from mysql_servers where hostgroup_id=1 and hostname='192.168.56.102';
Query OK, 1 row affected (0.00 sec)
 
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
| 1            | 192.168.56.103 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 1            | 192.168.56.103 | 3326 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
| 0            | 192.168.56.102 | 3316 | ONLINE | 1      | 0           | 1000            | 10                  | 0       | 10             |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+
3 rows in set (0.00 sec)
 
mysql> select * from stats_mysql_connection_pool;
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host       | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 0         | 192.168.56.102 | 3316     | ONLINE       | 0        | 1        | 1      | 0       | 53      | 12614           | 1908            | 682        |
| 1         | 192.168.56.103 | 3316     | ONLINE       | 0        | 1        | 2      | 131     | 110     | 24970           | 3924            | 1477       |
| 1         | 192.168.56.103 | 3326     | ONLINE       | 0        | 1        | 3      | 198     | 47      | 10669           | 1656            | 1101       |
| 1         | 192.168.56.102 | 3316     | OFFLINE_HARD | 0        | 0        | 1      | 4       | 13      | 2951            | 468             | 682        |
+-----------+----------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
4 rows in set (0.01 sec)

If you restart ProxySQL everything is cleared…

ProxySQL additional features

Query caching

Use this trick to reset statistics:

select 1 from stats_mysql_query_digest_reset;

One nice feature is query caching that reminds me the benefit I have seen with Memcached. After a run of my Java test application with 1000 loop I get below obvious statistics:

mysql> select hostgroup,count_star,min_time,max_time,sum_time,digest_text from stats_mysql_query_digest;
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostgroup | count_star | min_time | max_time | sum_time | digest_text                                                                                                                                                                                                           |
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1         | 1000       | 1052     | 4287     | 1778360  | select (select variable_value from information_schema.global_variables where variable_name=?) || ?|| (select variable_value from information_schema.global_variables where variable_name=?) variable_value            |
| 0         | 1000       | 1018     | 2209     | 1554333  | select (select variable_value from information_schema.global_variables where variable_name=?) || ?|| (select variable_value from information_schema.global_variables where variable_name=?) variable_value for update |
| 0         | 1          | 536      | 536      | 536      | set session autocommit=?                                                                                                                                                                                              |
| 0         | 1          | 1722     | 1722     | 1722     | SHOW VARIABLES WHERE Variable_name in (?, ?, ?, ?)                                                                                                                                                                    |
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Let’s cache for 10 seconds the read only query that is executed on hostgroup 1:

mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern        | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT.*FOR UPDATE$ | 0                    | NULL    | NULL            | 0                     | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     |
| 2       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT              | 0                    | NULL    | NULL            | 1                     | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
2 rows in set (0.00 sec)
 
mysql> update mysql_query_rules set cache_ttl=10000 where rule_id=2;
Query OK, 1 row affected (0.00 sec)
 
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

If I re-execute my Java test application we can see that on the 1000 loop only the first one is executed on my MariaDB backend layer, all the remaining ones are served directly by ProxySQL under the special hostgroup -1:

mysql> select hostgroup,count_star,min_time,max_time,sum_time,digest_text from stats_mysql_query_digest;
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hostgroup | count_star | min_time | max_time | sum_time | digest_text                                                                                                                                                                                                           |
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -1        | 999        | 0        | 0        | 0        | select (select variable_value from information_schema.global_variables where variable_name=?) || ?|| (select variable_value from information_schema.global_variables where variable_name=?) variable_value            |
| 1         | 1001       | 1052     | 4287     | 1780439  | select (select variable_value from information_schema.global_variables where variable_name=?) || ?|| (select variable_value from information_schema.global_variables where variable_name=?) variable_value            |
| 0         | 2000       | 1018     | 2858     | 3236456  | select (select variable_value from information_schema.global_variables where variable_name=?) || ?|| (select variable_value from information_schema.global_variables where variable_name=?) variable_value for update |
| 0         | 2          | 536      | 546      | 1082     | set session autocommit=?                                                                                                                                                                                              |
| 0         | 2          | 1722     | 1775     | 3497     | SHOW VARIABLES WHERE Variable_name in (?, ?, ?, ?)                                                                                                                                                                    |
+-----------+------------+----------+----------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

Query rewrite

Let’s imagine I have my application running a query to get maximum value of a column of below table:

MariaDB [(none)]> show create table replicationdb.test1;
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                  |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `val` int(11) DEFAULT NULL,
  `descr` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf32 |
+-------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
MariaDB [(none)]> explain extended select max(val) from replicationdb.test1;
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|    1 | SIMPLE      | test1 | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

As you know this is going to do a full table scan on replicationdb.test1 table to get this value. Now let’s imagine this table is static and I want to change the query each time it is fired to ProxySQL by something like (I assume I have three rows in my table):

select 3

Let’s add needed rule, as rule 2 will be fired before newly created rule 10 I deactivate it as well. Here the rule you enter is following regexp way of working so almost no limitation in what it is possible to do:

mysql> insert into mysql_query_rules (rule_id,active,match_pattern,replace_pattern,apply) values (10,1,'^select max\(val\) from replicationdb.test1$','select 3',1);
Query OK, 1 row affected (0.00 sec)
 
mysql> update mysql_query_rules set active=0 where rule_id=2;
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------------------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern                                | negate_match_pattern | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | mirror_flagOUT | mirror_hostgroup | error_msg | log | apply |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------------------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
| 1       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT.*FOR UPDATE$                         | 0                    | NULL    | NULL            | 0                     | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     |
| 2       | 0      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT                                      | 0                    | NULL    | NULL            | 1                     | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     |
| 10      | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^select max\(val\) from replicationdb.test1$ | 0                    | NULL    | select 3        | NULL                  | NULL      | NULL      | NULL    | NULL    | NULL  | NULL           | NULL             | NULL      | NULL | 1     |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------------------------------+----------------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+----------------+------------------+-----------+-----+-------+
3 rows in set (0.00 sec)
 
mysql> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

Now I execute the query with:

[mysql@server4 ~]$ /mysql/software/mysql01/bin/mysql --user=test --password=secure_password --host=127.0.0.1 --port=6033 -e "SELECT MAX(val) from replicationdb.test1" --ssl-mode=disabled
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| MAX(val) |
+----------+
|        3 |
+----------+

I get my result, but has it been transformed by ProxySQL ?:

mysql> select hostgroup,count_star,min_time,max_time,sum_time,digest_text from stats_mysql_query_digest;
+-----------+------------+----------+----------+----------+------------------------------------------+
| hostgroup | count_star | min_time | max_time | sum_time | digest_text                              |
+-----------+------------+----------+----------+----------+------------------------------------------+
| 0         | 1          | 3080     | 3080     | 3080     | SELECT MAX(val) from replicationdb.test1 |
| 0         | 1          | 0        | 0        | 0        | select @@version_comment limit ?         |
+-----------+------------+----------+----------+----------+------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 0    |
| 10      | 1    |
+---------+------+
2 rows in set (0.01 sec)

The hits column increases so we can say query rewrite is in action !

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

5 thoughts on “ProxySQL high availability tutorial with MariaDB replication”

  1. VAROQUI Stephane says:

    Excellent writing replication-manager can be used to provide master/slave failover , also i working on feature to enable query rewrite injection from repman with simple cases read-write-splitting and more complex move to shard cluster

  2. AliReza Nasri says:

    Hi
    I have setup mysql replication and proxysql, currently I’m using cpanel, one master and two slaves. I have hosted a domain and want to create a wordpress website but wordpress cannot be installed due to connection error in database, I need your help to find out this problem and appreciate your help.

    • Yannick Jaquier says:

      Hi,

      It’s not really possible to help you remotely without access to your installation. The only advise I can provide, and how I would do it myself, is to do a simple standalone installation of WordPress. Make it working fine, should not be an issue. Then I would add MySQL Replication and finally ProxySQL…

      Thanks,
      Yannick.

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>