MariaDB Galera with ProxySQL Deployment on Kubernetes

In this documentation we are going to deploy MariaDB Galera cluster along with ProxySQL on Kubernetes.

https://sesamedisk.com/mysql-cluster-deploy-galera-with-mariadb-proxysql/

Let us pull the helm chart from Bitnami:

helm pull oci://registry-1.docker.io/bitnamicharts/mariadb-galera

In values.yaml file we can change some values according to our needs. For example if we want to specify a storageClass we can enter it in global.storageClass.

global:
  storageClass: "zfs-hdd"

We can also change PVC (Persistent Volume Claim) in persistence.size.

persistence:
  size: 8Gi

Additionally we can specify resources in resources.limits and resources.requests.

resources:
  limits:
    cpu: 0.5
    memory: 1Gi
  requests:
    cpu: 0.5
    memory: 1Gi

Finally in values.yaml we can write username and password in rootUser.user and rootUser.password.

rootUser:
  user: admin
  password: "some_password"

After doing modifications, We can install the chart with helm install command in mariadb-galera.

helm install mariadb-galera ./mariadb-galera/ -f ./mariadb-galera/values.yaml -n mariadb-galera

We can make sure the installation was successful using kubectl get all command.

kubectl get all -n mariadb-galera

NAME                   READY   STATUS    RESTARTS   AGE
pod/mariadb-galera-0   1/1     Running   0          84m
pod/mariadb-galera-1   1/1     Running   0          83m
pod/mariadb-galera-2   1/1     Running   0          83m

NAME                              TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)                         AGE
service/mariadb-galera            ClusterIP   10.43.96.137    <none>        3306/TCP                        84m
service/mariadb-galera-headless   ClusterIP   None            <none>        4567/TCP,4568/TCP,4444/TCP      84m

NAME                              READY   AGE
statefulset.apps/mariadb-galera   3/3     84m

Now we need to create two users. One for cluster monitoring called monitoruser and the other for Database user called wordpress for example. To do this we should port-forward the service to be able to access it via mysql client.

kubectl port-forward service/mariadb-galera 3306 -n mariadb-galera

Forwarding from 127.0.0.1:3306 -> 3306
Forwarding from [::1]:3306 -> 3306

Then we will be able to access the cluster via mysql command.

mysql -h127.0.0.1 -P3306 -uadmin -psome_password --prompt "ProxySQL Admin>"

mysql> CREATE USER 'monitoruser'@'%' IDENTIFIED BY 'MonPass123^';
mysql> GRANT REPLICATION CLIENT ON *.* TO 'monitoruser'@'%';

mysql> CREATE USER 'wordpress'@'%' IDENTIFIED BY 'WPPass123^';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON wordpress.* TO 'wordpress'@'%';

At this point the MariaDB Galera cluster is ready. Now let’s install and configure ProxySQL.

Since ProxySQL’s official helm chart is somehow old (last commit in 2021), We are going to use the guidance here by Percona.

We first need to create the proxysql.cnf file as a Configmap to feed to ProxySQL cluster.

datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin;cluster:secret"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    cluster_username="cluster"
    cluster_password="secret"  
}

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:6033;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0.23"
    connect_timeout_server=3000
    monitor_username="monitoruser"
    monitor_password="MonPass123^"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

mysql_servers =
(
    { address="mariadb-galera-0.mariadb-galera-headless.mariadb-galera.svc" , port=3306 , hostgroup=10, max_connections=100 },
    { address="mariadb-galera-1.mariadb-galera-headless.mariadb-galera.svc" , port=3306 , hostgroup=20, max_connections=100 },
    { address="mariadb-galera-2.mariadb-galera-headless.mariadb-galera.svc" , port=3306 , hostgroup=20, max_connections=100 }
)

mysql_users =
(
    { username = "wordpress", password = "WPPass123^", default_hostgroup = 10, active = 1 }
)

