Daniel Watrous on Software Engineering

A Collection of Software Problems and Solutions

Posts tagged mysql

Software Engineering

Use Docker to Build a LEMP Stack (Buildfile)

I’ve been reviewing Docker recently. As part of that review, I decided to build a LEMP stack in Docker. I use Vagrant to create an environment in which to run Docker. For this experiment I chose to create Buildfiles to create the Docker container images. I’ll be discussing the following files in this post.


Download the Docker LEMP files as a zip (docker-lemp.zip).

Spin up the Host System

I start with Vagrant to spin up a host system for my Docker containers. To do this I use the following files.


# -*- mode: ruby -*-
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "ubuntu/trusty64"
  config.vm.network "public_network"
  config.vm.provider "virtualbox" do |v|
    v.name = "docker LEMP"
    v.cpus = 1
    v.memory = 512
  config.vm.provision :shell, path: "bootstrap.sh"

I start with Ubuntu and keep the size small. I create a public network to make it easier to test my LEMP setup later on.


#!/usr/bin/env bash
# set proxy variables
#export http_proxy=http://proxy.example.com:8080
#export https_proxy=https://proxy.example.com:8080
# bootstrap ansible for convenience on the control box
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 36A1D7869245C8950F966E92D8576A8BA88D21E9
sh -c "echo deb https://get.docker.io/ubuntu docker main > /etc/apt/sources.list.d/docker.list"
apt-get update
apt-get -y install lxc-docker
# need to add proxy specifically to docker config since it doesn't pick them up from the environment
#sed -i '$a export http_proxy=http://proxy.example.com:8080' /etc/default/docker
#sed -i '$a export https_proxy=https://proxy.example.com:8080' /etc/default/docker
# enable non-root use by vagrant user
groupadd docker
gpasswd -a vagrant docker
# restart to enable proxy
service docker restart

I’m working in a proxied environment, so I need to provide proxy details to the Vagrant host for subsequent Docker installation steps. Unfortunately Docker doesn’t key off the typical environment variables for proxy, so I have to define them explicitly in the docker configuration. This second proxy configuration allows Docker to download images from DockerHub. Finally I create a docker group and add the vagrant user to it so I don’t need sudo for docker commands.

At this point, it’s super easy to play around with Docker and you might enjoy going through the Docker User Guide. We’re ready to move on to create Docker container images for our LEMP stack.

Build the LEMP Stack

This LEMP stack will be split across two containers, one for MySQL and the other for Nginx+PHP. I build on Ubuntu as a base, which may increase the total size of the image in exchange for the ease of using Ubuntu.

MySQL Container

We’ll start with MySQL. Here’s the Dockerfile.


# LEMP stack as a docker container
FROM ubuntu:14.04
MAINTAINER Daniel Watrous <email>
#ENV http_proxy http://proxy.example.com:8080
#ENV https_proxy https://proxy.example.com:8080
RUN apt-get update
RUN apt-get -y upgrade
# seed database password
COPY mysqlpwdseed /root/mysqlpwdseed
RUN debconf-set-selections /root/mysqlpwdseed
RUN apt-get -y install mysql-server
RUN sed -i -e"s/^bind-address\s*=\s* =" /etc/mysql/my.cnf
RUN /usr/sbin/mysqld & \
    sleep 10s &&\
    echo "GRANT ALL ON *.* TO admin@'%' IDENTIFIED BY 'secret' WITH GRANT OPTION; FLUSH PRIVILEGES" | mysql -u root --password=secret &&\
    echo "create database test" | mysql -u root --password=secret
# persistence: http://txt.fliglio.com/2013/11/creating-a-mysql-docker-container/ 
CMD ["/usr/bin/mysqld_safe"]

Notice that I declare my proxy servers for the third time here. This one is so that the container can access package downloads. I then seed the root database passwords and proceed to install and configure MySQL. Before running CMD, I expose port 3306. Remember that this port will be exposed on the private network between Docker containers and is only accessible to linked containers when you start it the way I show below. Here’s the mysqlpwdseed file.


mysql-server mysql-server/root_password password secret
mysql-server mysql-server/root_password_again password secret

If you downloaded the zip file above and ran vagrant in the resulting directory, you should have the files above available in /vagrant/mysql. The following commands will build and start the MySQL container.

cd /vagrant/mysql
docker build -t "local/mysql:v1" .
docker images
docker run -d --name mysql local/mysql:v1
docker ps

At this point you should show a local image for MySQL and a running mysql container, as shown below.


Nginx Container

Now we’ll build the Nginx container with PHP baked in.


