Deploy ProxySQL as a service on Kubernetes using Azure Database for MySQL

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

Overview

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

Capture.PNG

 

Prerequisites

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.

 

Procedure

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

  1. Log in to the Azure Database for MySQL Master Server.mysql -hmydemomaster.mysql.database.azure.com -umydemo@mydemomaster -p​
  2. Create a new user ‘mydemouser’ that has the password ‘secretpassword’.CREATE USER 'mydemouser'@'%' IDENTIFIED BY 'secretpassword';
  3. 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;​
  4. Apply the changes to the permissions.FLUSH PRIVILEGES;​

 

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.

 

datadir="/var/lib/proxysql" admin_variables= { admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" refresh_interval=2000 } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:3306;/tmp/proxysql.sock" default_schema="information_schema" stacksize=1048576 server_version="5.1.30" connect_timeout_server=10000 monitor_history=60000 monitor_connect_interval=200000 monitor_ping_interval=200000 ping_interval_server_msec=10000 ping_timeout_server=200 commands_stats=true sessions_sort=true monitor_username="proxysql" monitor_password="proxysqlpassw0rd" } mysql_servers = ( { hostgroup_id=1, hostname="mydemomaster.mysql.database.azure.com", port=3306 , weight=1, comment="write Group", use_ssl=1 }, { hostgroup_id=2, hostname="mydemoreplica.mysql.database.azure.com", port=3306 , weight=1, comment="Read Group", use_ssl=1 } ) mysql_users = ( { username = "mydemouser" , password = "secretpassword" , default_hostgroup = 1 , active = 1 } ) mysql_query_rules = ( { rule_id=1 active=1 match_digest="^SELECT .* FOR UPDATE" destination_hostgroup=1 apply=1 }, { rule_id=2 active=1 match_digest="^SELECT .*" destination_hostgroup=2 apply=1 }, )

 

 

 

  • In the configMap file, modify the following parameters:
    • admin_variables: Modify admin_credentials to update the ProxySQL admin credentials.
    • mysql_servers: Modify this parameter to update Azure Database for MySQL server and read-replica details.
    • mysql_variables: Add or modify the variables. For example, you can modify monitoring user and password.
    • mysql_users: Modify this parameter to update the ProxySQL user. You can connect to the ProxySQL through this user.
    • mysql_query_rules: Modify this parameter to configure the routing rules for read and write split.

 

Prepare ProxySQL pod and service definition

Begin with the sample ProxySQL deployment file below. This template uses the latest ProxySQL docker image. You can update the number of replicas, restart policy and container port for ProxySQL.

 

apiVersion: apps/v1 kind: Deployment metadata: name: proxysql spec: replicas: 1 selector: matchLabels: app: proxysql tier: frontend template: metadata: labels: app: proxysql tier: frontend spec: restartPolicy: Always containers: - name: proxysql image: proxysql/proxysql:latest volumeMounts: - name: "proxysql-config" mountPath: "/etc/proxysql.cnf" subPath: "proxysql.cnf" ports: - containerPort: 3306 name: "proxysql-mysqldb" - containerPort: 6032 name: "proxysql-admin" volumes: - name: "proxysql-config" configMap: name: "proxysql-configmap" --- apiVersion: v1 kind: Service metadata: name: proxysql labels: app: proxysql tier: frontend spec: type: LoadBalancer ports: - port: 3306 name: "proxysql-mysqldb" - port: 6032 name: "proxysql-admin" selector: app: proxysql tier: frontend

 

 

 

 

Deploy the resources

Next, you’ll deploy ProxySQL on AKS.

  1. Configure kubectl to use the credential for the new AKS cluster.az aks get-credentials --name aks-cluster-name --resource-group "mydemo"​
  2. Deploy the configMap.kubectl create configmap proxysql-configmap --from-file=proxysql.cnf​
  3. Deploy ProxySQL using the proxysql deployment file.kubectl apply -f ./proxysqlaks.yml​
  4. Watch the deployment to see whether external links are generated by AKS.kubectl get service proxysql -w​[Use CTRL + C to break out]
  5. After the external IP is generated for ProxySQL service, use the below command to connect to ProxySQL and verify all config.
    1. Use the below command to get the external IP of the proxysql service.kubectl get pods,services -o wide ​
  6. Connect to ProxySQL using this external IPmysql -h[External_IP] -umydemouser -psecretpassword -P3306​

To learn how to scale your WordPress application using ProxySQL deployed as service in AKS to perform transparent read/write split across Azure Database for MySQL replicas, see the blog post scaling your Azure Database for MySQL workload running on Kubernetes with read replicas and ProxySQL.

If you have trouble setting up ProxySQL as a service using Azure Database for MySQL, please contact the Azure Database for MySQL team at AskAzureDBforMySQL@service.microsoft.com.

Thank you!

Amol Bhatnagar

Program Manager - Microsoft

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*

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