Using mysqldump for Backup and Restore in a Kubernetes-Based SingleStore Deployment

{question}

How to use mysqldump For database backup and restore in a Kubernetes environment?

{question}

{answer}

Creating a Backup Using mysqldump

To conduct a database dump from SingleStore operating in a Kubernetes environment, follow the steps below:

1. Create a Temporary Pod with MySQL Client
Launch a temporary interactive pod that includes the MySQL client. This pod will be used to run the mysqldump command.

kubectl run mysqldump-client --rm -i -t --image=mysql:8 --restart=Never -- bash

2. Run mysqldump Inside the Pod
Once inside the temporary pod, execute the command to export the desired database. Substitute <AGGREGATOR-SVC> with the aggregator service name (typically in the format <cluster-name>-aggregator):

mysqldump -h <AGGREGATOR-SVC> -P 3306 -u <user> -p<password> <db_name> > dump.sql

3. Copy the Dump File from the Pod
Open a second terminal and copy the dump file to your local machine using:

kubectl cp mysqldump-client:/dump.sql ./dump.sql

Note: Kubernetes pods typically use ephemeral storage (e.g., /tmp or the container’s filesystem), which is limited in size. If the dump file exceeds this limit, the pod may crash due to insufficient disk space. Always ensure that you copy the dump file out before the pod is terminated.

Restoring a Database from a Backup File

You can restore the database using the MySQL client by following one of these methods:

Direct Restore from File

mysql -hhostname -P 3306 -u root -ppassword your_db < your_db.sql

OR

Log in and use the source command:

mysql> use Test

Database changed

mysql> source dump.sql

Query OK, 0 rows affected (0.00 sec)

{answer}