# LEMP stack as a docker container
FROM ubuntu:14.04
MAINTAINER Daniel Watrous <email>
ENV http_proxy http://proxy.example.com:8080
ENV https_proxy https://proxy.example.com:8080
# install nginx
RUN apt-get update
RUN apt-get -y upgrade
RUN apt-get -y install nginx
RUN echo "daemon off;" >> /etc/nginx/nginx.conf
RUN mv /etc/nginx/sites-available/default /etc/nginx/sites-available/default.bak
COPY default /etc/nginx/sites-available/default
# install PHP
RUN apt-get -y install php5-fpm php5-mysql
RUN sed -i s/\;cgi\.fix_pathinfo\s*\=\s*1/cgi.fix_pathinfo\=0/ /etc/php5/fpm/php.ini
# prepare php test scripts
RUN echo "<?php phpinfo(); ?>" > /usr/share/nginx/html/info.php
ADD wall.php /usr/share/nginx/html/wall.php
# add volumes for debug and file manipulation
VOLUME ["/var/log/", "/usr/share/nginx/html/"]
CMD service php5-fpm start && nginx

After setting the proxy, I install Nginx and update the configuration file. I then install PHP5 and update the php.ini file. I then use two different methods to create php files. If you’re deploying an actual application to a Docker container, you may not end up using either of these, but instead install a script that will grab your application from git or subversion.

Next I define two volumes. You’ll see shortly that this makes it straightforward to view logs and manage code for debug. Finally I expose port 80 for web traffic and the CMD references two commands using && to make sure both PHP and Nginx are running in the container.


