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.
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 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
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
After the proxysql successfully starts, a message similar to the following appears:
systemctl status proxysql
Execute the below steps on ProxySQL server:
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
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);
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;
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';
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 –p secretpassword -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 –p admin -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
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.