In the distributed application, it’s quite common to use load balancers so that your application isn’t dependent on database topologies, to easily load balance the workload, and to do connection failover. ProxySQL, a high-performance MySQL proxy, allows users to send 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 takes an example of Azure Kubernetes Service (AKS) to deploy ProxySQL as a service to split the read and write workloads to Azure Database for MySQL.
Important: Azure Database for MySQL supports ProxySQL version 2.0.6 and later.
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 as a service in AKS using Azure Database for MySQL setup
The basic setup for running ProxySQL as a service in AKS using Azure Database for MySQL is shown in Figure 1.
To prepare for setting up ProxySQL as a service in AKS, you need:
ProxySQL will be configured to direct all the write workload to this server.
Enable “Allow access to Azure services” in Azure Database for MySQL master server. For more information, see Connecting from Azure.
An Azure Database for MySQL server to use as the host group 1 (the Read Replica server). For more information, see Create a replica.
ProxySQL will be configured to direct all the read workload to this server.
Important: Enforcement of SSL connections is enabled by default in Azure Database for MySQL. You should avoid disabling the SSL option and instead configure ProxySQL to use SSL as outlined below.
Setting up ProxySQL as a service in AKS using Azure Database for MySQL involves the following tasks:
Creating MySQL users on the Master Server.
Preparing configMap for ProxySQL.
Preparing the ProxySQL pod and service definition.
Deploying the resources.
Create MySQL users on the Master Server
With 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, you need to create a user on the MySQL database with the same credentials as on the ProxySQL server
Log in to the Azure Database for MySQL Master Server.
mysql -hmydemomaster.mysql.database.azure.com -umydemo@mydemomaster -p
Create a new user ‘mydemouser’ that has the password ‘secretpassword’.
CREATE USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword';
Grant ‘mydemouser’ privileges to fully access the Azure Database for 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.
Prepare configMap for ProxySQL
For the purposes of this post, we use the configMap to deploy the configuration to the ProxySQL container. Begin with the sample ProxySQL configMap file provided below.