Load balance read replicas using ProxySQL in Azure Database for MySQL

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 setup 

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

 

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: 

 

1. Installing ProxySQL on Ubuntu VM.
2. Setting up ProxySQL. 
3. Creating MySQL users on master server.
4. Creating the ProxySQL user. 
5. Configuring monitoring on ProxySQL. 
6. Configuring the routing rules for read and write split. 
7. 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. 

 

1. 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

    
2. 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

    
3. Install mysql-client. 

   sudo apt-get install mysql-client​

    
4. After installing ProxySQL, start the ProxySQL service, as it does not start automatically

   sudo systemctl start proxysql

    
5. Check the status of service.

   systemctl status proxysql 

    After the ProxySQL service successfully starts, a message similar to the following appears: 

 

Setting up ProxySQL 

On the ProxySQL server, perform the following steps:

 

1. Connect to the ProxySQL administration interface with the default password ‘admin’.

   mysql –u admin –padmin -h 127.0.0.1 -P 6032 

    
2. Add the reader and writer nodes ProxySQL server pool. 
   
   Important: ProxySQL currently does not support Private link FQDN privatelink.mysql.database.azure.com, which is a known limitation.
   
   

   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'); 

    
3. 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. 

 

1. Create a new user ‘mydemouser’ with the password ‘secretpassword’

   CREATE USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword'; 

    
2. 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

    
3. Apply the changes to the permissions

   FLUSH PRIVILEGES; 

     

Creating the ProxySQL user 

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);

 

 

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:

 

1. Log into the master server. 
   
   Note: The login syntax varies depending on whether the master server is running:
      - Single Server: Use mysql -uusername@hostname -hhostname -p
      - Flexible Server: Use mysql -uusername -hhostname -p
   
   
2. Create a new user ‘monitoruser’ with the password ‘secretpassword’

   CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'secretpassword'; 

    
3. Grant ‘monitoruser’ privileges to fully access the MySQL server

   GRANT SELECT ON *.* TO ' monitoruser'@'%' WITH GRANT OPTION; 

    
4. Apply the changes to the permissions 

   FLUSH PRIVILEGES; 

    
5. 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 

1. On the ProxySQL 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); 

    
2. 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. 

 

1. 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; 

    
2. 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: 

 

1. Log in to the server running ProxySQL with the ProxySQL user you created.

   mysql –u mydemouser –psecretpassword -h127.0.0.1 -P6033 

    
2. 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: 

 

1. Connect to the ProxySQL administration interface with the default password ‘admin’.

   mysql –u admin –padmin -h127.0.0.1 -P6032 

    
2. 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