Skip to main content

Operations runbooks

cnmsql ships a kubectl plugin, kubectl-cnmsql, that wraps common day-two operations. Install it once:

curl -sSfL https://github.com/cnmsql/cnmsql/raw/main/hack/install-cnmsql-plugin.sh | sh -s -- -b ~/.local/bin

The script downloads the latest release, verifies its checksum, and installs the plugin along with a kubectl_complete-cnmsql shim for shell tab completion.

Verify a release​

A checksum match only proves the download was not corrupted. Each release also carries two attestations that tie the archives to this repository's release workflow:

  • checksums.txt.sig and checksums.txt.pem: a keyless cosign signature over checksums.txt, recorded in the public Rekor transparency log.
  • kubectl-cnmsql.intoto.jsonl (from v0.9.0): SLSA build provenance for every archive, recording the workflow, commit and tag that built it.

Download the archive for your platform and the release metadata:

VERSION=v0.9.0
ARCHIVE=kubectl-cnmsql_${VERSION#v}_linux_amd64.tar.gz
BASE=https://github.com/cnmsql/cnmsql/releases/download/${VERSION}
for f in "$ARCHIVE" checksums.txt checksums.txt.sig checksums.txt.pem kubectl-cnmsql.intoto.jsonl; do
  curl -sSfLO "$BASE/$f"
done

Check the signature on checksums.txt, then the archive against it:

cosign verify-blob checksums.txt \
  --certificate checksums.txt.pem \
  --signature checksums.txt.sig \
  --certificate-identity "https://github.com/cnmsql/cnmsql/.github/workflows/release.yml@refs/tags/${VERSION}" \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com
sha256sum --ignore-missing -c checksums.txt

Check the provenance with slsa-verifier:

slsa-verifier verify-artifact "$ARCHIVE" \
  --provenance-path kubectl-cnmsql.intoto.jsonl \
  --source-uri github.com/cnmsql/cnmsql \
  --source-tag "$VERSION"

On macOS, use shasum -a 256 --ignore-missing -c checksums.txt instead of sha256sum.

The operator image ghcr.io/cnmsql/cnmsql is signed the same way by the build workflow, on every push to main and every release tag. It also carries an SPDX SBOM and SLSA provenance for each platform:

cosign verify ghcr.io/cnmsql/cnmsql:${VERSION#v} \
  --certificate-identity "https://github.com/cnmsql/cnmsql/.github/workflows/build.yml@refs/tags/${VERSION}" \
  --certificate-oidc-issuer https://token.actions.githubusercontent.com

Inspect the attestations with Docker Buildx:

docker buildx imagetools inspect ghcr.io/cnmsql/cnmsql:${VERSION#v} \
  --format '{{ json .SBOM }}'        # SPDX SBOM per platform
docker buildx imagetools inspect ghcr.io/cnmsql/cnmsql:${VERSION#v} \
  --format '{{ json .Provenance }}'  # SLSA provenance per platform

Most commands accept an optional CLUSTER argument. When you omit it, the plugin picks the only cluster in the current namespace. If the namespace holds several clusters, read-only commands (status, logs, metrics) warn and pick the first by name, while commands that change anything refuse to guess and ask for an explicit CLUSTER.

Commands in this guide use cluster-sample as the Cluster name.

Inspect cluster state​

kubectl cnmsql status
kubectl cnmsql status cluster-sample

Add -w or --watch to refresh every 2s, like watch(1):

kubectl cnmsql status -w
kubectl cnmsql status -w --watch-interval=5s

The status command follows the layout of kubectl cnpg status: a cluster summary (primary and when it was promoted, health, data size, current GTID), any conditions that need attention, the continuous backup and binlog archiving state (first point of recoverability, last successful and failed backups, archiving health), each replica's replication stream (IO/SQL threads, lag, semi-sync), a per-instance table, recent backups and certificate expiry. Add -v for every condition and backup, full GTID sets, and the Services and PodDisruptionBudgets. An instance whose manager cannot be reached is shown as Unreachable rather than failing the command. For raw Kubernetes output, kubectl describe cluster and kubectl get events still work and give you more detail when you need it.

Key status fields on the Cluster resource:

  • status.readyInstances
  • status.currentPrimary
  • status.targetPrimary
  • status.gtidExecutedByInstance
  • status.divergedInstances
  • status.replicationBrokenInstances
  • status.continuousArchiving
  • status.phase and status.phaseReason

Stream logs​

kubectl cnmsql logs cluster cluster-sample          # all instances, merged with a prefix
kubectl cnmsql logs cluster cluster-sample cluster-sample-2  # single instance

Scale up​

kubectl patch cluster cluster-sample --type merge -p '{"spec":{"instances":4}}'
kubectl wait --for=condition=Ready cluster/cluster-sample --timeout=15m

Scale-up is ordered. cnmsql creates one replica at a time and waits for it to be healthy before creating the next one.

Scale down​

kubectl patch cluster cluster-sample --type merge -p '{"spec":{"instances":1}}'

Scale-down removes highest-ordinal replicas first. cnmsql deletes replica Pods and stops their bootstrap Jobs — running ones included, even when the Pod was never created. Bootstrapped PVCs are retained; volumes that never finished bootstrapping are deleted. It never scales below one instance and does not remove the current primary during normal scale-down.

If the primary itself sits above the new count (for example a failover promoted cluster-sample-3 and you then scale to 2), cnmsql first switches the primary over to a healthy replica within the desired count, then removes the old one. While that happens the cluster reports Ready=False with phase Provisioning and a reason naming the primary. Scale-down also waits for any switchover or failover already in progress to finish before removing anything.

List retained PVCs:

kubectl get pvc -l mysql.cnmsql.co/cluster=cluster-sample

Delete retained PVCs only after confirming the data is no longer needed.

Planned switchover​

cnmsql follows the CNPG-style status transition model. A planned switchover promotes a named healthy replica. Use the plugin:

kubectl cnmsql promote cluster-sample cluster-sample-2

Watch progress:

kubectl cnmsql status -w

The operator validates the target, waits for GTID containment, bounds the operation by spec.maxSwitchoverDelay, and lets the selected instance promote itself. Role Services move after the database role is safe.

Fence an instance​

Fencing takes an instance out of service without deleting it or its data. The Pod stays and the PVC stays, but the instance drops out of all routing Services and mysqld is stopped:

kubectl cnmsql fence on cluster-sample cluster-sample-2

Fencing the primary, or every instance with '*', takes the cluster's writes offline, so the command asks for confirmation first (--yes skips it).

Unfence it to restart mysqld and restore normal routing and role reconciliation:

kubectl cnmsql fence off cluster-sample cluster-sample-2

The in-Pod manager stops mysqld while staying alive as PID 1, so the Pod keeps answering its control and liveness endpoints. The liveness probe does not depend on mysqld being up, so a fenced instance is not restarted by the kubelet. Because mysqld is down, the Pod reports NotReady and shows as 0/1 Running, which is expected. The data directory is untouched, so you can mount the PVC elsewhere or restart the instance by unfencing.

The operator tracks fenced instances in status.fencedInstances. A fenced instance is skipped as a failover candidate. Fencing the primary stops writes for the cluster because the rw Service has no endpoint. Once the fence has stopped mysqld, the primary counts as failed, so automatic failover promotes a safe replica after spec.failoverDelay. The fenced Pod is not deleted: it stays fenced until you unfence it, and it then rejoins as a replica of the new primary. Use fencing to freeze an instance for inspection or maintenance; to move the primary role deliberately, use a switchover.

Automatic failover​

Automatic failover is driven by primary health, Pod readiness, and GTID safety. spec.failoverDelay controls how long cnmsql waits after detecting the primary as failed. 0 means immediate failover.

spec:
  failoverDelay: 30

During failover cnmsql:

  1. chooses a ready replica with healthy replication SQL state;
  2. excludes any replica already known to be diverged (see below);
  3. checks that candidate GTID sets are comparable;
  4. fences the old primary Pod while retaining its PVC (a primary you fenced yourself is already stopped and keeps its Pod and its fence);
  5. sets targetPrimary to the safe candidate;
  6. updates role labels and Services after promotion.

If GTID sets are divergent or no safe candidate exists, failover is blocked instead of risking data loss.

Known-diverged replicas are excluded explicitly, before the GTID comparison. This matters because a diverged replica's GTID set is a superset: it carries errant transactions the others never saw. The candidate selection would otherwise pick it as the most up to date, and promoting it makes those errant transactions canonical. The primary is unreachable during a failover, so divergence cannot be computed live; instead the operator relies on status.divergedInstances recorded earlier, while the primary was still reachable. If every surviving candidate is known-diverged, failover blocks with "every replica candidate has diverged ... manual recovery required" rather than promoting one. Re-initialise a survivor (see Re-initialise an instance) to recover.

Former primary rejoin​

A former primary that returns after failover starts read-only and follows the current primary if its GTID set is compatible.

If it contains errant transactions, cnmsql marks it diverged and keeps it out of service. Do not delete the retained PVC until you have decided whether manual recovery is required.

Check:

kubectl cnmsql status cluster-sample

Look for entries under divergedInstances. To bring a diverged instance back into service, re-initialise it (see Re-initialise an instance), which discards its data and re-clones from a backup.

A replica whose replication has aborted at the SQL layer (a stopped IO or SQL thread with a recorded error, such as a duplicate-key conflict) is reported under replicationBrokenInstances and marks the cluster Degraded, even while the Pod is still Running. This catches a replica that is up but silently no longer replicating, which would otherwise sit unnoticed. Re-initialise it when the break is not transient.

Restart an instance​

Restart all instances in a rolling fashion, or a single instance:

kubectl cnmsql restart cluster-sample          # rolling restart
kubectl cnmsql restart cluster-sample cluster-sample-2  # single instance

The command prompts for confirmation. Skip the prompt with --yes or -y.

A single instance is restarted by deleting its Pod gracefully. For the primary, the Pod's preStop hook holds mysqld up until the operator has switched the role over to a replica (unless spec.enableSwitchoverOnDrain is false, in which case stopping it triggers a failover), then mysqld shuts down cleanly.

Every instance boots read only. The in-pod role reconciler observes Cluster status and only clears read-only mode when the instance is the confirmed primary.

Destroy an instance​

Delete a single instance Pod and its PVC:

kubectl cnmsql destroy cluster-sample cluster-sample-3

This command also prompts for confirmation, listing what it will delete. It refuses when the Pod or PVC does not carry the cluster's label, so a mistyped instance name cannot delete an unrelated claim. Use it to clean up a failed or diverged instance you have decided to discard. The remaining instances keep running unaffected.

Re-initialise an instance from scratch​

When a replica is diverged (errant GTIDs) or its replication is irrecoverably broken, you can re-initialise it instead of destroying it. MySQL has no pg_rewind to surgically realign a divergent replica, so the remediation is the same as CloudNativePG's destroy-and-rebootstrap fallback: discard the local data and re-clone a fresh copy from a backup.

kubectl cnmsql reinit cluster-sample cluster-sample-2

The operator deletes the instance's Pod and PVC and recreates them empty, so the bootstrap re-clones from a backup and rejoins replication. The instance keeps its name and ordinal, so it keeps its server_id; only its data is discarded.

This is destructive and irreversible. Any data that exists only on that instance, such as errant transactions, is lost. It prompts for confirmation; skip the prompt with --yes/-y.

The current primary cannot be re-initialised this way. It is the replication source, so the command refuses it. To replace a primary, switch over first, then re-initialise the former primary as a replica.

Under the hood the command appends the instance to the Cluster's cnmsql.cnmsql.co/reinit annotation, a comma-separated list the operator consumes. Re-initialisation is always human-triggered; the operator never re-clones an instance over its retained PVC on its own. The operator clears the entry once the teardown completes and the instance has been recreated.

Reload MySQL parameters​

After you change spec.mysql.parameters, apply dynamic parameters without restarting:

kubectl cnmsql reload cluster-sample

This connects to each instance over mTLS and issues the equivalent of reloading the running configuration. Parameters that require a restart are noted and need a follow-up rolling restart.

Update parameters:

kubectl patch cluster cluster-sample --type merge -p \
  '{"spec":{"mysql":{"parameters":{"require_secure_transport":"ON"}}}}'

cnmsql owns replication, backup, PITR, identity, and lifecycle-critical settings. User parameters that conflict with managed keys are rejected by the configuration layer.

Take an on-demand backup​

Instead of crafting a Backup YAML by hand, use the plugin:

kubectl cnmsql backup cluster-sample

This creates a Backup object with sensible defaults: xtrabackup method, prefer-standby target, online mode. The Backup reconciler then runs the actual XtraBackup job. Track it:

kubectl cnmsql status cluster-sample
kubectl get backup -l mysql.cnmsql.co/cluster=cluster-sample

For a SQL dump instead, add --method logical (and --databases to dump only some). kubectl cnmsql backup download <backup> fetches a finished dump from the object store, and kubectl cnmsql restore <cluster> --backup <backup> --databases <db> loads databases from it back into the running cluster. See Logical Backups.

For recurring backups, create a ScheduledBackup resource. See the Scheduled Backups page for the schedule format and options.

Deleting the Backup Kubernetes object does not delete the remote object-store artifacts today. Remote cleanup is a planned finalizer/retention feature.

User management​

cnmsql manages MySQL users through the control-tier API, reached over mTLS port-forwarding inside the cluster:

kubectl cnmsql user create cluster-sample --name=app --password-stdin < secret.txt
kubectl cnmsql user alter cluster-sample --name=app        # prompt for new password
kubectl cnmsql user list cluster-sample
kubectl cnmsql user drop cluster-sample --name=old-user

Passwords are never accepted as flags. Use --password-stdin for piping from a secret, or let the plugin prompt on the terminal with echo disabled.

Users can be created with optional grants (--superuser), TLS requirements (--require-x509), and named privileges.

Database management​

Manage MySQL databases the same way:

kubectl cnmsql database create cluster-sample --name=analytics
kubectl cnmsql database list cluster-sample
kubectl cnmsql database drop cluster-sample --name=analytics

You can specify character set and collation on create:

kubectl cnmsql database create cluster-sample --name=utf8db --charset=utf8mb4 --collation=utf8mb4_unicode_ci

Node maintenance window​

Toggle the maintenance window before draining a node or performing Kubernetes node maintenance:

kubectl cnmsql maintenance set cluster-sample
kubectl cnmsql maintenance unset cluster-sample

set prompts for confirmation (--yes skips it). By default the window reuses PVCs: the operator relaxes the cluster's PodDisruptionBudgets so its nodes can be drained, and rescheduled Pods reattach their existing volumes. Pass --reuse-pvc=false to keep the budgets in force, since draining a node would otherwise discard that instance's data.

Scrape Prometheus metrics​

kubectl cnmsql metrics cluster-sample              # primary
kubectl cnmsql metrics cluster-sample cluster-sample-2  # specific instance
kubectl cnmsql metrics -w --filter=mysql_global_status_threads  # watch mode, filtered

Add -w for continuous refresh. Use --filter with a pattern to narrow the output to matching metric names (grep-style substring match).

Benchmark a cluster​

Both benchmarks run as short-lived Jobs in the cluster's namespace. They stream their output to your terminal, and the Job (plus any scratch volume) is deleted when the run finishes.

Benchmark MySQL throughput with sysbench against the read-write endpoint:

kubectl cnmsql bench mysql cluster-sample
kubectl cnmsql bench mysql cluster-sample --time=60 --threads=8 --tables=16
kubectl cnmsql bench mysql cluster-sample --tests=oltp_point_select

The plugin creates a temporary user that can connect over the network, along with a scratch database, runs the workloads you selected, and drops the user when it is done. The operator's root user only listens on the local socket, so it is never used over the network. The bench user's generated password reaches the Job through a Secret, and the connection uses TLS.

Benchmark storage with fio against a fresh PVC on the cluster's storage class. It reports IOPS, throughput, and latency:

kubectl cnmsql bench fio cluster-sample
kubectl cnmsql bench fio cluster-sample --node=worker-2 --size=5Gi --file-size=4Gi

fio can be heavy on I/O, so the command asks for confirmation first; pass --yes to skip it. Add --dry-run to either subcommand to print the resources it would create without applying them, or --keep to leave the Job in place for inspection instead of deleting it.

Continuous archiving operations​

When continuous archiving is enabled, inspect:

kubectl cnmsql status cluster-sample

Look for continuousArchiving in the output. Growing pending files or a degraded condition usually means an object-store, credential, network, or throughput issue.

Safe maintenance habits​

  • Prefer planned switchover before node or primary maintenance.
  • Keep at least three instances for meaningful automatic failover.
  • Use semi-sync when acknowledged-write durability matters.
  • Keep object-store lifecycle rules aligned with backup and PITR retention.
  • Treat retained PVCs and remote backups as recovery assets.

Group Replication operations​

These commands apply only to clusters with spec.replication.mode: groupReplication. They refuse to run against async clusters.

Inspect the group view​

kubectl cnmsql group status cluster-gr
kubectl cnmsql group status cluster-gr -w

Shows the group name, whether it is bootstrapped, quorum status, the current primary, online member count, view ID, and a per-member table with state, role, and reachability.

Recover from quorum loss​

When a Group Replication cluster loses quorum (fewer than a majority of members are ONLINE), writes are blocked and the cluster reports Phase=Blocked. Recovery is a deliberate, confirmed human action through the plugin:

kubectl cnmsql group recover cluster-gr

Before acting, read the full command help and the Group Replication page. Quorum recovery overrides Paxos consensus with group_replication_force_members and can cause split-brain and permanent data loss if a lost member is still running elsewhere. The command prints a consequence summary and requires confirmation. The operator independently verifies quorum is lost and a safe survivor exists before acting; it refuses if safety is unprovable.

Fencing under Group Replication​

Fencing works the same way (kubectl cnmsql fence on/off) but acts differently under the hood:

  • Instead of stopping mysqld, the fenced member runs STOP GROUP_REPLICATION, gracefully leaving the group. mysqld stays up and reachable for inspection.
  • Fencing the primary triggers a group re-election. Fencing a secondary shrinks the group, and the operator refuses if it would drop the group below quorum.

Switchover under Group Replication​

Switchover works the same way (kubectl cnmsql promote <cluster> <target>) but uses group_replication_set_as_primary on the group instead of the async stop/promote/demote dance. The operator validates that the target is an ONLINE SECONDARY, invokes the UDF, and observes the result. Bounded by spec.maxSwitchoverDelay as with async.