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

Binary log retention

Retention is binlog_expire_logs_seconds=604800 (7 days) at roughly 1 GiB of binlog per day, against a dataset of about 5 GiB. Expected steady state on a 40 GiB datadir is therefore near 30% used.

Before 2026-09-19 this was never configured and sat at the MySQL 8.4 compiled default of 2592000 (30 days). That converges on ~30 GiB of binlog and parks the volume at 88-90% full permanently, which fired MySQLDatadirFillingUp on idealogic-prod-0 and -1.

7 days is sized off the nightly dumpInstance backup: deleteBackupData is false, so every nightly full dump is kept indefinitely and 7 days of binlog gives sub-day point-in-time recovery comfortably past the most recent usable dump.

spec.mycnf is inert on a running cluster. The operator renders it into the config ConfigMap only at initial provisioning, so the manifest value applies to a rebuilt or DR-restored cluster and nothing else.

SET PERSIST is per instance and does not replicate. It must be run on all three members separately, or a restarted member silently reverts to the 30-day default.

Applying the retention change to a running cluster

Run on each of idealogic-prod-0, -1 and -2. The router cannot be used to target a specific member, so go through the pod:

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

for i in 0 1 2; do
  kubectl exec -n mysql "idealogic-prod-$i" -c mysql -- \
    mysql --protocol=socket -uroot -p"$ROOT_PW" -e "
      SET PERSIST binlog_expire_logs_seconds = 604800;
      SELECT @@hostname, @@binlog_expire_logs_seconds;"
done

Verify against performance_schema.persisted_variables, which reflects the actual contents of mysqld-auto.cnf. A row there is the proof that the value survives a restart:

SELECT @@hostname AS host,
       @@binlog_expire_logs_seconds AS live_value,
       (SELECT VARIABLE_VALUE FROM performance_schema.persisted_variables
         WHERE VARIABLE_NAME = 'binlog_expire_logs_seconds') AS in_mysqld_auto_cnf;

Do not verify persistence with performance_schema.variables_info.VARIABLE_SOURCE. Immediately after SET PERSIST that column reads DYNAMIC, not PERSISTED, on a correctly persisted variable — DYNAMIC means "assigned during this server run", which SET PERSIST also does. It only flips to PERSISTED after a restart, once the value is read back from mysqld-auto.cnf at startup.

variables_info therefore cannot distinguish SET GLOBAL from SET PERSIST while the server is up. persisted_variables can, and is the right check. (The max_connections note in idealogic-prod-cluster.yml reaches the right conclusion by the right evidence — an empty mysqld-auto.cnf — but its shorthand "DYNAMIC = NOT persisted" does not generalise.)

Reclaiming the space

Changing the variable does not free anything on its own. MySQL purges expired binlogs only at rotation or at startup, so force one rotation per member:

FLUSH BINARY LOGS;

FLUSH BINARY LOGS is not replicated, so it too runs per instance. Expect roughly 23 GiB freed per member, taking the datadir from about 82% to about 30%.

Confirm all three members are ONLINE before purging — SELECT MEMBER_HOST, MEMBER_STATE FROM performance_schema.replication_group_members. Binlogs are the donor source for Group Replication distributed recovery, and purging while a member is rejoining forces a full clone instead of an incremental catch-up.

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