MySQL InnoDB Cluster: idealogic-prod

This page documents the MySQL InnoDB Cluster deployment for production workloads, including backup and restore procedures.

Cluster overview

Cluster name

idealogic-prod

Namespace

mysql

Operator

MySQL Operator for Kubernetes v2.1.9

Instances

2 (Primary + Secondary)

Router instances

2

Storage class

longhorn-db-cluster

Storage size

40Gi per instance

Architecture

The cluster uses MySQL Group Replication in single-primary mode:

                    ┌─────────────────────────────────────┐
                    │           MySQL Routers             │
                    │  idealogic-prod-router (2 replicas) │
                    └─────────────┬───────────────────────┘
                                  │
                    ┌─────────────┴───────────────┐
                    │                             │
              ┌─────▼─────┐                ┌──────▼────┐
              │  Primary  │◄──────────────►│ Secondary │
              │idealogic- │  Group         │idealogic- │
              │ prod-0    │  Replication   │ prod-1    │
              └───────────┘                └───────────┘

  • MySQL Router - Load balances client connections and provides automatic failover

  • Primary instance - Handles all write operations

  • Secondary instance - Read replica with automatic promotion on primary failure

Connecting to the cluster

Applications connect via the MySQL Router service:

Read/Write

idealogic-prod.mysql.svc.cluster.local:6446

Read-only

idealogic-prod.mysql.svc.cluster.local:6447

Credentials

The root password is stored in the mysql-password secret in the mysql namespace:

kubectl get secret mysql-password -n mysql -o jsonpath='{.data.rootPassword}' | base64 -d

Backup configuration

The cluster uses the MySQL Operator’s native backup feature with S3 storage.

Backup schedule

Frequency

Daily at 02:00 UTC

Retention

7 days

Method

dumpInstance (logical backup)

Storage

AWS S3

Bucket

idl-xnl-jhb1-rc1-backup

Prefix

mysql/idealogic-prod/

Region

eu-west-1

Backup components

The backup system consists of three parts:

  1. Backup profile (s3-daily) - Defines the S3 storage configuration

  2. Backup schedule (daily-backup) - CronJob that triggers backups at 02:00 UTC

  3. Cleanup job (idealogic-prod-backup-cleanup) - CronJob that removes backups older than 7 days at 04:00 UTC

Monitoring backups

List all backup resources:

kubectl get mysqlbackups -n mysql

Check the status of a specific backup:

kubectl get mysqlbackup <backup-name> -n mysql -o yaml

Manual backup

To trigger an immediate backup:

kubectl apply -f - <<EOF
apiVersion: mysql.oracle.com/v2
kind: MySQLBackup
metadata:
  name: manual-backup-$(date +%Y%m%d%H%M%S)
  namespace: mysql
spec:
  clusterName: idealogic-prod
  backupProfileName: s3-daily
EOF

Restore procedures

The MySQL Operator supports restore via the initDB feature when creating a new cluster.

Option 1: Restore to a new cluster

Create a new InnoDBCluster that initialises from the S3 backup:

apiVersion: mysql.oracle.com/v2
kind: InnoDBCluster
metadata:
  name: idealogic-prod-restored
  namespace: mysql
spec:
  instances: 2
  router:
    instances: 2
  secretName: mysql-password
  tlsUseSelfSigned: true

  initDB:
    dump:
      storage:
        s3:
          bucketName: idl-xnl-jhb1-rc1-backup
          prefix: mysql/idealogic-prod
          config: s3-backup-credentials
          endpoint: https://s3.eu-west-1.amazonaws.com
          profile: default

  datadirVolumeClaimTemplate:
    storageClassName: longhorn-db-cluster
    accessModes:
      - ReadWriteOnce
    resources:
      requests:
        storage: 40Gi

This creates a parallel cluster for testing or migration without affecting production.

Option 2: Restore to the existing cluster

This procedure requires downtime. The existing cluster must be deleted and recreated.

  1. Delete the existing cluster (PVCs are retained but will not be used):

    kubectl delete innodbcluster idealogic-prod -n mysql

  2. Wait for pods to terminate:

    kubectl get pods -n mysql -w

  3. Apply the restore manifest with initDB section pointing to the backup:

    kubectl apply -f idealogic-prod-cluster-restore.yml

  4. Monitor the restore progress:

    kubectl logs -f -l mysql.oracle.com/cluster=idealogic-prod -n mysql

Option 3: Manual restore with MySQL Shell

For selective table or database restore, connect directly to the cluster:

kubectl exec -it idealogic-prod-0 -n mysql -c mysql -- mysqlsh root@localhost

Then use util.loadDump():

util.loadDump("mysql/idealogic-prod", {
    s3BucketName: "idl-xnl-jhb1-rc1-backup",
    s3EndpointOverride: "https://s3.eu-west-1.amazonaws.com",
    resetProgress: true,
    ignoreExistingObjects: true
})

Dependent applications

The following applications use this MySQL cluster:

Application Database

wordpress-wpca-prod

wpcycling

wordpress-wpca-test

wpcycling_test

jasper-reports

jasperreports

Troubleshooting

Check cluster status

kubectl get innodbcluster idealogic-prod -n mysql

View cluster events

kubectl describe innodbcluster idealogic-prod -n mysql

Access MySQL shell

kubectl exec -it idealogic-prod-0 -n mysql -c mysql -- mysql -u root -p

Check replication status

From within MySQL shell:

SELECT * FROM performance_schema.replication_group_members;

Related resources