A Better Dockerized MySQL

MySQL still plays a large part in many software stacks and while many IaaS vendors have their own hosted versions (i.e. Amazon RDS), it’s still fairly common to run MySQL in a Docker container, especially in development environments.

One common problem that’s encountered with MySQL is initializing it before its use and having your application connect only after initialization is complete. While some find it acceptable to include initialization code in their application startup, in my own projects I prefer a run script that handles initialization as part of starting the container’s process and then have the application startup gracefully handle the connection.

Building the MySQL image requires four files:

• Dockerfile — Installs mysql-apt-config to support fetching MySQL via apt-get, adds our support files, and compiles the timezone.sql file from the base image’s timezone files
• my.cnf — A MySQL configuration file
• mysql.list — Apt source list for downloading MySQL
• run.sh — Run script that we’ll be using to initialize and launch MySQL

The Dockerfile shown below installs MySQL, creates the timezone SQL, and adds the remaining supporting files to the image.

The MySQL configuration file can be modified as needed. Do note the datadir location is set to /var/lib/mysql. Launching the container with a data volume mounted at /var/lib/mysql will allow you to persist data when the container is removed.

The apt sources below should be updated to match the base ubuntu image from the Dockerfile when updates occur.

deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-apt-config
deb http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.6
deb-src http://repo.mysql.com/apt/ubuntu/ trusty mysql-5.6

The run script below should be used as the container command to start MySQL

#!/bin/bash
#
# This does the job of mysql_secure_installation via queries that works
# better in a single-process Docker environment
#

secure_installation() {
    echo "Securing installation.."
    mysql -u root <<-EOF
    UPDATE mysql.user SET Password=PASSWORD('$MYSQL_ROOT_PASSWORD') WHERE User='root';
    DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
    DELETE FROM mysql.user WHERE User='';
    DELETE FROM mysql.db WHERE Db='test' OR Db='test\_%';
    GRANT ALL PRIVILEGES ON *.* TO '$MYSQL_USERNAME' IDENTIFIED BY '$MYSQL_PASSWORD' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    CREATE DATABASE IF NOT EXISTS test;
    CREATE DATABASE IF NOT EXISTS $MYSQL_DATABASE CHARACTER SET utf8;
    SOURCE /usr/share/mysql/innodb_memcached_config.sql;
    USE mysql;
    SOURCE /etc/mysql/zoneinfo.sql;
    install plugin daemon_memcached soname "libmemcached.so";
EOF
}

if [ -z "$MYSQL_ROOT_PASSWORD" ]; then
    echo "MYSQL_ROOT_PASSWORD not set";
    exit 1;
fi

if [ -z "$MYSQL_USERNAME" ]; then
    echo "MYSQL_USERNAME not set";
    exit 1;
fi

if [ -z "$MYSQL_PASSWORD" ]; then
    echo "MYSQL_PASSWORD not set";
    exit 1;
fi

if [ -z "MYSQL_DATABASE" ]; then
    echo "MYSQL_DATABASE not set";
    exit 1;
fi

echo "Setting up initial data"
mysql_install_db

echo "Starting mysql to setup privileges..."
/usr/sbin/mysqld &
MYSQL_TMP_PID=$!
echo "Sleeping for 5s"
sleep 5

# Try to connect as root without a password
mysql -u root <<-EOF
USE mysql;
EOF

if [ $? != 1 ]; then
    secure_installation
else
    echo "Error is OK, root password already set"
fi 

echo "Kill temporary mysql daemon"
kill -TERM $MYSQL_TMP_PID && wait

echo "Launching mysqld_safe"
/usr/bin/mysqld_safe

Starting the container with “run.sh” ensures the database is initialized and that the root user has a password. Additionally, it installs the memcached plugin and populates the timezone tables. Upon completion, it launches mysqld_safe as the final command.
We can build the image:

docker build -t custom-mysql .

And then start the container:

docker run -d --name custom-mysql -e MYSQL_ROOT_PASSWORD=rootpass -e MYSQL_USERNAME=mysql_user -e MYSQL_PASSWORD=password -e MYSQL_DATABASE=mydb custom-mysql /run.sh

After a few moments, the database will be fully initialized and ready to accept connections.

This works OK when you’re manually launching containers, but causes problems with Docker Compose, Elastic Beanstalk, and other platforms that launch containers for you. Our solution is rather simple.

A run script acts as a wrapper around any applications that need to access the database and requires a configurable number of successful connections before launching the application.

Here’s how we do it:

#!/bin/bash
SUCCESSFUL_ATTEMPTS=0
MIN_SUCCESSFUL=5
check_db() {
    python -c "import MySQLdb, os; MySQLdb.connect(host=os.environ.get('MYSQL_HOST'), user=os.environ.get('MYSQL_USERNAME'), passwd=os.environ.get('MYSQL_PASSWORD'));"
    if [ $? -eq 0 ]; then
        SUCCESSFUL_ATTEMPTS=$((SUCCESSFUL_ATTEMPTS+1))
    fi
}

check_db
while [ SUCCESSFUL_ATTEMPTS -lt MIN_SUCCESSFUL ]; do
    check_mysql
    echo "$SUCCESSFUL_ATTEMPTS successful connections. Need $MIN_SUCCESSFUL. Sleeping for 1 second and retrying..."
    sleep 1
done

We include check_db.sh in any run scripts that we use to start our database-dependent applications like so:

#!/bin/bash
. ./check_db.sh
uwsgi -s /tmp/uwsgi.sock -w app

Until our container is able to make 5 successful connections to our MySQL container, the uwsgi process simply won’t start and we can tweak the minimum required connection attempts as needed.

No more launch failures due to unreachable MySQL containers!