← back home

WordPress Storage Container – Setting up the database in Docker

Written by Bunkers on February 25, 2017

This is a follow on post to yesterday's introduction covering my motivations and approach for setting up a development WordPress Docker environment. If you're following along, make sure you have a working Docker tool set installed, so you can run docker and docker-compose on the command line. If you haven't then look back at yesterday's post for some options.

I've simplified (at least for me) the approach taken in my main reference article by not using Ansible. This has pros and cons, but it's another technology to understand, and another moving part to go wrong!

I am using docker-compose. It's technology that may go out of favour soon for more production level tools for orchestrating multiple containers, but for this project it's perfect.

Four containers make up the environment. These are:

Let's start with the database. We're going to use the main image, but add in a couple of scripts to make backing up and restoring the database more straight forward.

Create a project directory (I've called mine wp-docker) and a sub-directory called percona. In there create a file called load_db.sh with the following contents:

#!/bin/bash
echo "use $MYSQL_DATABASE;" | cat - /tmp/wp_backup.sql > temp && mv temp /tmp/wp_backup.sql
mysql -uroot -p$MYSQL_ROOT_PASSWORD < /tmp/wp_backup.sql && rm /tmp/wp_backup.sql

If you're familiar with using the MySQL client this should be straightforward. It's taking a SQL backup file from /tmp/wp_backup.sql and adding a command to switch to the correct database at the top. Then it logs in as root, runs the SQL script and deletes it. This is so you don't accidentally stamp over your database at a later date. You'll notice we're using a couple of environment variables here. I'll cover that shortly.

As you might be able to work out from this, if you've got a database backup you'd like to use put that in a further sub-directory called tmp and name it wp_backup.sql

Next create another script file called backup.sh that just contains:

#!/bin/bash
MYSQL_PWD=$MYSQL_ROOT_PASSWORD mysqldump -hlocalhost -uroot $MYSQL_DATABASE

This script is a single command. It runs mysqldump to get a backup SQL script for the database.

Now create the Docker file, called Dockerfile with the following contents:

FROM percona:latest
 
COPY tmp/wp_backup.sql /tmp/
COPY load_db.sh /
COPY backup.sh /
RUN chmod +x /load_db.sh
RUN chmod +x /backup.sh

This take the latest Percona image and copies in our database backup along with our two helper scripts. Lastly it makes the scripts executable so we can actually run them!

Come out of the percona directory and create another file called db.env. As the name hints at, this is a file containing the database environment variables used by all the containers that need database connection information.

You can vary the contents from mine if you want to use different names for your database and user, but for now make the contents:

MYSQL_DATABASE=wpdocker
MYSQL_USER=wpdocker
MYSQL_PASSWORD=password
MYSQL_RANDOM_ROOT_PASSWORD=1
WORDPRESS_DB_HOST=wpdb

That's everything we need and to build our Percona container. Use this command to do just that:

docker build ./percona

All being well this will output an image ID which we can then use to run a container with:

docker run --env-file db.env -name percona -d caf6680063ae

Your image ID at the end will be different from mine. If you want to check you're up and running then you can run:

docker exec -it percona mysql -uwpdocker -p wpdocker

After the prompt for your password, set in the db.env file then you should find yourself in the familiar MySQL command line client.

The last file to create is another helper script. You'll notice that there is an environment variable called MYSQL_RANDOM_ROOT_PASSWORD which causes the generation of a random password for the root user on startup. It outputs this just once, so if needed, you must search the logs for GENERATED ROOT PASSWORD. I found this was a bit irritating, so created a script to help. If you want to do the same, create a file called dbpass.sh and make it executable with chmod +x dbpass.sh. Place the following script in the file:

#!/bin/bash
docker logs percona 2>&1 | grep PASSWORD | sed -e 's/GENERATED ROOT PASSWORD\: //g' | awk 'NR==1 { printf("%s", $0); next } { printf("\n%s", $0) }' | pbcopy

This is a bit hacky, but it searches the logs of the container for a line with PASSWORD in it. The sed command removes GENERATED ROOT PASSWORD: at the start of the line and the awk command makes sure the end of the line has carriage returns stripped. Lastly it pipes it through pbcopy to place it on my clipboard. This is Mac specific, and I don't know if there's an equivalent for Linux, so feel free to remove that. It will just echo the password to the screen instead.

Now we can run our load_db.sh script with:

docker exec -e MYSQL_ROOT_PASSWORD=aihohjohz7Ahhohx9fidohpheideik percona ./load_db.sh

Your password will hopefully be different! Getting a backup of the database is similar:

docker exec -e MYSQL_ROOT_PASSWORD=aihohjohz7Ahhohx9fidohpheideikoo percona ./backup.sh > db20170225.sql

There's some additional work I'll do here when this is a production environment. Organisation and rotation of the backup files for example. There's a good script I use elsewhere that backs up MySQL databases via a daily cron job.

So there we have it! One layer of the application complete. Any application requiring a MySQL database could use the same setup too. After all that scripting it's time to reward myself with a strong cup of coffee!