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 

ProxySQLhigh-performance MySQL proxy, enables users to distribute different queries to multiple servers to distribute the load more efficiently. 

ProxySQL has several benefitsincluding 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. 

Capture.PNG

Figure 1: ProxySQL with Azure Database for MySQL 

Prerequisites

To prepare for setting up ProxySQL, you need: 

 

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

ImportantEnforcement 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

ImportantAzure 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 ProxySQLstart the ProxySQL service, as it does not start automaticallysudo systemctl start proxysql 
  5. Check the status of service.systemctl status proxysql  After the proxysql successfully startsmessage similar to the following appears: 

Capture1.PNG

Setting up the ProxySQL

Execute the below steps on ProxySQL server:

 

  1. Connect to the ProxySQL administration interface with the default password ‘admin’.mysql –u admin –p admin -h127.0.0.1 -P6032  
  2. 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');  
  3. 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. 

 

  1. Create a new user ‘mydemouser’ with the password ‘secretpasswordCREATE USER 'mydemouser'@'%' IDENTIFIED BY ' secretpassword';  
  2. Grant ‘mydemouser’ privileges to fully access the MySQL server.GRANT ALL PRIVILEGES ON *.* TO ' mydemouser'@'%' WITH GRANT OPTION;  
  3. 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:

 

  1. Create a new user ‘monitoruser with the password ‘secretpasswordCREATE USER ' monitoruser'@'%' IDENTIFIED BY 'secretpassword';  
  2. Grant ‘monitoruser’ privileges to fully access the MySQL serverGRANT SELECT ON *.* TO ' monitoruser'@'%' WITH GRANT OPTION;  
  3. 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 

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

 

  1. On the server running ProxySQLexecute 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 ProxySQLexecute 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 stepsProxySQL 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 –p secretpassword -h127.0.0.1 -P6033  
  2. 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: 

 

  1. Connect to the ProxySQL administration interface with the default password ‘admin’.mysql –u admin –p admin -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

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.