You are looking at the documentation of a prior release. To read the documentation of the latest release, please visit here.

New to KubeDB? Please start here.

KubeDB - MySQL Remote Replica

This tutorial will show you how to use KubeDB to provision a MySQL Remote Replica from a kubedb managed mysql instance. Remote replica can used in in or across cluster

Before You Begin

Before proceeding:

Read mysql replication concept to learn about MySQL Replication.
You need to have a Kubernetes cluster, and the kubectl command-line tool must be configured to communicate with your cluster. If you do not already have a cluster, you can create one by using kind.
Now, install KubeDB cli on your workstation and KubeDB operator in your cluster following the steps here.
To keep things isolated, this tutorial uses a separate namespace called demo throughout this tutorial. Run the following command to prepare your cluster for this tutorial:

  $ kubectl create ns demo
  namespace/demo created

Note: The yaml files used in this tutorial are stored in docs/guides/mysql/clustering/remote-replica/yamls folder in GitHub repository kubedb/docs.

Remote Replica

The remote replica allows you to replicate data from an KubeDB managed MySQL server to a read-only mysql server. The whole process uses MySQL asynchronous replication to keep up-to-date the replica with source server. It’s useful to use remote replica to scale of read-intensive workloads, can be a workaround for your BI and analytical workloads and can be geo-replicated.

Deploy Mysql server

The following is an example MySQL object which creates a MySQL Group replicated instance.we will create a tls secure instance since were planing to replicated across cluster

Lets start with creating a secret first to access to database and we will deploy a tls secured instance since were replication across cluster

Create Issuer/ClusterIssuer

Now, we are going to create an example Issuer that will be used throughout the duration of this tutorial. Alternatively, you can follow this cert-manager tutorial to create your own Issuer. By following the below steps, we are going to create our desired issuer,

Start off by generating our ca-certificates using openssl,

openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout ./ca.key -out ./ca.crt -subj "/CN=mysql/O=kubedb"

create a secret using the certificate files we have just generated,

kubectl create secret tls my-ca \
     --cert=ca.crt \
     --key=ca.key \
     --namespace=demo
secret/my-ca created

Now, we are going to create an Issuer using the my-ca secret that hols the ca-certificate we have just created. Below is the YAML of the Issuer cr that we are going to create,

apiVersion: cert-manager.io/v1
kind: Issuer
metadata:
  name: mysql-issuer
  namespace: demo
spec:
  ca:
    secretName: my-ca

Let’s create the Issuer cr we have shown above,

kubectl apply -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/issuer.yaml
issuer.cert-manager.io/mysql-issuer created

Create Auth Secret

apiVersion: v1
data:
  password: cGFzcw==
  username: cm9vdA==
kind: Secret
metadata:
  name: mysql-singapore-auth
  namespace: demo
type: kubernetes.io/basic-auth

$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/mysql-singapore-auth.yaml
secret/mysql-singapore-auth created

Deploy MySQL with TLS/SSL configuration

apiVersion: kubedb.com/v1alpha2
kind: MySQL
metadata:
  name: mysql-singapore
  namespace: demo
spec:
  authSecret:
    name: mysql-singapore-auth
  version: "8.0.35"
  replicas: 3
  topology:
    mode: GroupReplication
  storageType: Durable
  storage:
    storageClassName: "linode-block-storage"
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 10Gi
  requireSSL: true
  tls:
    issuerRef:
      apiGroup: cert-manager.io
      kind: Issuer
      name: mysql-issuer
    certificates:
      - alias: server
        subject:
          organizations:
            - kubedb:server
        dnsNames:
          - localhost
        ipAddresses:
          - "127.0.0.1"
  terminationPolicy: WipeOut

$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/mysql-singapore.yaml
mysql.kubedb.com/mysql created

KubeDB operator sets the status.phase to Ready once the database is successfully created

$ kubectl get mysql -n demo
NAME              VERSION   STATUS   AGE
mysql-singapore   8.0.35    Ready    22h

Connect with MySQL database

Now, you can connect to this database from your terminal using the mysql user and password.

$ kubectl get secrets -n demo mysql-singapore-auth -o jsonpath='{.data.\username}' | base64 -d
root

$ kubectl get secrets -n demo mysql-singapore-auth -o jsonpath='{.data.\password}' | base64 -d
pass

