MySQL

Published: 6 Oct. 2026 Last updated: 19 Oct. 2026

Summary

MySQL is one of the world’s most popular open-source relational database management systems. It is widely used for web applications, data warehousing, and transactional workloads. MySQL’s default storage engine, InnoDB, provides ACID-compliant transactions, crash recovery via write-ahead logging (redo log), and row-level locking.

This Application Note explains how to use CloudCasa to back up and restore MySQL running in Kubernetes. It covers the two most common ways to install MySQL:

Helm chart - MySQL is installed directly with a Helm chart. CloudCasa has been tested with MySQL 8.4 installed using the Bitnami MySQL Helm chart.
MySQL operator - MySQL is created and managed by an operator. CloudCasa has been tested with MySQL 8.4 created using the Oracle MySQL Operator for Kubernetes.

The backup steps are the same for both methods. The restore steps are slightly different, and are described separately. The steps are expected to work with later versions of MySQL, and with other installation methods, as well.

Before you start

Persistence must be enabled. CloudCasa backs up the data that MySQL has saved to disk on its Persistent Volumes (PVs). The Bitnami Helm chart and the MySQL operator both enable persistence by default. If persistence has been explicitly disabled in the Helm chart values or the InnoDBCluster specification, re-enable it before using CloudCasa.
InnoDB should be the storage engine. The backup approach described below is designed for InnoDB, which has been the default storage engine since MySQL 5.5. InnoDB’s write-ahead logging and crash recovery ensure that data on disk is always recoverable from a PV snapshot, even if a small number of transactions were still in memory at the time of the snapshot.

Backup

It is recommended to define a full cluster backup, so that all resources MySQL needs, including any cluster-scoped resources, are included and can be restored together. If you are defining a more granular backup, ensure that MySQL namespaces are included, and that the option to backup PVs is enabled (which is the default).

A pre-backup App Hook is used to flush MySQL data to disk before CloudCasa takes the PV snapshot. This Application Note describes three backup methods, each using a different MySQL command in the pre-backup hook. All three have been tested with both the Bitnami Helm chart and the MySQL operator.

Comparison of backup methods
Method	Consistency level	Write blocking	Post-hook needed	Storage engines
FLUSH TABLES WITH READ LOCK (FTWRL)	Application-consistent	Yes — all writes blocked until lock is released	Yes	All (InnoDB, MyISAM, etc.)
LOCK INSTANCE FOR BACKUP	Crash-consistent	No — DML continues; only DDL is blocked	Yes	InnoDB only
FLUSH TABLES	Crash-consistent	No — no persistent lock	No	InnoDB only

Which method should you choose?

If you use only InnoDB tables (the default since MySQL 5.5), FLUSH TABLES is the simplest option. It flushes dirty pages to disk, does not block writes, and does not require a post-backup hook. InnoDB’s crash recovery ensures a consistent restore.
If you need to prevent DDL operations (ALTER TABLE, DROP TABLE, etc.) during the snapshot, use LOCK INSTANCE FOR BACKUP. This is useful if schema changes might run concurrently with backups.
If you use non-InnoDB storage engines (such as MyISAM) or need a fully application-consistent snapshot with no in-flight transactions, use FLUSH TABLES WITH READ LOCK (FTWRL). This is the most protective option, but it blocks all writes for the duration of the snapshot.

Step 1: Find your MySQL pods

Note the namespace where MySQL is running, then list the pods and their labels:

kubectl get pods -n <mysql-namespace> --show-labels

Make a note of:

A label that can uniquely identify MySQL server pods. For example:
- Bitnami Helm chart (standalone): app.kubernetes.io/name=mysql
- Bitnami Helm chart (replication): app.kubernetes.io/component=primary
- MySQL operator: component=mysqld
The name of the MySQL container in the pod. For both the Bitnami Helm chart and the MySQL operator, it is mysql. To check it:
```
kubectl get pod <mysql-pod> -n <mysql-namespace> -o jsonpath='{.spec.containers[*].name}'
```

