A whale jumping out of the water

Using PHP and MySQL with Docker

Recently, I’ve been do­ing some work with con­tain­ers. For the unini­ti­ated, a con­tainer is like a vir­tual ma­chine be­cause your ap­pli­ca­tion is run in an iso­lated, con­sis­tent en­vi­ron­ment. Yet, this en­vi­ron­ment is more light­weight than a vir­tual ma­chine. It uti­lizes the un­der­ly­ing op­er­at­ing sys­tem rather than run­ning an en­tire op­er­at­ing sys­tem for each ap­pli­ca­tion. Thus, con­tain­ers are cheap-ish to spin up and easy to share.

The most com­mon plat­form for cre­at­ing con­tain­ers is Docker so that’s what I’ll be us­ing here. You can in­stall Docker from this link. Warning though: If you have Windows Home Edition, you will need Windows Subsystem for Linux Version 2 to run the lat­est ver­sion of Docker. Those on *nix sys­tems should be able to just in­stall the soft­ware.

Database Setup

Let’s start by set­ting up the data­base. For this part of the tu­to­r­ial, I’m as­sum­ing you have some knowl­edge of SQL; how­ever, you should just be able to blindly copy and paste in the text. I’m putting all my work in a folder called tu­to­r­ial. Also, you will have to make a few more fold­ers and files to fol­low along to the end—just a fore­warn­ing.

To be­gin, we are go­ing to need an im­age to form the base of our con­tainer. In Docker, an im­age is ba­si­cally a file sys­tem and some set­tings. Each con­tainer will get its own vir­tual file sys­tem sep­a­rate from your ac­tual one, and it is re­made fresh every time a con­tainer is spun up. If you want your con­tain­ers to ac­cess per­sis­tent stor­age or mount to your ac­tual filesys­tem, you'll have to look into vol­umes. I'm not go­ing to use a vol­ume here, but it's some­thing you should be aware of.

Now, Docker man­ages the life­cy­cle of a con­tainer, but it also acts as a pack­age man­ager for im­ages. Thus, we don’t have to make our own from scratch. Instead, we can use and ex­tend ex­ist­ing im­ages. For our needs here, we’ll ex­tend a ba­sic MySQL im­age, which can be found on the Docker Hub web­site. The web­site will give some ba­sic in­for­ma­tion about the im­age; we mostly care about the im­age name, the avail­able tags, and the en­vi­ron­ment vari­ables.

To cre­ate a new im­age, we’ll need to cre­ate a YAML file, com­monly known as a Dockerfile. This file con­tains var­i­ous com­mands to cre­ate a con­tainer. Don't worry—I’m go­ing to pro­vide the files needed for our ad­ven­ture, in­clud­ing this Dockerfile, with some ex­plana­tory com­ments. Also, the Docker web­site pro­vides a ref­er­ence if you would like to go into more de­tail. This guide only pro­vides a rel­a­tively high-level overview of Docker.

Here is the Dockerfile it­self.

FROM mysql:8.0

# Set an insecure password
ENV MYSQL_ROOT_PASSWORD=example

# Copy over our SQL queries
COPY ./mysql/init.sql /init.sql

# Startup MySQL and run the queries
CMD ["mysqld", "--init-file=/init.sql"]

And you’ll need the req­ui­site SQL file. I’ve put both of these files in a folder called mysql in our tu­to­r­ial di­rec­tory.

CREATE DATABASE app;
USE app;

CREATE TABLE message (
id INT NOT NULL AUTO_INCREMENT,
message VARCHAR(50) NOT NULL,
PRIMARY KEY(id)
);

INSERT INTO message (message)
VALUES
("Hello World"),
("A second message"),
("J.Cole went double platinum with no features");

Some Dockerfile Commands

I’ll go ahead and ex­plain a few of the com­mon Dockerfile com­mands.

The FROM com­mand tells Docker which im­age to ex­tend. Here, we are build­ing on top of the MySQL im­age with tag 8.0. If you're feel­ing ad­ven­tur­ous, try chang­ing the tag; the tu­to­r­ial should still work.

The ENV com­mand sets—acts shocked—an en­vi­ron­ment vari­able. Environment vari­ables, broadly speak­ing, are dy­nam­i­cally set val­ues that af­fect the be­hav­ior of run­ning processes. In this case, we are set­ting the pass­word for the data­base. Thus, if you want to log in with an ex­ter­nal tool like MySQL Workbench, use the pass­word ex­am­ple and the de­fault user­name of root. You won’t need any pro­grams other than Docker to com­plete this tu­to­r­ial, but data­base tool­ing should work with the con­tainer­ized data­base.

The COPY com­mand moves files from your ac­tual file sys­tem to the con­tainer’s vir­tual filesys­tem. Keep in mind, con­tain­ers are im­mutable so we have to copy over files or mount vol­umes dur­ing the cre­ation step. So, what are we copy­ing over here? It’s just a SQL file to pop­u­late a table. Obviously, out of the box, the data­base will be a blank slate. I went ahead and added some records as part of the data­base ini­tial­iza­tion process. This tech­nique is prob­a­bly not how you ac­tu­ally do this in a real en­vi­ron­ment, but it keeps the tu­to­r­ial sim­ple.