The operator creates a standalone mysql server for the newly created MySQL object.

Now you can connect to the database using the above info. Ignore the warning message. It is happening for using password in the command.

Data Insertion

Let’s insert some data to the newly created mysql server . we can use the primary service or governing service to connect with the database

Read the comment written for the following commands. They contain the instructions and explanations of the commands.

# create a database on primary
$ kubectl exec -it -n demo mysql-singapore-0 -- mysql -u root --password='pass' --host=mysql-singapore-0.mysql-singapore-pods.demo -e "CREATE DATABASE playground;"
mysql: [Warning] Using a password on the command line interface can be insecure.

# create a table
$ kubectl exec -it -n demo mysql-singapore-0 -- mysql -u root --password='pass' --host=mysql-singapore-0.mysql-singapore-pods.demo -e "CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));"
mysql: [Warning] Using a password on the command line interface can be insecure.


# insert a row
$  kubectl exec -it -n demo mysql-singapore-0 -c mysql -- mysql -u root --password='pass' --host=mysql-singapore-0.mysql-singapore-pods.demo -e "INSERT INTO playground.equipment (type, quant, color) VALUES ('slide', 2, 'blue');"
mysql: [Warning] Using a password on the command line interface can be insecure.

# read from primary
$ kubectl exec -it -n demo mysql-singapore-0 -c mysql -- mysql -u root --password='pass' --host=mysql-singapore-0.mysql-singapore-pods.demo -e "SELECT * FROM playground.equipment;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

Exposing to outside world

For Now we will expose our mysql with ingress with to outside world

$ helm repo add ingress-nginx https://kubernetes.github.io/ingress-nginx
$ helm upgrade -i ingress-nginx ingress-nginx/ingress-nginx  \
                                      --namespace demo --create-namespace \
                                      --set tcp.3306="demo/mysql-singapore:3306"

Let’s apply the ingress yaml thats refers to mysql-singpore service

apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  name: mysql-singapore
  namespace: demo  
spec:
  ingressClassName: nginx
  rules:
  - host: mysql-singapore.something.org
    http:
      paths:
      - backend:
          service:
            name: mysql-singapore
            port:
              number: 3306
        path: /
        pathType: Prefix

$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/mysql-ingress.yaml
ingress.networking.k8s.io/mysql-singapore created
$ kubectl get ingress -n demo
NAME              CLASS   HOSTS                           ADDRESS          PORTS   AGE
mysql-singapore   nginx   mysql-singapore.something.org   172.104.37.147   80      22h

Now will be able to communicate from another cluster to our source database

Prepare for Remote Replica

We wil use the kubedb_plugin for generating configuration for remote replica. It will create the appbinding and and necessary secrets to connect with source server

$ kubectl dba remote-config mysql -n demo mysql-singapore -uremote -ppass -d 172.104.37.147 -y
home/mehedi/go/src/kubedb.dev/yamls/mysql/mysql-singapore-remote-config.yaml

Create Remote Replica

We have prepared another cluster in london region for replicating across cluster. follow the installation instruction above.

Create sourceRef

We will apply the generated config from kubeDB plugin to create the source refs and secrets for it

$ kubectl apply -f  /home/mehedi/go/src/kubedb.dev/yamls/bank_abc/mysql/mysql-singapore-remote-config.yaml

secret/mysql-singapore-remote-replica-auth created
secret/mysql-singapore-client-cert-remote created
appbinding.appcatalog.appscode.com/mysql-singapore created

$ kubectl get appbinding -n  demo
NAME              TYPE               VERSION   AGE
mysql-singapore   kubedb.com/mysql   8.0.35    4m17s

Create remote replica auth

We will need to use the same auth secrets for remote replicas as well since operations like clone also replicated the auth-secrets from source server

apiVersion: v1
data:
  password: cGFzcw==
  username: cm9vdA==
kind: Secret
metadata:
  name: mysql-london-auth
  namespace: demo
type: kubernetes.io/basic-auth

kubectl create -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/mysql-london-auth.yaml

apiVersion: kubedb.com/v1alpha2
kind: MySQL
metadata:
  name: mysql-london
  namespace: demo
