March 13, 2020

How to dump and restore MySQL/MariaDB databases

Options for dumping data:

# Directly
mysqldump --databases $MYSQL_DATABASE -u$MYSQL_USER -p$MYSQL_PASSWORD > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

# Docker
docker exec -it <container_name> sh -c 'mysqldump --databases $MYSQL_DATABASE -u$MYSQL_USER -p$MYSQL_PASSWORD' > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

# Kubernetes (vulnerable to networking failures)
kubectl -n <namespace> exec deploy/<deploy_name> -- bash -c 'mysqldump --databases $MYSQL_DATABASE -u$MYSQL_USER -p$MYSQL_PASSWORD' > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

Additionally, if your database operates in a remote server (like Amazon RDS), you may still use a local Docker container for dumping the data.

# Create a local container with the desired version (in this example, MariaDB 10.4 is used)
docker run -it --rm -v ${PWD}:/dump -w /dump mariadb:10.4 bash

# Get the data
mysqldump -h <hostname> --databases <database_name> -u <database_user> --password='<password>' > dump-`date '+%Y_%m_%d__%H_%M_%S'`.sql

For restoring data, you can use:

mysql -u$MYSQL_USER -p$MYSQL_PASSWORD < dump.sql

If you’re using Docker, you can also place dump files on /docker-entrypoint-initdb.d and those will be imported on the first run. Accepted files types are *.sql, *.sql.gz, and *.sh.

<laury.dev/>