Stormes.Net - Programming in PHP

MySQL in Docker for FasterDevelopment and Testing

James Stormes June 8, 2017 Docker, MySQL, Setup No Comments

Docker MySQL for Development

In my last post I presented a strategy for managing the database schema and lookup tables as part of your code base. In continuing in that theme I wanted to include tool for using that strategy for development and testing. If you are to consider your database schema as part of your code then you must treat it as part of your code. You should have automated tests around it. You should include test scenarios for regression testing and edge case testing. If this testing is hard you will be unlikely to do it. Docker MySQL can help you automate this process.

Unless you have been coding under a rock, you will have bumped up against Docker. Weather you like it or not, it provides some cool functionality, letting you build entire ecosystems for applications with a few simple scripts. We will be using this functionality to build a MySql environment that can be used for development and testing. You will need to have Docker installed to follow this post.

You can find the code at: https://github.com/jstormes/DockerMySqlDev

For this demo we will be using the test data from: https://github.com/datacharmer/test_db

Scripts to Start the Docker Containers

Let setup some scripts to orchestrate things for us. There are two distinct types of script, the one that setup and run the Docker containers and the ones that run inside the Docker containers. It is important to understand the difference. The ones that setup the containers are specific to each platform, Windows, Apple OS X, and Linux. While the one that run inside the containers will be the same regardless of the platform. This is one of the advantages of using Docker for development.

WindowsRequirements

PowerShell Available
Docker Installed

Apple OS X Requirements

Xcode installed
Docker Installed
iTerm (Optional)

Linux Requirements

Docker Installed and in the path

The Windows glue script

# PowerShell Script to start a temporary MySql server.
# This script starts a MySql docker container, then starts a
# bash shell in the container.
#
# When the user exits the bash shell it stops the container.
#
#
$Container_Name = "mysql-dev"
$MYSQL_ROOT_PASSWORD = "mypassword"
$MYSQL_DATABASE_NAME = "employees"
$EXTERNAL_MYSQL_PORT = "4000"

if (-Not (Get-Command "docker" -errorAction SilentlyContinue))
{
    echo "Docker not installed, the 'docker' command is required to run this scrip."
    exit
}

$project_root = (get-item $PSScriptRoot ).parent.FullName

docker run --detach --rm --name=$Container_Name -h $Container_Name -v $project_root/:/opt/project -w /opt/project `
    -p ${EXTERNAL_MYSQL_PORT}:3306 `
    -e="MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD" -e="MYSQL_DATABASE_NAME=$MYSQL_DATABASE_NAME" `
    -e="EXTERNAL_MYSQL_PORT=$EXTERNAL_MYSQL_PORT" mysql
docker exec -it $Container_Name bash -c 'exec bash /opt/project/bin/bashrc'
docker stop $Container_Name

# PowerShell Script to start a temporary MySql server.

# This script starts a MySql docker container, then starts a

# bash shell in the container.

# When the user exits the bash shell it stops the container.

$Container_Name = "mysql-dev"

$MYSQL_ROOT_PASSWORD = "mypassword"

$MYSQL_DATABASE_NAME = "employees"

$EXTERNAL_MYSQL_PORT = "4000"

if (-Not (Get-Command "docker" -errorAction SilentlyContinue))

{

echo "Docker not installed, the 'docker' command is required to run this scrip."

exit

}

$project_root = (get-item $PSScriptRoot ).parent.FullName

docker run --detach --rm --name=$Container_Name -h $Container_Name -v $project_root/:/opt/project -w /opt/project `

-p ${EXTERNAL_MYSQL_PORT}:3306 `

-e="MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD" -e="MYSQL_DATABASE_NAME=$MYSQL_DATABASE_NAME" `

-e="EXTERNAL_MYSQL_PORT=$EXTERNAL_MYSQL_PORT" mysql

docker exec -it $Container_Name bash -c 'exec bash /opt/project/bin/bashrc'

docker stop $Container_Name

To run the script; Using File Explorer navigate to the “bin/start-mysql-dev.ps1” file right click on it and “Run with PowerShell”.

The OS X/Linux glue script

#!/usr/bin/env bash
# This script starts a MySql docker container, then starts a
# bash shell in the container.
#
# When the user exits the bash shell it stops the container.
#
#
Container_Name="mysql-dev"
MYSQL_ROOT_PASSWORD="mypassword"
MYSQL_DATABASE_NAME="employees"
EXTERNAL_MYSQL_PORT="4000"

docker -v >/dev/null 2>&1 ||
{
    echo >&2 "Docker not installed, the 'docker' command is required to run this scrip.";
    exit 1;
}

project_root="$( cd "$( dirname "${BASH_SOURCE[0]}" )"/.. && pwd )"

docker run --detach --rm --name=$Container_Name -h $Container_Name -v "$project_root":/opt/project -w /opt/project \
    -p ${EXTERNAL_MYSQL_PORT}:3306 \
    -e="MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD" -e="MYSQL_DATABASE_NAME=$MYSQL_DATABASE_NAME" \
    -e="EXTERNAL_MYSQL_PORT=$EXTERNAL_MYSQL_PORT" mysql
docker exec -it $Container_Name bash -c 'exec bash /opt/project/bin/bashrc'
docker stop $Container_Name

#!/usr/bin/env bash

# This script starts a MySql docker container, then starts a

# bash shell in the container.

# When the user exits the bash shell it stops the container.

Container_Name="mysql-dev"

MYSQL_ROOT_PASSWORD="mypassword"

MYSQL_DATABASE_NAME="employees"

EXTERNAL_MYSQL_PORT="4000"

docker -v >/dev/null 2>&1 ||

{

echo >&2 "Docker not installed, the 'docker' command is required to run this scrip.";

exit 1;

}

project_root="$( cd "$( dirname "${BASH_SOURCE[0]}" )"/.. && pwd )"

docker run --detach --rm --name=$Container_Name -h $Container_Name -v "$project_root":/opt/project -w /opt/project \

-p ${EXTERNAL_MYSQL_PORT}:3306 \

-e="MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD" -e="MYSQL_DATABASE_NAME=$MYSQL_DATABASE_NAME" \

-e="EXTERNAL_MYSQL_PORT=$EXTERNAL_MYSQL_PORT" mysql

docker exec -it $Container_Name bash -c 'exec bash /opt/project/bin/bashrc'

docker stop $Container_Name

To run the script in OS X; Using Finder navigate to the “bin/start-mysql-dev.sh” file right click on it “Open With” -> “iTerm”.

To run the script in Linux; in a shell run the “bin/start-mysql-dev.sh” file.

Glue script summary

The glue scripts are very similar between Windows, OS X and Linux. Looking at the files you can see line for line the Windows and Bash scripts are preforming the same function. This “glue” gives us an OS level abstraction that makes each of the system the same beyond this point. Using each systems preferred scripting environment gives us better control over the execution than say a “docker-compose.yml” file. While still letting us use the abstracted results.

The MySQL Automation

If you run the above script, you will be dropped into a command prompt, with the following prompt:

The sample data will have been loaded and ready to go. You can connect from the host system using the information provided in the terminal. You can connect from another Docker container using the the provided information as long as you use “–link mysql-dev:mysql-dev” in the docker command. There will be more on this in a follow up post.

You can also use this as a training/testing environment. When you type exit the server will vanish and when you run it again, the database will be created again from scratch. Adding *.sql files to the sql/base directory will let you play with setting up new tables and populating with seed data.