spec:
  authSecret:
    name: mysql-london-auth
  healthChecker:
    failureThreshold: 1
    periodSeconds: 10
    timeoutSeconds: 10
    disableWriteCheck: true
  version: "8.0.35"
  replicas: 1
  topology:
    mode: RemoteReplica
    remoteReplica:
      sourceRef:
        name: mysql-singapore
        namespace: demo
  storageType: Durable
  storage:
    storageClassName: "linode-block-storage"
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 10Gi
  terminationPolicy: WipeOut

Here,

spec.topology contains the information about the mysql server.
spec.topology.mode we are defining the server will be working a Remote Replica.
spec.topology.remoteReplica.sourceref we are referring to source to read. The mysql instance we previously created.
spec.terminationPolicy specifies what KubeDB should do when a user try to delete the operation of MySQL CR. Wipeout means that the database will be deleted without restrictions. It can also be “Halt”, “Delete” and “DoNotTerminate”. Learn More about these HERE.

$ kubectl create -f https://github.com/kubedb/docs/raw/v2024.4.27/docs/guides/mysql/clustering/remote-replica/yamls/mysql-london.yaml
mysql.kubedb.com/mysql-london created

Now we will be able to see kubedb will provision a Remote Replica from the source mysql instance. Lets checkout out the statefulSet , pvc , pv and services associated with it . KubeDB operator sets the status.phase to Ready once the database is successfully created. Run the following command to see the modified MySQL object:

$ kubectl get mysql -n demo 
NAME           VERSION   STATUS   AGE
mysql-london   8.0.35    Ready    7m17s

Validate Remote Replica

Since both source and replica database are in the ready state. we can validate Remote Replica is working properly by checking the replication status

$ kubectl exec -it -n demo mysql-london-0 -c mysql -- mysql -u root --password='pass' --host=mysql-london-0.mysql-london-pods.demo -e "show slave status\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.104.37.147
                  Master_User: remote
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000001
          Read_Master_Log_Pos: 4698131
               Relay_Log_File: mysql-london-0-relay-bin.000007
                Relay_Log_Pos: 1415154
        Relay_Master_Log_File: binlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            ....

Read Data

In the previous step we have inserted into the primary pod. In the next step we will read from secondary pods to determine whether the data has been successfully copied to the secondary pods.

# read from secondary-1
$ kubectl exec -it -n demo mysql-london-0 -c mysql -- mysql -u root --password='pass'  --host=mysql-london-0.mysql-london-pods.demo -e "SELECT * FROM playground.equipment;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+

Write on Secondary Should Fail

Only, primary member preserves the write permission. No secondary can write data.

Automatic Failover

To test automatic failover, we will force the primary Pod to restart. Since the primary member (Pod) becomes unavailable, the rest of the members will elect a new primary for these group. When the old primary comes back, it will join the group as a secondary member.

Read the comment written for the following commands. They contain the instructions and explanations of the commands.

# delete the primary Pod mysql-london-0
$ kubectl delete pod mysql-london-0 -n demo
pod "mysql-london-0" deleted

# check the new primary ID
$ kubectl exec -it -n demo mysql-london-0 -c mysql -- mysql -u root --password='pass' --host=mysql-london-0.mysql-london-pods.demo -e "show slave status\G" 
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: mysql.demo.svc
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 214789
               Relay_Log_File: mysql-london-0-relay-bin.000002
                Relay_Log_Pos: 186366
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        ...

# read data after recovery
$ kubectl exec -it -n demo mysql-london-0 -c mysql -- mysql -u root --password='pass' --host=mysql-read-2.mysql-read-pods.demo -e "SELECT * FROM playground.equipment;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  7 | slide |     2 | blue  |
+----+-------+-------+-------+

Cleaning up

Clean what you created in this tutorial.

kubectl delete -n demo my/mysql-singapore
kubectl delete -n demo my/mysql-london
kubectl delete ns demo

Next Steps

Detail concepts of MySQL object.
Detail concepts of MySQLDBVersion object.
Want to hack on KubeDB? Check our contribution guidelines.

KubeDB

Stash

KubeVault

Voyager

ConfigSyncer

Guard

RESOURCES

Druid

Elasticsearch

FerretDB

Kafka

MariaDB

Memcached

Microsoft SQL Server

MongoDB

MySQL

OpenSearch

PerconaXtraDB

PgBouncer

Pgpool

PostgreSQL

ProxySQL

RabbitMQ

Redis

SingleStore

Solr

ZooKeeper