Load balance read replicas using ProxySQL in Azure Database for MySQL

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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.

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

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 article Create a Linux virtual machine in the Azure portal.
An Azure Database for MySQL server to use as host 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.

Firewall rules allowing 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.
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.

Firewall 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.
Configuring monitoring on ProxySQL.
Configuring the routing rules for read and write split.
Saving 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 ensure 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 installing ProxySQL, start the ProxySQL service, as it does not start automaticallysudo systemctl start proxysql
Check the status of service.systemctl status proxysql After the proxysql successfully starts, 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 –p admin -h127.0.0.1 -P6032
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 poolUPDATE 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 ALL PRIVILEGES ON *.* TO ' mydemouser'@'%' WITH GRANT OPTION;
Apply the changes to the permissionsFLUSH 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 ‘monitoruser’ with the password ‘secretpassword’CREATE USER ' monitoruser'@'%' IDENTIFIED BY 'secretpassword';
Grant ‘monitoruser’ privileges to fully access the MySQL serverGRANT 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 ProxySQL Server, configure the write traffic to route to the master serverinsert 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 serverinsert 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, execute 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, execute 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 ProxySQL user you created.mysql –u mydemouser –p secretpassword -h127.0.0.1 -P6033
Run the read and write queriesSELECT * 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