Skip to content

In the previous post I explained how ProxySQL works with Azure Database for MySQL.

To install it :

 apt-get install -y lsb-release
 wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
 echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ \
    | tee /etc/apt/sources.list.d/proxysql.list
 apt-get update
 apt-get install proxysql
 sudo service proxysql start

If you want you can build it from source. To make a usable package you just need to have docker available. The build process through the Makefile trigger a docker container which already has all the required dependencies for building installed. For example to make a package for ubuntu 18, to install it and to run it :

$ git clone https://github.com/sysown/proxysql.git
$ cd  proxysql
$ make ubuntu18
$ dpkg -i binaries/proxysql_2.0.13-ubuntu18_amd64.deb
$ sudo service proxysql start

We now have a running proxysql. Let us use it. We first create a master and 2 replicas Azure Database for MySQL. We connect to the master and create 2 users : 'sbtest' for injecting traffic and 'monitoruser' required by proxysql to monitor the backend servers :

mysql -h mysqlpaasmaster.mysql.database.azure.com -u administrator@mysqlpaasmaster -p \
  --ssl-mode=REQUIRED
 
CREATE SCHEMA sbtest;
CREATE USER sbtest@'%' IDENTIFIED BY 'Passw0rd';
GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@'%';
 
CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'Passw0rd'; 
GRANT SELECT ON *.* TO 'monitoruser'@'%' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

Now we configure proxysql through the proxysql admin. At initial startup proxysql reads its configuration from /etc/proxysql.cnf. This is where the admin user credentials are defined :

        admin_credentials="proxysqladmin:Passw0rd"
        mysql_ifaces="0.0.0.0:6032"

All the rest of the configuration can be done in a scripted way that will be persisted to disk in a SQLite database.

mysql -h 127.0.0.1  -u proxysqladmin -pPassw0rd -P6032 --ssl
 
set mysql-monitor_username='monitoruser'; 
set mysql-monitor_password='Passw0rd';

Let us definine the servers

 
insert into mysql_servers(hostgroup_id,hostname,port,weight,use_ssl, comment) 
  values(10,'mysqlpaasmaster.mysql.database.azure.com',3306,1,1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,use_ssl, comment) 
  values(20,'mysqlpaasreplica1.mysql.database.azure.com',3306,1,1,'Read Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,use_ssl, comment) 
  values(20,'mysqlpaasreplica2.mysql.database.azure.com',3306,1,1,'Read Group');

We then define the 'sbtest' proxysql user. The 'sbtest' user has for default host group 10 which is the master server. That means that all queries for which no routing rules applies will end there.

insert into mysql_users(username,password,default_hostgroup,transaction_persistent) 
  values('sbtest','Passw0rd',10,1);

No we need to define the query routing rules that will determine to which host groups and consequently backends the queries will be routed. For Read/Write splitting that is quite simple : SELECT FOR UPDATE to 'Write group, SELECT to 'Read group' and all the rest to the default group of the user. So this means everything to 'Write group' except pure SELECT.

insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) 
  values(1,1,'^SELECT.*FOR UPDATE$',10,1); 
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) 
  values(2,1,'^SELECT',20,1);

Our setup for proxysql is in memory and need to be pushed to runtime an disk.

load mysql users to runtime; 
load mysql servers to runtime; 
load mysql query rules to runtime; 
load mysql variables to runtime; 
load admin variables to runtime; 
 
save mysql users to disk; 
save mysql servers to disk; 
save mysql query rules to disk; 
save mysql variables to disk; 
save admin variables to disk;

To test our configuration we need to inject traffic. We will use sysbench for that :

  sysbench --threads=4 '/usr/share/sysbench/oltp_read_write.lua' \
            --db-driver=mysql --time=20 \
            --mysql-host='127.0.0.1' --mysql-port=3306 \
            --mysql-user=sbtest --mysql-password=Passw0rd \
            --tables=5 --tables=10000 \
            prepare
 
  sysbench --threads=4 '/usr/share/sysbench/oltp_read_write.lua' \
            --db-driver=mysql --time=20 \
            --mysql-host='127.0.0.1' --mysql-port=3306 \
            --mysql-user=sbtest --mysql-password=Passw0rd \
            --tables=5 --tables=10000 \
            run

We see that master and replicas have received their share of sysbench queries.

MySQL > SELECT hostgroup, srv_host,Queries FROM stats_mysql_connection_pool;
+-----------+--------------------------------------------+---------+
| hostgroup | srv_host                                   | Queries |
+-----------+--------------------------------------------+---------+
| 10        | mysqlpaasmaster.mysql.database.azure.com   | 472     |
| 20        | mysqlpaasreplica1.mysql.database.azure.com | 415     |
| 20        | mysqlpaasmaster.mysql.database.azure.com   | 402     |
| 20        | mysqlpaasreplica2.mysql.database.azure.com | 422     |
+-----------+--------------------------------------------+---------+
4 ROWS IN SET (0.00 sec).

We also get the digest of all the queries run and on which hostgroup they ran. We can see here that all INSERT, UPDATE,DELE were sent to the Write hostgroup and the SELECT to the Read hostgroup.

SELECT hostgroup, username, digest_text FROM  stats_mysql_query_digest;
+-----------+----------+-------------------------------------------------------------+
| hostgroup | username | digest_text                                                 |
+-----------+----------+-------------------------------------------------------------+
| 10        | sbtest   | INSERT INTO sbtest5 (id, k, c, pad) VALUES (?, ?, ?, ?)     |
| 10        | sbtest   | DELETE FROM sbtest2 WHERE id=?                              |
| 10        | sbtest   | UPDATE sbtest2 SET c=? WHERE id=?                           |
| 20        | sbtest   | SELECT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c   |
| 20        | sbtest   | SELECT SUM(k) FROM sbtest4 WHERE id BETWEEN ? AND ?         |
...

I hope this helped.

Azure Database for MySQL is a PaaS offer. It has a specific architecture that relies on a gateway. This has a huge advantage in the way it handle High availability. If a server fails it will automatically restart. The storage for the database is highly resilient and will be reconnected to the new server. You get HA out of the box without having to care about replica and failover handling.

if we look at a connection to a Azure Database for MySQL it is different from a usual MySQL connection.

mysql -h mysqlpaasmaster.mysql.database.azure.com \
  -u sbtest@mysqlpaasmaster -p \
  --ssl-mode=REQUIRED

we notice :
hostname : mysqlpaasmaster.mysql.database.azure.com
username : sbtest@mysqlpaasmaster

Why do we have the instance name in the username ?
If we look at what the host name is, using the unix host command (dig would also do the trick).

$ host mysqlpaasmaster.mysql.database.azure.com
mysqlpaasmaster.mysql.database.azure.com is an alias for cr5.northeurope1-a.control.database.windows.net.
cr5.northeurope1-a.control.database.windows.net has address 52.138.224.6

The host name is just an alias to a gateway server (it is not an A record in the DNS). So the host you connect to is specific to the database's region but carry no information about the mysql instance you connect to. This explains why when you connect you need to embed the database name into the user name. This is the only way for the gateway to know which instance you want to connect to.

Does this fit with proxySQL ? We might think No. But in fact it works perfectly with ProxySQL. ProxySQL which knows the backends hostnames is able to inject this hostname in the MySQL protocol when talking to the azure gateway. This is possible thanks to the fact that ProxySQL uses MariaDB Connector C to communicate with the gateway. With MariaDB Connector C it is possible in the connection options to add '_server_host' which contains the hostname information. In this way the gateway knows what instance is referenced without having it in the username.

ProxySQL is a fantastic technology widely used on MySQL / MariaDB architectures on premise or in the cloud. It has a nice design with the concept of host groups and query rules used to route queries to the desired backend server (based on port or regex).

To achieve this routing proxySQL uses a set of users that will potentially connect to multiple backends depending on the status of these backends and the routing query rules. This is also the same for the monitoring user that is common to all the backends.

So now to test this how this works with Azure Database for MySQL I have setup a Master with 2 replicas (that can be geo replica in another region if you wish). I have created a single user 'sbtest' in proxySQL. On this setup I run a simple sysbench to inject traffic. I use the oltp_read_write.lua script to generate insert, update, delete and select to validate that the read write splitting is working correctly. And it works like a charm 🙂

Here are the host groups, 10 for writes and 20 for reads. Hostgroup 20 contains the 2 replicas plus the master that can also be used for reads(if you want it to focus on write you can put a low weight). Hostgroup 10 contains only the master :

MySQL > SELECT hostgroup_id,hostname,STATUS,comment,use_ssl FROM mysql_servers;
+--------------+--------------------------------------------+--------+-------------+---+
| hostgroup_id | hostname                                   | STATUS | comment     |use_ssl
+--------------+-----------------,---------------------------+--------+-------------+----
| 10           | mysqlpaasmaster.mysql.database.azure.com   | ONLINE | WRITE GROUP | 1 |
| 20           | mysqlpaasreplica1.mysql.database.azure.com | ONLINE | READ GROUP  | 1 |
| 20           | mysqlpaasreplica2.mysql.database.azure.com | ONLINE | READ GROUP  | 1 |
| 20           | mysqlpaasmaster.mysql.database.azure.com   | ONLINE | WRITE GROUP | 1 |
+--------------+--------------------------------------------+--------+-------------+---+
4 ROWS IN SET (0.00 sec)

Here is the single user used for all the backends.

MySQL > SELECT username,password,active,use_ssl,default_hostgroup FROM mysql_users;
+----------+----------+--------+---------+-------------------+
| username | password | active | use_ssl | default_hostgroup |
+----------+----------+--------+---------+-------------------+
| sbtest   | password | 1      | 0       | 10                | 
+----------+----------+--------+---------+-------------------+
1 ROW IN SET (0.00 sec)

And here are the query rules to route the queries to the right backend.

MySQL >  SELECT rule_id,match_digest,destination_hostgroup FROM mysql_query_rules;
+---------+-----------------------+-----------------------+
| rule_id | match_digest          | destination_hostgroup |
+---------+-----------------------+-----------------------+
| 1       | ^SELECT .* FOR UPDATE | 10                    |
| 2       | ^SELECT .*            | 20                    |
+---------+-----------------------+-----------------------+
2 ROWS IN SET (0.00 sec)

Metrics data has also been collected inside the stats schema. We see that master and replicas have received their share of sysbench queries.

MySQL > SELECT hostgroup, srv_host,Queries FROM stats_mysql_connection_pool;
+-----------+--------------------------------------------+---------+
| hostgroup | srv_host                                   | Queries |
+-----------+--------------------------------------------+---------+
| 10        | mysqlpaasmaster.mysql.database.azure.com   | 472     |
| 20        | mysqlpaasreplica1.mysql.database.azure.com | 415     |
| 20        | mysqlpaasmaster.mysql.database.azure.com   | 402     |
| 20        | mysqlpaasreplica2.mysql.database.azure.com | 422     |
+-----------+--------------------------------------------+---------+
4 ROWS IN SET (0.00 sec).

Through the stats we also get the digest of all the queries run and on wich hostgroup they ran. We can see here that all INSERT, UPDATE,DELE were sent to the Write hostgroup and the SELECT to the Read hostgroup.

SELECT hostgroup, username, digest_text FROM  stats_mysql_query_digest;
+-----------+----------+-------------------------------------------------------------+
| hostgroup | username | digest_text                                                 |
+-----------+----------+-------------------------------------------------------------+
| 10        | sbtest   | INSERT INTO sbtest5 (id, k, c, pad) VALUES (?, ?, ?, ?)     |
| 10        | sbtest   | DELETE FROM sbtest2 WHERE id=?                              |
| 10        | sbtest   | UPDATE sbtest2 SET c=? WHERE id=?                           |
| 20        | sbtest   | SELECT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c   |
| 20        | sbtest   | SELECT SUM(k) FROM sbtest4 WHERE id BETWEEN ? AND ?         |
...

In the monitor schema we will find data that has been collected by the 'monitoruser'. In the monitor schema we can find log data for connect, ping, read_only ... Here for example the ping data to check the availability of the backends :

MySQL > SELECT hostname FROM mysql_server_ping_log;
+--------------------------------------------+------+------------------+------------+
| hostname                                   | port | time_start_us    | ping_success_time_us |
+--------------------------------------------+------+------------------+------------+
| mysqlpaasreplica1.mysql.database.azure.com | 3306 | 1591785759257052 | 20088      |
| mysqlpaasreplica2.mysql.database.azure.com | 3306 | 1591785759269801 | 19948      |
| mysqlpaasmaster.mysql.database.azure.com   | 3306 | 1591785759282430 | 19831      |

I hope this helped you understand how ProxySQL work with Azure Database for MySQL/MariaDB.
The use of proxySQL on Azure with Azure Database for MySQL/MariaDB definitely brings a lot of value.