The App Hook runs once on every pod that matches the label. For the Bitnami Helm chart in replication mode, use a label that selects only the primary pod (e.g. app.kubernetes.io/component=primary) — the replicas replicate from the primary and do not need their own flush. For the MySQL operator (InnoDB Cluster with Group Replication), the hook runs on each member, which is acceptable as each member holds its own copy of the data.

Step 2: Create the pre-backup App Hook

In CloudCasa, go to Configuration → App Hooks and click “Add App Hook”. Fill in the fields as follows:

Name: A name for the hook, for example mysql-pre-backup.

Type: Pre-backup

Pod selector: The label from Step 1, for example app.kubernetes.io/name = mysql or component = mysqld.

Container name: The container name from Step 1, for example mysql.

Command: Use one of the following, depending on which backup method you chose:

Method 1: FLUSH TABLES WITH READ LOCK (FTWRL)

sh -c '
  P="${MYSQL_ROOT_PASSWORD:-$(cat "${MYSQL_ROOT_PASSWORD_FILE:-/dev/null}" 2>/dev/null)}"
  export MYSQL_PWD="$P"
  mysql -u root -e "FLUSH TABLES WITH READ LOCK; DO SLEEP(3600);" &
  LOCK_PID=$!
  echo "$LOCK_PID" > /tmp/cc_ftwrl.pid
  sleep 2
  if ! kill -0 "$LOCK_PID" 2>/dev/null; then
    echo "MySQL lock session failed - check credentials and server status" >&2
    exit 1
  fi
'

This command:

Finds the MySQL root password from the MYSQL_ROOT_PASSWORD environment variable, or from a file named by MYSQL_ROOT_PASSWORD_FILE.
Runs FLUSH TABLES WITH READ LOCK (FTWRL). This flushes all dirty pages from the InnoDB buffer pool to the tablespace files on disk, flushes the binary log, closes all open tables, and acquires a global read lock. The lock prevents any writes until it is released, ensuring the PV snapshot captures a fully consistent point-in-time state with no in-flight transactions.
DO SLEEP(3600) keeps the MySQL client session open in the background so the lock is held while CloudCasa takes the PV snapshot. The lock is released when the post-backup hook terminates this session (Step 3).
The command writes the process ID of the background session to a file, so the post-backup hook can find and stop it.
After 2 seconds, it verifies the background session is still running. If it has exited, that means FTWRL failed (wrong password, MySQL not running, or insufficient privileges), and the hook returns an error.

This method requires a post-backup hook (Step 3).

Method 2: LOCK INSTANCE FOR BACKUP

sh -c '
  P="${MYSQL_ROOT_PASSWORD:-$(cat "${MYSQL_ROOT_PASSWORD_FILE:-/dev/null}" 2>/dev/null)}"
  export MYSQL_PWD="$P"
  mysql -u root -e "LOCK INSTANCE FOR BACKUP; DO SLEEP(3600);" &
  LOCK_PID=$!
  echo "$LOCK_PID" > /tmp/cc_ftwrl.pid
  sleep 2
  if ! kill -0 "$LOCK_PID" 2>/dev/null; then
    echo "MySQL lock session failed - check credentials and server status" >&2
    exit 1
  fi
'

This command:

Runs LOCK INSTANCE FOR BACKUP. This acquires a backup lock that prevents file operations that would be unsafe during a backup (such as DDL statements and FLUSH TABLES ... FOR EXPORT), while allowing normal DML (inserts, updates, deletes) to continue. InnoDB data is not flushed at lock time — instead, InnoDB’s crash recovery replays any pending redo log entries when MySQL starts after restore.
Like Method 1, the lock is held in a background session and released by the post-backup hook.
This method is suitable only for InnoDB tables. Non-InnoDB tables may not be in a consistent state.

This method requires a post-backup hook (Step 3).

Method 3: FLUSH TABLES

sh -c '
  P="${MYSQL_ROOT_PASSWORD:-$(cat "${MYSQL_ROOT_PASSWORD_FILE:-/dev/null}" 2>/dev/null)}"
  export MYSQL_PWD="$P"
  mysql -u root -e "FLUSH TABLES;" && echo "Tables flushed"
