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=wordpress@789 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.