How to Run MySQL Docker Container [With Backup and Restore]

In this article, I’m going to run and manage the MySQL docker container with mounted volumes for production. Also explaining about MySQL backup and restore with simple bash script, which we’re going to integrate with the cron tab.

Setting up MySQL Docker

I’m using MySQL version 8.0 which is the latest image that available in docker hub. And here the docker run command to deploy the docker container. And you can customize the name and passwords accordingly. Also, we’re exposing the port 3306 to local.

docker run -d --name mysql-wp -p3306:3306 -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=wordpress -e MYSQL_USER=wordpress -e MYSQL_PASSWORD=mypassword mysql:8.0

Mounting Directories

MySQL is the Database, and it’s the stateful container. So We need to mount the data folder to the host machine for reusability even after killing/deleting the container.

docker run -d --name mysql-wp -p3306:3306 -v ./db_data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mypassword -e MYSQL_DATABASE=wordpress -e MYSQL_USER=wordpress -e MYSQL_PASSWORD=mypassword mysql:8.0

Also, recommended to use –net=host argument, which is uses main network interface of host for faster communication.

MySQL Backup

I’m using simple command to take backup from running MySQL docker container. We can also set this command on crontab for regular backup in production.

docker exec mysql-wp /usr/bin/mysqldump -u root --password=mypassword mydb > /tmp/latest_backup.sql

First and second parameter is user and password of MySQL DB. And third parameter is mydb is the name of the database. You can replace it with your DB.

MySQL Restore

Here is the command to restore the .sql file to the running WordPress container. And the parameter is as same as backup command.

cat /tmp/latest_backup.sql | docker exec -i mysql-wp /usr/bin/mysql -u root --password=mypassword mydb

Wrap Up

Here is my backup script to take backup regularly with the data time tramp as file name. And also it keeps 10 old backup files based on time period. This script you can set in crontab for regular backups.

#!/bin/bash
mkdir -p backups
FILE=./backups/latest_backup.sql
if [ -f "$FILE" ]; then
    mv ./backups/latest_backup.sql ./backups/$(date +"%Y-%m-%d_%H-%M-%S")_backup.sql
fi
docker exec mysql-wp /usr/bin/mysqldump -u root --password=mypassword wordpress > ./backups/latest_backup.sql
find ./backups -type f -mtime +10 -delete

Most commonly, we use MySQL for WordPress, and Here is the link to create and manage the WordPress docker container with Nginx configuration.