instantlinux/mysqldump repository overview

⁠mysqldump

This dockerizes a simple script I wrote in 2008 to perform a daily dump of the MySQL databases in a Percona Galera cluster. This image is based on MariaDB 10.3.x client.

This Kubernetes or docker-compose service definition will cause a dump to happen at the default hour (3:30am in $TZ) from a server named dbhost onto a subdirectory "mysql" in volume "backup".

⁠Usage

Before running it, grant access to a mysql user thus:

    mysql> GRANT SELECT,RELOAD,REPLICATION CLIENT ON *.* TO
      '$USER'@'10.%' IDENTIFIED BY '$PSWD';

Make sure the named volume "backup" exists, and that your mysql-backup secret contains the $PSWD you've set:

    # docker volume create backup
    # docker secret create mysql-backup - <<EOT
    user=bkp
    password=$PSWD
    EOT

Optionally, add this role user to your Docker host:

    useradd -u 210 -c "Mysql backups" -s /bin/bash mysqldump

(Not recommended: an alternative user can be specified if you override the entrypoint to invoke adduser, and define the USERNAME environment variable.)

Retention is set by a variable $KEEP_DAYS which defaults to 31: within the directory you will find a subdirectory dbhost, and within that a separate directory for each day of the month. If you set $KEEP_DAYS to 7, it will keep a directory for each day of the week. Backups older than $KEEP_DAYS will be automatically removed.

Launch this docker image in kubernetes or docker-compose using one of the files provided here. This repo has complete instructions for building a kubernetes cluster⁠ where you can launch with helm⁠ or kubernetes.yaml⁠ using make and customizing Makefile.vars⁠ after cloning this repo:

git clone https://github.com/instantlinux/docker-tools.git
cd docker-tools/k8s
make mysqldump

⁠Variables

⁠Secrets

An example to define credentials in Kubernetes:

apiVersion: v1
kind: Secret
metadata:
  name: mysql-backup-creds
type: Opaque
data:
  mysql-backup-creds: |
    password=yourmileagemayvary
    user=backupallthethings

⁠Notes

Successful completion of dumps is indicated by updates to the file mysqldump-status.txt: if that file's age exceeds 24+ hours, look for failures. If the dump files are complete but storage rapidly runs out because zstd or bzip2 didn't run, run the script manually (using the commands seen in crontab -l -u mysqldump from a su - mysqldump shell within the container) and check for errors such as the following:

mariadb-dump: Got error: 1044: "Access denied for user 'bkp'@'%' to database 'rsnap'" when using LOCK TABLES
mariadb-dump: Couldn't execute 'show events': Access denied for user 'bkp'@'%' to database 'rsnap' (1044)

or

mariadb-dump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'test'': Column count of mysql.proc is wrong. Expected 22, found 21. Created with MariaDB 120002, now running 120302. Please use mariadb-upgrade to fix this error (1558)

You may need to grant permissions or run the mariadb-upgrade maintenance command on your database, if anything has recently changed on the server.

Dumps run in parallel in order to use available multi-core CPUs (mainly for compression). You can limit the number of simultaneous runs by changing the value of SKEW_SECONDS, so if your server has many databases, increase this to prevent consuming excessive memory or CPU.

It's tested on MariaDB, so the LOCK_FOR_BACKUP parameter isn't really supported unless someone submits a pull-request to make this work with Percona again.

⁠Contributing

If you want to make improvements to this image, see CONTRIBUTING⁠.