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.
The below figure shows the basic setup 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
To prepare for setting up ProxySQL, you need:
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.
The process for setting up ProxySQL as a load balancer in Azure Database for MySQL involves:
Important: Azure Database for MySQL supports ProxySQL version2.0.6 and later.
cd /tmp
curl -OL https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql_2.0.6-ubuntu18_amd64.deb
sudo dpkg -i proxysql_*
sudo apt-get update
sudo apt-get install mysql-client
sudo systemctl start proxysql
systemctl status proxysql
After the ProxySQL service successfully starts, a message similar to the following appears:
On the ProxySQL server, perform the following steps:
mysql –u admin –padmin -h 127.0.0.1 -P 6032
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');
UPDATE mysql_servers SET use_ssl=1 WHERE hostgroup_id=1;
UPDATE mysql_servers SET use_ssl=1 WHERE hostgroup_id=2;
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 USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword';
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
FLUSH PRIVILEGES;
Allow the ‘mydemouser’ user to connect to ProxySQL server.
On the ProxySQL server, run the following command:
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('mydemouser','secretpassword',1,1);
Create the monitoring user on the master server and then configure ProxySQL to monitor the nodes.
Create monitoring user on the master server:
CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'secretpassword';
GRANT SELECT ON *.* TO ' monitoruser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
set mysql-monitor_username='monitoruser';
set mysql-monitor_password='secretpassword';
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
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.
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;
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:
mysql –u mydemouser –psecretpassword -h127.0.0.1 -P6033
SELECT *
FROM mydemotable;
UPDATE mydemotable
SET mydemocolumn=value
WHERE condition;
To verify that ProxySQL has routed the above read and write correctly:
mysql –u admin –padmin -h127.0.0.1 -P6032
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.