Overview

ProxySQL, a high-performance MySQL proxy, enables users to distribute different queries to multiple servers to distribute the load more efficiently.

ProxySQL has several benefits, including intelligent load balancing across different databases and the ability to determine if a database instance is running so that read traffic can be redirected accordingly.

This blog post shows how to set up ProxySQL as a load balancer to split the read and write workloads to Azure Database for MySQL.

Note:

ProxySQL is an open source community tool. It is supported by Microsoft on a best effort basis. In order to get production support with authoritative guidance, you can evaluate and reach out to ProxySQL Product support.

ProxySQL on Azure Database for MySQL set up

The below figure shows the basic set up for ProxySQL with Azure Database for MySQL as a master and read replica server.

Figure 1: ProxySQL with Azure Database for MySQL

You can also set up ProxySQL using Azure Resource Manager. To deploy above setup, please refer to https://github.com/Azure/azure-mysql/tree/master/arm-templates/ExampleWithProxySQL

Prerequisites

To prepare for setting up ProxySQL, you need:

A Linux Virtual Machine running Ubuntu , which will be the ProxySQL server. For more information, see the articl e Create a Linux virtual machine in the Azure portal .

An Azure Database for MySQL server to use as h ost group 0 ( the Master Server) . For more information, see the article Create an Azure Database for MySQL server by using the Azure portal . ProxySQL will be configured to direct all the write workload to this server .

Fi rewall rules allow ing the Linux VM to connect to the Azure Database for MySQL master server. For more information, see Create a server-level firewall rule in the Azure portal .

A n Azure Database for MySQL server to use as the host group 1 ( the Read Replica s erver ) . For more information, see Create a replica . ProxySQL will be configured to direct all the read workload to this server.

Fi rewall rules allowing the Linux VM to connect to the Azure Database for MySQL read replica server. For more information , see Create a server-level firewall rule in the Azure portal .

Important: Enforcement of SSL connections is enabled by default on Azure Database for MySQL. We recommend avoiding disabling the SSL option and configure ProxySQL to use SSL as outlined below.

Procedure

The process for setting up ProxySQL as a load balancer in Azure Database for MySQL involves:

Installing ProxySQL on Ubuntu VM. Setting up ProxySQL . Creating MySQL users on master server. Creating the ProxySQL user . Configur ing m onitoring on ProxySQL . Configur ing the routing rules for read and write split . Sav ing the changes to the ProxySQL configuration to persist across restarts .

Install ProxySQL on Ubuntu VM

Important: Azure Database for MySQL supports ProxySQL version2.0.6 and later.

Download the latest version of ProxySQL into the / tmp directory . ProxySQL packages are on ProxySQL GitHub release page . cd /tmp curl -OL https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql_2.0.6-ubuntu18_amd64.deb Install ProxySQL by using dpkg and update the package repository to en sure that you have the latest version build. sudo dpkg -i proxysql_* sudo apt-get update Install mysql -client . sudo apt-get install mysql-client​ After install ing ProxySQL , start the ProxySQL service , as it does not start automatically sudo systemctl start proxysql Check the status of service . systemctl status proxysql After the p roxysql successfully start s , a message similar to the following appears :

Setting up the ProxySQL

Execute the below steps on ProxySQL server:

Connect to the ProxySQL administration interface with the default password ‘admin’ . mysql –u admin –padmin -h 127.0.0.1 -P 6032 Add the reader and writer nodes ProxySQL server pool . insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'mydemomasterserver.mysql.database.azure.com',3306,1,'Write Group'); insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'mydemoreplicaserver.mysql.database.azure.com',3306,1,'Read Group'); Enable SSL support in ProxySQL server pool UPDATE mysql_servers SET use_ssl=1 WHERE hostgroup_id=1; UPDATE mysql_servers SET use_ssl=1 WHERE hostgroup_id=2;

Creating the MySQL users on master server

In ProxySQL, the user connects to ProxySQL and in turn ProxySQL passes the connection to the MySQL node. To allow ProxySQL to access to the MySQL database, we need to create a user on MySQL database with the same credentials as on the ProxySQL server.

Create a new user ‘ mydemouser ’ with the password ‘ secretpassword ’ CREATE USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword'; Grant ‘ mydemouser ’ privileges to fully access the MySQL server. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'mydemouser'@'%' WITH GRANT OPTION Apply the changes to the permissions FLUSH PRIVILEGES;

Creating the ProxySQL user

Allow the ‘mydemouser’ user to connect to ProxySQL server.

Execute the below query on ProxySQL server:

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('mydemouser','secretpassword',1,1);

Configure Monitoring on ProxySQL

Create the monitoring user on the Master server and then configure ProxySQL to monitor the nodes.

Create monitoring user on the master server:

Create a new user ‘ monito r user ’ with the password ‘ secretpassword ’ CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'secretpassword'; Grant ‘ monitoruser ’ privileges to fully access the MySQL server GRANT SELECT ON *.* TO ' monitoruser'@'%' WITH GRANT OPTION; Apply the changes to the permissions FLUSH PRIVILEGES;

On the server running ProxySQL, configure mysql-monitor to the username of the new account.

set mysql-monitor_username='monitoruser'; set mysql-monitor_password='secretpassword';

Configure the routing rules for read and write split

On the P roxy SQL Server , configure the write traffic to route to the master server insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1); On the ProxySQL Server, configure the read traffic to route to the read replica server insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);

Save the changes made to the ProxySQL configuration to persists across restarts

In ProxySQL configuration system, the changes we made are in memory and to make them persist across the restarts, you must copy settings to runtime and save them to disk.

On the server running ProxySQL , e xecute the below commands to save the settings to runtime : 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; On the server running ProxySQL , e xecute the following commands to save the settings to disk: 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;

After successfully completing the above steps, ProxySQL is configured and is ready to split the read and write workload. To test the functionality to determine if the read and write splits are being forwarded properly:

Log in to the server running ProxySQL with the ProxySQ L user you created . mysql –u mydemouser –p secretpassword -h127.0.0.1 -P6033 Run the read and write queries SELECT * FROM mydemotable; UPDATE mydemotable SET mydemocolumn=value WHERE condition;

To verify that ProxySQL has routed the above read and write correctly:

Connect to the ProxySQL administration interface with the default password ‘admin’ . mysql –u admin –p admin -h127.0.0.1 -P6032 Execute the following query : SELECT * FROM stats_mysql_query_digest;

If you have trouble setting up ProxySQL on Azure Database for MySQL, please contact the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com

Thank you!

Amol Bhatnagar

Program Manager - Microsoft