'

This command:

Runs FLUSH TABLES. This closes all open tables and flushes dirty pages from the InnoDB buffer pool to the tablespace files on disk. It does not acquire any lock — the command completes immediately and writes resume normally.
Because there is no lock, a small number of transactions may be in flight at the moment the PV snapshot is taken. InnoDB’s crash recovery automatically replays these from the redo log when MySQL starts after restore.
This is the simplest method and the least intrusive. It is suitable only for InnoDB tables.

This method does not require a post-backup hook. Skip Step 3 if you use this method.

Password setup for the MySQL operator

If the MySQL root password is not available as an environment variable in the MySQL pod (which is the case with the Oracle MySQL Operator), the hook command must retrieve the password from the Kubernetes Secret. Replace the first line inside the quotes (the line starting with P=) with the following:

P="${MYSQL_ROOT_PASSWORD:-}"; if [ -z "$P" ]; then TOKEN=$(cat /var/run/secrets/kubernetes.io/serviceaccount/token); NS=$(cat /var/run/secrets/kubernetes.io/serviceaccount/namespace); CLUSTER=$(echo "$HOSTNAME" | sed "s/-[0-9]*$//"); P=$(curl -sk -H "Authorization: Bearer $TOKEN" "https://kubernetes.default.svc/api/v1/namespaces/$NS/secrets/${CLUSTER}-cluster-secret" | sed -n "s/.*\"rootPassword\": *\"\([^\"]*\)\".*/\1/p" | base64 -d); fi

This reads the pod’s service account token, determines the cluster name from the pod hostname, and retrieves the root password from the corresponding Kubernetes Secret via the Kubernetes API. The curl and sed commands are available in the MySQL operator container image.

For this to work, the MySQL pod’s service account must have permission to read Secrets in its namespace. The MySQL operator grants this by default.

Click Save.

Step 3: Create the post-backup App Hook

Skip this step if you chose Method 3 (FLUSH TABLES). Method 3 does not acquire a lock and does not need a post-backup hook.

Create another App Hook with the following settings:

Name: A name for the hook, for example mysql-post-backup.

Type: Post-backup

Pod selector: Same label as the pre-backup hook.

Container name: Same container name as the pre-backup hook.

Command:

sh -c '
  if [ -f /tmp/cc_ftwrl.pid ]; then
    kill "$(cat /tmp/cc_ftwrl.pid)" 2>/dev/null || true
    rm -f /tmp/cc_ftwrl.pid
  fi
'

Click Save.

What this command does:

It kills the background MySQL session started by the pre-backup hook. When the session ends, MySQL automatically releases the global read lock (Method 1) or backup lock (Method 2), and normal operations resume.
If the PID file does not exist (for example, if the pre-backup hook did not run or failed), the command does nothing and exits successfully.

A post-backup App Hook is required for Methods 1 and 2 because the pre-backup hook leaves a lock in place. Without this hook, the lock would only be released when the background sleep expires (up to 1 hour), during which MySQL would be read-only (Method 1) or unable to run DDL (Method 2).

Note: A failed pre-backup App Hook does not stop the backup. The MySQL PVs are still backed up, but CloudCasa marks the backup job as Partially Failed, with a message such as “Backup partially successful. … app hooks failed”. If the pre-backup hook fails, the backed-up data is still recoverable — InnoDB’s crash recovery automatically replays the redo log when MySQL starts from the restored PVs. Open the job on the Activity page and check its errors and warnings to see why the App Hook failed.

Step 4: Create a backup definition

Create a backup definition following the CloudCasa User Guide. Select a full cluster backup, and in the App Hooks section of the backup definition, add the pre-backup hook created in Step 2. If you chose Method 1 or Method 2, also add the post-backup hook from Step 3.

Restore

Restore to a namespace or cluster where MySQL is not already running. Do not restore over a running MySQL deployment. When MySQL starts after the restore, InnoDB automatically runs crash recovery on the restored data files, replaying any pending transactions from the redo log to bring the database to a fully consistent state.