server {
        listen 80 default_server;
        listen [::]:80 default_server ipv6only=on;
        root /usr/share/nginx/html;
        index index.php index.html index.htm;
        server_name localhost;
        location / {
                try_files $uri $uri/ =404;
        error_page 404 /404.html;
        error_page 500 502 503 504 /50x.html;
        location = /50x.html {
                root /usr/share/nginx/html;
        location ~ \.php$ {
                fastcgi_split_path_info ^(.+\.php)(/.+)$;
                fastcgi_pass unix:/var/run/php5-fpm.sock;
                fastcgi_index index.php;
                include fastcgi_params;

This is my default Nginx configuration file.


// database credentials (defined in group_vars/all)
$dbname = "test";
$dbuser = "admin";
$dbpass = "secret";
$dbhost = "mysql";
// query templates
$create_table = "CREATE TABLE IF NOT EXISTS `wall` (
   `id` int(11) unsigned NOT NULL auto_increment,
   `title` varchar(255) NOT NULL default '',
   `content` text NOT NULL default '',
   PRIMARY KEY  (`id`)
$select_wall = 'SELECT * FROM wall';
// Connect to and select database
$link = mysql_connect($dbhost, $dbuser, $dbpass)
    or die('Could not connect: ' . mysql_error());
echo "Connected successfully\n<br />\n";
mysql_select_db($dbname) or die('Could not select database');
// create table
$result = mysql_query($create_table) or die('Create Table failed: ' . mysql_error());
// handle new wall posts
if (isset($_POST["title"])) {
    $result = mysql_query("insert into wall (title, content) values ('".$_POST["title"]."', '".$_POST["content"]."')") or die('Create Table failed: ' . mysql_error());
// Performing SQL query
$result = mysql_query($select_wall) or die('Query failed: ' . mysql_error());
// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo "\t<tr>\n";
    foreach ($line as $col_value) {
        echo "\t\t<td>$col_value</td>\n";
    echo "\t</tr>\n";
echo "</table>\n";
// Free resultset
// Closing connection
<form method="post">
Title: <input type="text" name="title"><br />
Message: <textarea name="content"></textarea><br />
<input type="submit" value="Post to wall">

Wall is meant to be a simple example that shows both PHP and MySQL are working. The commands below will build this new image and start it as a linked container to the already running MySQL container.

cd /vagrant/nginx
docker build -t "local/nginx:v1" .
docker images
docker run -d -p 80:80 --link mysql:mysql --name nginx local/nginx:v1
docker ps

So that now your Docker environment looks like the image below with an image for MySQL and one for Nginx and two processes running that are linked together.


As shown in the image above, we have mapped port 80 on the container to port 80 on the host system. This means we can discover the IP address of our host (remember the public network in the vagrant file) and load the web page.


Working with Running Containers

True to the single responsibility container design of Docker, these running containers are only running their respective service(s). That means they aren’t running SSH (and they shouldn’t be). So how do we interact with them, such as viewing the log files on the Nginx server or connecting to MySQL? We use Linked containers that leverage the shared volumes or exposed ports.

Connect to MySQL

To connect to MySQL, create a new container from the same MySQL image and link it to the already running MySQL. Start that container with /bin/bash. You can then use the mysql binaries to connect. Notice that I identify the host as ‘mysql’. That’s because when I linked the containers, Docker added an alias in the /etc/hosts file of the new container that mapped the private Docker network IP to ‘mysql’.

docker run -i -t --link mysql:mysql local/mysql:v1 /bin/bash
mysql -u admin -h mysql test --password=secret


View Ngnix Logs

It’s just as easy to view the Nginx logs. In this case I start up a plain Ubuntu container using the –volumes-from and indicate the running nginx container. From bash I can easily navigate to the Nginx logs or the html directory.

docker run -i -t --volumes-from nginx ubuntu /bin/bash




Software Engineering

Build a Multi-server LEMP stack using Ansible

My objective in this post is to explore the use of Ansible to configure a multi-server LEMP stack. This builds on the preliminary work I did demonstrating how to use Vagrant to create an environment to run Ansible. You can follow this entire example on any Windows (or Linux) host.

Ansible only runs on Linux hosts, not Windows. As a result, I needed to provision one Linux host to act as Ansible controller. One aspect of Ansible that I wanted to explore is the ability to manage multiple hosts with different configurations. For this experiment, I provision two more Linux hosts, one to act as a database host and the other to function as an Nginx/PHP server for a complete LEMP stack. I created the diagram below to illustrate my setup.


There are two primary artifact categories for this experiement:

  • Vagrantfile to provision each host
  • Ansible playbook related files

Since there were more than a few Ansible playbook files, I chose to create a github repository rather than provide all the code here. You can clone/fork the files to run this experiment here:



Here is a list of the files you’ll find in that repository.

  • Vagrantfile
  • control.sh
  • lemp/group_vars/all
  • lemp/hosts
  • lemp/roles/common/handlers/main.yml
  • lemp/roles/common/tasks/main.yml
  • lemp/roles/database/handlers/main.yml
  • lemp/roles/database/tasks/main.yml
  • lemp/roles/web/handlers/main.yml
  • lemp/roles/web/tasks/main.yml
  • lemp/roles/web/templates/default
  • lemp/roles/web/templates/wall.php
  • lemp/site.yml

I do use a bootstrap shell script, control.sh, with Vagrant for the Ansible control server. It is necessary to install Ansible on the control server, but since Ansible doesn’t require an agent, there’s no need to bootstrap the other servers.

Playbook files

For each Ansible defined role there are three artifact categories.

  • handlers
  • tasks
  • templates

Handlers are named tasks that can be called or notified when Ansible detects other events. These are commonly used to trigger service restarts when configuration files change, as an example.

Tasks are the meat of the playbook. This lists out the steps to put a system into a desired state, including installing software, copying templates, registering and calling handlers, etc.

Configuration files, such as the nginx ‘default’ configuration in this case, can be stored in the templates folder and copied to the host using a task. Templates are helpful when a desired configuration differs significantly from a system default, this can be easier than updating individual lines in a file one at a time using lineinfile. The Ansible playbook files are in the following directory.


The site.yml file ties it all together by associating host groups with roles. You run the playbook like this.

ansible-playbook -i hosts site.yml

The example wall.php script should be accessible locally using the port 80->8080 mapping as or over port 80 on the external IP assigned to the web host. Here’s what you can expect to see.



I used the ansible examples repository on Github while putting this together. You may find it useful. For the specifics of installing LEMP on Ubuntu, I followed my Vagrant tutorial.

Software Engineering

Using Vagrant to build a LEMP stack

I may have just fallen in love with the tool Vagrant. Vagrant makes it possible to quickly create a virtual environment for development. It is different than cloning or snapshots in that it uses minimal base OSes and provides a provisioning mechanism to setup and configure the environment exactly the way you want for development. I love this for a few reasons:

  • All developers work in the exact same environment
  • Developers can get a new environment up in minutes
  • Developers don’t need to be experts at setting up the environment.
  • System details can be versioned and stored alongside code

This short tutorial below demonstrates how easy it is to build a LEMP stack using Vagrant.

Install VirtualBox

Vagrant is not a virtualization tool. Instead vagrant will leverage an existing provider of virtual compute resources, either local or remote. For example, Vagrant can be used to create a virtual environment on Amazon Web Services or locally using a tool like VirtualBox. For this tutorial, we’ll use VirtualBox. You can download and install VirtualBox from the official website.


Install Vagrant

Next, we install Vagrant. Downloads are freely available on their website.


For the remainder of this tutorial, I’m going to assume that you’ve been through the getting started training and are somewhat familiar with Vagrant.

Accommodate SSH Keys

UPDATE 6/26/2015: Vagrant introduced the unfortunate feature of producing a random key for each new VM as the default behavior. It’s possible to restore the original functionality (described below) and use the insecure key with the config.ssh.insert_key = false setting in a Vagrantfile.

Until (if ever) Vagrant defaults to using the insecure key, a system wide work around is to add a Vagrantfile to the local .vagrant.d folder which will add set this setting for all VMs (see Load Order and Merging), unless otherwise overridden. The Vagrant file can be as simple as this:

# -*- mode: ruby -*-
# vi: set ft=ruby :
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.ssh.insert_key = false

Vagrant creates an SSH key which it installs on guest hosts by default. This can be a huge time saver since it prevents the need for passwords. Since I use PuTTY on windows, I needed to convert the SSH key and save a PuTTY session to accommodate connections. Use PuTTYgen to do this.

  1. Open PuTTYgen
  2. Click “Load”
  3. Navigate to the file C:\Users\watrous\.vagrant.d\insecure_private_key

PuTTYgen shows a dialog saying that the import was successful and displays the details of the key, as shown here:


Click “Save private key”. You will be prompted about saving the key without a passphrase, which in this case is fine, since it’s just for local development. If you end up using Vagrant to create public instances, such as using Amazon Web Services, you should use a more secure connection method. Give the key a unique name, like C:\Users\watrous\.vagrant.d\insecure_private_key-putty.ppk and save.

Finally, create a saved PuTTY session to connect to new Vagrant instances. Here are some of my PuTTY settings:



The username may change if you choose a different base OS image from the vagrant cloud, but the settings shown above should work fine for this tutorial.

Get Ready to ‘vagrant up’

Create a directory where you can store the files Vagrant needs to spin up your environment. I’ll refer to this directory as VAGRANT_ENV.

To build a LEMP stack we need a few things. First is a Vagrantfile file where we identify the base OS, or box, ports, etc. This is a text file that follows Ruby language conventions. Create the file VAGRANT_ENV/Vagrantfile with the following contents:

# -*- mode: ruby -*-
# vi: set ft=ruby :
# Vagrantfile API/syntax version. Don't touch unless you know what you're doing!
Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  # All Vagrant configuration is done here. The most common configuration
  # options are documented and commented below. For a complete reference,
  # please see the online documentation at vagrantup.com.
  # Every Vagrant virtual environment requires a box to build off of.
  config.vm.box = "ubuntu/trusty64"
  config.vm.provision :shell, path: "bootstrap.sh"
  config.vm.network :forwarded_port, host: 4567, guest: 80
  config.ssh.shell = "bash -c 'BASH_ENV=/etc/profile exec bash'"

This file chooses a 64 bit trusty version of Ubuntu, forwards port 4567 on the host machine to port 80 on the guest machine and identifies a bootstrap shell script, which I show next.

Create VAGRANT_ENV/bootstrap.sh with the following contents:

#!/usr/bin/env bash
#accommodate proxy environments
#export http_proxy=http://proxy.company.com:8080
#export https_proxy=https://proxy.company.com:8080
apt-get -y update
apt-get -y install nginx
debconf-set-selections <<< 'mysql-server mysql-server/root_password password secret'
debconf-set-selections <<< 'mysql-server mysql-server/root_password_again password secret'
apt-get -y install mysql-server
apt-get -y install php5-fpm php5-mysql
sed -i s/\;cgi\.fix_pathinfo\s*\=\s*1/cgi.fix_pathinfo\=0/ /etc/php5/fpm/php.ini
service php5-fpm restart
mv /etc/nginx/sites-available/default /etc/nginx/sites-available/default.bak
cp /vagrant/default /etc/nginx/sites-available/default
service nginx restart
echo "<?php phpinfo(); ?>" > /usr/share/nginx/html/info.php

This script executes a sequence of commands from the shell as root after provisioning the new server. This script must run without requiring user input. It also should accommodate any configuration changes and restarts necessary to get your environment ready to use.

More sophisticated tools like Ansible, Chef and Puppet can also be used.

you may have noticed that the above script expects a modified version of nginx’s default configuration. Create the file VAGRANT_ENV/default with the following contents:

server {
	listen 80 default_server;
	listen [::]:80 default_server ipv6only=on;
	root /usr/share/nginx/html;
	index index.php index.html index.htm;
	server_name localhost;
	location / {
		try_files $uri $uri/ =404;
	error_page 404 /404.html;
	error_page 500 502 503 504 /50x.html;
	location = /50x.html {
		root /usr/share/nginx/html;
	location ~ \.php$ {
		fastcgi_split_path_info ^(.+\.php)(/.+)$;
		fastcgi_pass unix:/var/run/php5-fpm.sock;
		fastcgi_index index.php;
		include fastcgi_params;

vagrant up

Now it’s time to run ‘vagrant up‘. To do this, open a console window and navigate to your VAGRANT_ENV directory, then run ‘vagrant up’.


If this is the first time you have run ‘vagrant up’, it may take a few minutes to download the ‘box’. Once it’s done, you should be ready to visit your PHP page rendered by nginx on a local virtual machine created and configured by Vagrant: