MariaDB Operations
Tips and tricks for managing and operating the MariaDB cluster within a Genestack environment.
Connect to the database
Sometimes an operator may need to connect to the database to troubleshoot things or otherwise make modifications to the databases in place. The following command can be used to connect to the database from a node within the cluster.
mysql -h $(kubectl -n openstack get service mariadb-cluster-primary -o jsonpath='{.spec.clusterIP}') \
-p$(kubectl --namespace openstack get secret mariadb -o jsonpath='{.data.root-password}' | base64 -d) \
-u root
Info
The following command will leverage your kube configuration and dynamically source the needed information to connect to the MySQL cluster. You will need to ensure you have installed the mysql client tools on the system you're attempting to connect from.
Manually dumping and restoring databases
When running mysqldump
or mariadbdump
the following commands can be useful for generating a quick backup.
Individual Database Backups
mysqldump --host=$(kubectl -n openstack get service mariadb-cluster -o jsonpath='{.spec.clusterIP}')\
--user=root \
--password=$(kubectl --namespace openstack get secret mariadb -o jsonpath='{.data.root-password}' | base64 -d) \
--single-transaction \
--routines \
--triggers \
--events \
${DATABASE_NAME} \
--result-file=/tmp/${DATABASE_NAME}-$(date +%s).sql
Dump all databases as individual files in /tmp
mysql -h $(kubectl -n openstack get service mariadb-cluster -o jsonpath='{.spec.clusterIP}') \
-u root \
-p$(kubectl --namespace openstack get secret mariadb -o jsonpath='{.data.root-password}' | base64 -d) \
-e 'show databases;' \
--column-names=false \
--vertical | \
awk '/[:alnum:]/' | \
xargs -i mysqldump --host=$(kubectl -n openstack get service mariadb-cluster -o jsonpath='{.spec.clusterIP}') \
--user=root \
--password=$(kubectl --namespace openstack get secret mariadb -o jsonpath='{.data.root-password}' | base64 -d) \
--single-transaction \
--routines \
--triggers \
--events \
{} \
--result-file=/tmp/{}-$(date +%s).sql
Individual Database Restores
Ensure the destination database exists
The destination database must exist prior to restoring individual SQL
backups. If it does not already exist, it's important to create the
database with the correct charset and collate values. Failing to do so can
result in errors such as Foreign Key Constraint is Incorrectly Formed
during DB upgrades.
Restoring a database
Restore using the MariaDB CRD
To restore the most recent successful backup, create the following resource to spawn a job that will mount the same storage as the backup and apply the dump to your MariaDB database.
Refer to the mariadb-operator restore documentation for more information.
The following command may lead to data loss
Tip
If you have multiple backups available, the operator is able to infer which
backup to restore based on the spec.targetRecoveryTime
field discussed
in the operator documentation here.