mysql_replication_hostgroups=({
writer_hostgroup=10
reader_hostgroup=20
check_type="innodb_read_only"
comment="Some Comments for example Wordpress"
})

The things that are worth telling about above configuration are the following:

1. The admin_variables.admin_credentials value contains the username and password you need to use when you try to connect to 6032 via mysql command.
2. The mysql_variables.monitor_username and mysql_variables.monitor_password should get the credentials that we created for monitoring user.
3. mysql_servers must contain each MariaDB Galera pod address, port, max_connections, as well as hostgroup. Hostgroups are used when you try to separate read pods from write pods.
4. mysql_users contain the credentials of the wordpress user we created as well as default_hostgroup and whether it is active or not.
5. Finally mysql_replication_hostgroups specifies which hostgroups should act for read and which hostgroups should act as write.

We can create the Configmap from the above config file with the name proxysql-configmap.

kubectl create configmap proxysql-configmap --from-file=proxysql.cnf -n mariadb-galera

Next we need to create the ProxySQL Statefulset, Service, and proxysqlcluster service.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: proxysql
  labels:
    app: proxysql
spec:
  replicas: 3
  serviceName: proxysqlcluster
  selector:
    matchLabels:
      app: proxysql
  updateStrategy:
    type: RollingUpdate
  template:
    metadata:
      labels:
        app: proxysql
    spec:
      restartPolicy: Always
      containers:
        - image: proxysql/proxysql:2.5.2
          name: proxysql
          volumeMounts:
            - name: proxysql-config
              mountPath: /etc/proxysql.cnf
              subPath: proxysql.cnf
            - name: proxysql-data
              mountPath: /var/lib/proxysql
              subPath: data
          ports:
            - containerPort: 6033
              name: proxysql-mysql
            - containerPort: 6032
              name: proxysql-admin
      volumes:
        - name: proxysql-config
          configMap:
            name: proxysql-configmap
  volumeClaimTemplates:
    - metadata:
        name: proxysql-data
      spec:
        accessModes: ["ReadWriteOnce"]
        resources:
          requests:
            storage: 2Gi
---
apiVersion: v1
kind: Service
metadata:
  # annotations:
  labels:
    app: proxysql
  name: proxysql
spec:
  ports:
    - name: proxysql-mysql
      nodePort: 30033
      port: 6033
      protocol: TCP
      targetPort: 6033
    - name: proxysql-admin
      nodePort: 30032
      port: 6032
      protocol: TCP
      targetPort: 6032
  selector:
    app: proxysql
  type: NodePort
---
apiVersion: v1
kind: Service
metadata:
  name: proxysqlcluster
  labels:
    app: proxysql
spec:
  clusterIP: None
  ports:
    - port: 6032
      name: proxysql-admin
  selector:
    app: proxysql

We can then apply the Yaml file.

kubectl apply -f proxysql.yaml -n mariadb-galera

We can then check logs to see whether we have errors or not.

kubectl logs pod/proxysql-0 -n mariadb-galera

If no error exists then we can test the connection of the two ports we exposed in the yaml file (6032 and 6033).

For 6032 port use admin credentials we specify in proxysql.cnf file.

kubectl port-forward service/proxysql 6032:6032 -n mariadb-galera

Forwarding from 127.0.0.1:6032 -> 6032
Forwarding from [::1]:6032 -> 6032

mysql -h127.0.0.1 -P6032 -uadmin -padmin --prompt "ProxySQL Admin>"

For 6033 port use wordpress credentials of the user we created.

kubectl port-forward service/proxysql 6033:6033 -n mariadb-galera

Forwarding from 127.0.0.1:6033 -> 6033
Forwarding from [::1]:6033 -> 6033

mysql -h127.0.0.1 -P6033 -uwordpress -pWPPass123^ --prompt "ProxySQL Admin>"

By being able to get authenticated and get shell from the above commands we can make sure our deployment is complete.