MySQL installed with a Helm chart

When creating the restore definition, select the namespace where MySQL is installed. PVs are included by default.

No operator or Custom Resource Definitions (CRDs) are needed. The restored StatefulSets start the MySQL pods directly.

If MySQL was installed in replication mode (primary and replicas), all instances are restored with their own PVs. Replication resumes automatically after the pods start.

MySQL installed with the MySQL operator

When creating the restore definition, select the namespace where MySQL is installed.

Also select the namespace where the MySQL operator is installed, if the operator is not already running in the target cluster. The operator manages the MySQL InnoDBCluster resources after they are restored. If the operator is not running, the restored MySQL resources may not start correctly. If the operator is already running in the target cluster, restoring its namespace again is optional.

Important: Make sure the “Include all cluster-scoped resources” switch is enabled when creating the restore definition. This restores the CRDs for MySQL resources, such as InnoDBCluster.

Note: If you need to remove a MySQL deployment and its operator from a cluster, for example during testing, delete the MySQL InnoDBCluster resource first and wait until it is fully removed before deleting the operator. If both are deleted at the same time, the MySQL deployment can get stuck while terminating, because the operator is needed to finish the cleanup.

Check the restored data

After the restore completes, check that the MySQL pods are running:

kubectl get pods -n <mysql-namespace>

Then check that your databases and data are intact:

kubectl exec -n <mysql-namespace> <mysql-pod> -c mysql -- sh -c 'export MYSQL_PWD="${MYSQL_ROOT_PASSWORD:-$(cat ${MYSQL_ROOT_PASSWORD_FILE:-/dev/null} 2>/dev/null)}"; mysql -u root -e "SHOW DATABASES;"'

You can also verify the row count for a specific table:

kubectl exec -n <mysql-namespace> <mysql-pod> -c mysql -- sh -c 'export MYSQL_PWD="${MYSQL_ROOT_PASSWORD:-$(cat ${MYSQL_ROOT_PASSWORD_FILE:-/dev/null} 2>/dev/null)}"; mysql -u root -e "SELECT COUNT(*) FROM <your_database>.<your_table>;"'

The list of databases and row counts should match what was present before the backup.

Troubleshooting

MySQL pod fails to start after restore with redo log error

After restoring only the primary PV (not the full namespace), MySQL may fail to start with an error such as:

[ERROR] [MY-013879] [InnoDB] The redo log file ./#innodb_redo/#ib_redo9 comes from other data directory than redo log file ./#innodb_redo/#ib_redo5.

This happens because MySQL 8.4 uses multiple rotated redo log files under the #innodb_redo/ directory. When a single PV is restored in isolation, these files may come from different points in time, and MySQL refuses to start.

To fix this:

Scale down the StatefulSet:

kubectl scale sts <statefulset-name> -n <mysql-namespace> --replicas=0

Run a temporary pod to clear the redo log files:

kubectl run redo-fix -n <mysql-namespace> --image=busybox --restart=Never --overrides='{
  "spec": {
    "containers": [{
      "name": "redo-fix",
      "image": "busybox",
      "command": ["sh", "-c", "rm -f /data/#innodb_redo/#ib_redo* && echo Cleared"],
      "volumeMounts": [{"name": "data", "mountPath": "/data"}]
    }],
    "volumes": [{
      "name": "data",
      "persistentVolumeClaim": {"claimName": "<pvc-name>"}
    }]
  }
}'

Wait for the pod to complete, then delete it:

kubectl wait --for=condition=ready pod/redo-fix -n <mysql-namespace> --timeout=30s
kubectl delete pod redo-fix -n <mysql-namespace>

Scale the StatefulSet back up:

kubectl scale sts <statefulset-name> -n <mysql-namespace> --replicas=1

InnoDB recreates the redo log directory on startup. The database data is not affected — only the redo log replay metadata is cleared.

Note: This issue does not occur when restoring the full namespace (all PVs together), which is the recommended approach. It only affects partial restores where individual PVs are restored separately.