Finally, the CMD com­mand tells the con­tainer what to run once the con­tainer starts up. Here, I am over­rid­ing the mysql’s im­age de­fault be­hav­ior be­cause we need to add a flag to the com­mand. The –init-file flag al­lows us to run a file con­tain­ing SQL queries af­ter the data­base ini­tial­izes, which is ex­actly what we want.

This file by it­self does noth­ing. We'll have to build the Dockerfile into an im­age and run it—but that'll come later.

PHP Setup

Cool—we have a data­base Dockerfile. Let’s do some­thing with it by cre­at­ing a sim­ple PHP page, which will in­volve spin­ning up a sec­ond con­tainer. This pat­tern will get fa­mil­iar. You will spend some amount of time con­fig­ur­ing Docker con­tain­ers for all the com­po­nents of your ap­pli­ca­tion.

The PHP file it­self cre­ates an html table dis­play­ing the con­tents of the data­base table—pretty much in a one-to-one fash­ion. Most of this file is html. You don’t have to un­der­stand all the de­tails of the PHP part, but it’s just query­ing our data­base.

<!doctype html>
<html lang="en">

<head>
<meta charset="utf-8">
<title>Docker Tutorial</title>
<meta name="description" content="Learn how to use Docker with PHP">
<meta name="author" content="Matthew Parris">
</head>

<body>
<h1>Docker Tutorial</h1>
<div class=".db-table">
<table>
<tr>
<th>Id</th>
<th>Message</th>
</tr>
<?php
$user = 'root';
$pass = 'example';

try {
$dbh = new PDO('mysql:host=db;port=3306;dbname=app', $user, $pass);
foreach ($dbh->query('SELECT * from message') as $row) {
$html = "<tr><td>${row['id']}</td><td>${row['message']}</td></tr>";
echo $html;
}
$dbh = null;
} catch (PDOException $e) {
print "Error!: " . $e->getMessage() . "<br/>";
die();
}
?>

</table>
</div>
</body>

</html>

An in­ter­est­ing thing to point out here is the data­base con­tainer's host­name. It's the same name as the con­tainer. Thus, you don't have to fig­ure out what IP the con­tainer has been as­signed. Some DNS magic is mak­ing our lives eas­ier be­hind the scenes.

Now, let's look at the Docker file. It's sim­i­lar to the one from ear­lier—so I'm not go­ing to re­view the syn­tax again. We are go­ing to build from the base im­age found at this link. One thing to note is that we have to in­stall a PHP ex­ten­sion for PDO to es­tab­lish a con­nec­tion to our MySQL data­base. Luckily, the base PHP im­age pro­vides some util­ity scripts to work with these ex­ten­sions. It's an easy thing to im­ple­ment but could be eas­ily over­looked.

FROM php:7.4-cli

# Move our PHP file into the container
COPY ./php/index.php /usr/src/app/index.php

# Make things easier if you shell in
WORKDIR /usr/src/app

# Our PHP will be running on port 8000
EXPOSE 8000

# Install the PDO MySQL extension so we can database
RUN docker-php-ext-install pdo_mysql

# Set up a web server
CMD ["php", "-S", "0.0.0.0:8000"]

To note, I’ve placed both this Dockerfile as well as the PHP file in a folder called php. You'll need to be care­ful about the di­rec­tory struc­ture be­cause it will mat­ter for the next step. Things will crash if you screw up 🙃

Docker Compose

Phew—we're al­most there. We have our im­ages and could im­per­a­tively use the Docker CLI to run the con­tain­ers and net­work them to­gether. But that's no fun and is a pain to man­age. Instead, I'm go­ing to scrib­ble up a Docker Compose file.

Docker Compose is an ab­strac­tion on top of Docker to fire up a set of con­tain­ers, vol­umes, net­works, and other en­vi­ron­ment stuff. In other words, it is ba­si­cally a de­clar­a­tive way of in­ter­fac­ing with Docker. We can cre­ate a sin­gle YAML file to spin an en­vi­ron­ment up and down.

version: '3.7'
services:
db:
build:
context: .
dockerfile: ./mysql/Dockerfile.yaml
image: tutorial-db
restart: always
ports:
- 3306:3306
app:
build:
context: .
dockerfile: ./php/Dockerfile.yaml
image: tutorial-php
restart: always
ports:
- 8000:8000

Run the Containers

Awesome. We now have every­thing we need. Let's run this thing by telling Docker Compose to spin up our con­tain­ers.

docker-com­pose up -d

You should then be able to nav­i­gate to lo­cal­host:8080 and see the page. It's not glo­ri­ous, but there should be an html table with some rows pop­u­lated.

Of course, for more re­al­is­tic sce­nar­ios, you aren't go­ing to have a sin­gle PHP page that con­nects to a pre-pop­u­lated data­base. Your app will likely be split into com­po­nents run­ning as their own mi­croser­vices. Alternatively, you might be tran­si­tion­ing a mono­lithic ap­pli­ca­tion over to a con­tainer. These use cases are ob­vi­ously more com­pli­cated than this silly ex­am­ple, but the point was to fo­cus on Docker. Many of the con­cepts used here will be ap­plic­a­ble to larger pro­jects. Anyway, I hope you learned some­thing.