Docker: backup DB and troubleshooting

In my local development environment, I have a running database that I want to backup.

Find container

First, list containers with docker ps, identify the database you want to backup and note down the CONTAINER ID.

In my case, the container id is 3f7b373c036d.

Backup

Create a file named backup.properties for storing the backup needed information, such as container id, DB name and credentials, and backup directory (which must exists).

DB_IMAGE_ID=2a62a5ddad13
DB_USER=root
DB_PASSWORD=somewordpress
DB_NAME=wordpress
BACKUP_DIR=~/backup/local

Then, source the properties file and execute the backup using docker exec .., as you see in the snippet below.

# read a file with variables
source backup.properties
# perform the backup
sudo docker exec $DB_IMAGE_ID /usr/bin/mysqldump --add-drop-table -u $DB_USER -p$DB_PASSWORD $DB_NAME > "$BACKUP_DIR/db-backup-$(date +"%Y-%m-%d_%H-%M-%S").sql"

Note: be careful when providing the password. As you can read from mysqldump manual, there must be no space between the option and the password itself in both cases: -p<password> and --password=<password>.

Troubleshooting with container shell

In some cases, you might need to access the container’s shell for gathering relevant information. For example, when you don’t know the db name and you need to find it.

To access the container’s shell, get the container id, and start bash with docker exec

docker exec -it 2a62a5ddad13 /bin/bash

Note: you are accessing the container as root.

Following with the example, we launch the mysql prompt from the container shell

# launch mysql prompt as root
mysql -u root -p

From the prompt, we can list the databases, and find the missing database name.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| wordpress          |
+--------------------+
5 rows in set (0.01 sec)

Now we know the DB name is wordpress, so we can exit the msysql prompt with the exit; command.

Then, we can perform a db backup with mysqldump from inside the docker shell.

root@2a62a5ddad13:/backup
# mysqldump --add-drop-table -u root -p wordpress > db-backup.sql

Now we have a backup file in /backup/db-backup.sql, inside the container. We can now exit the docker shell with the exit command.

Then, we can use the host’s shell to copy the backup from the container to the host filesystem.

user@host:~
$ docker cp 2a62a5ddad13:/backup ~/backup/docker-db.sql

References

• https://hub.docker.com/r/aveltens/wordpress-backup/