Scaling an Azure Database for MySQL workload running on Kubernetes with read replicas and ProxySQL

Introduction

Imagine that you are designing and developing a cloud-native application on Kubernetes to allow your application to scale seamlessly as and when needed. You decide to host your MySQL database for the application on Azure Database for MySQL, which is a fully managed service that ensures your backups and addresses availability and security.With Kubernetes, you can easily scale out the application layer by simply adding nodes to the Kubernetes Cluster or by scaling individual application pods, which are stateless in nature. However, when scaling up a MySQL database, you may be constrained by the maximum limits supported by a single node of Azure Database for MySQL. In this scenario, MySQL replication with ProxySQL load balancer proxy can provide a more scalable design from database perspective.

You can easily scale out Azure Database for MySQL using read replicas, and even further using a load balancer proxy to decouple and abstract your application from the complexity of database replication. In the distributed application, it’s quite common to use load balancers so that your application is not dependent on database topologies, to easily load balance the workload, and to do connection failover. ProxySQL, a high-performance MySQL proxy, enables 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.

In this blog post, we use WordPress as one of the canonical sample applications for MySQL. This application needs to be modernized to a scalable cloud native design architecture to cater to millions of mobile and internet device users. The following section describes how to scale your WordPress application using ProxySQL deployed as service in Azure Kubernetes Service (AKS) to perform transparent read/write split across Azure Database for MySQL replicas.

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 set up for running ProxySQL as a service in AKS using Azure Database for MySQL is shown in Figure 1.

Procedure

Setting up ProxySQL as a service in AKS using Azure Database for MySQL involves the following tasks:

1. Deploy ProxySQL as a service.
2. Prepare the WordPress deployment.
3. Deploy WordPress.

Deploy ProxySQL as a service

To deploy ProxySQL as a service, refer to deploy ProxySQL as a service on Kubernetes using Azure Database for MySQL.

Prepare the WordPress deployment

You can use the sample WordPress deployment file below. Update the WordPress config details in the WordPress environment variables and the ProxySQL service details from the ProxySQL deployment you just performed.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: wordpress
  labels:
    app: wordpress
spec:
  replicas: 1
  selector:
    matchLabels:
      app: wordpress
      tier: frontend
  strategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: wordpress
        tier: frontend
    spec:
      restartPolicy: Always
      containers:
      - image: wordpress:latest
        name: wordpress
        env:
        - name: WORDPRESS_DB_HOST
          value: proxysql
        - name: WORDPRESS_DB_USER
          value: mydemouser
        - name: WORDPRESS_DB_DATABASE
          value: wordpress
        - name: WORDPRESS_DB_PASSWORD
          value: "secretpassword"
        ports:
        - containerPort: 80
          name: wordpress
---
apiVersion: v1
kind: Service
metadata:
  name: wordpress
  labels:
    app: wordpress
    tier: frontend
spec:
  type: LoadBalancer
  ports:
  - name: wordpress
    nodePort: 30088
    port: 80
  selector:
    app: wordpress
    tier: frontend

Deploy the resources

Next, deploy WordPress on AKS.

1. Configure kubectl to use the credential for the AKS cluster created as part of deploying ProxySQL as a service.

   az aks get-credentials --name aks-cluster-name --resource-group "mydemo"​

2. Deploy WordPress using the wordpress deployment file.

   kubectl create -f .\wordpress.yml​

3. After the deployment finishes, can go to WordPress site by visiting the external IP. To get the external IPs, run the following command:

   kubectl getpods,services -o wide   ​

    

To learn about how to load balance read replicas using ProxySQL in Azure Database for MySQL, see the post here.

If you have trouble setting up ProxySQL as a service in AKS using Azure Database for MySQL, please contact the Azure Database for MySQL team at [email protected]

Thank you!

Amol Bhatnagar

Program Manager - Microsoft