cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Deploy ProxySQL as a service on Kubernetes using Azure Database for MySQL
By
ambhatna
Published Jan 13 2020 12:00 PM 13.5K Views
ambhatna
Microsoft
‎Jan 13 2020 12:00 PM

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

‎Jan 13 2020 12:00 PM

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.

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 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"
}
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="proxysqlsecretpassword"
}
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 IP
    mysql -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

 

2 Comments
Version history
Last update:
‎Jun 22 2020 06:21 PM
Updated by:
Labels