IBM Z Day on Nov. 21: Discover the ideal environment for modern, mission-critical workloads. Learn more

Create a database in a Docker container for local development

You’ve probably heard a lot about Docker as a tool for simplifying deployment of applications, but containers can also make your local development process easier and faster!

Instead of maintaining database installations in your local environment or connecting to test databases in the cloud, use a Docker container! With Docker containers you can have multiple versions of the same database easily accessible and quickly restore from backups to make sure your test data is congruent with production data.

Learning objectives

After completing this guide, you will know:

  • How to create a Docker container running a MySQL database.
  • Four different ways to load data into your containerized MySQL database.
  • How to connect to a MySQL Docker container with a local client.
  • How to connect to a MySQL Docker container with a client in another container.
  • How to use a MySQL Docker container as a local development environment with Loopback.

Prerequisites

To follow this How-to guide, you need to have:

Estimated time

To complete this how-to should take about 90 minutes. We estimate thirty minutes for installing the prerequisites and another sixty minutes to follow the guide.

Steps

Note: This how-to was tested using Node 8.9.1, npm 5.5.1, and macOS Sierra (10.12).

Install Prerequisites

  • To check if Docker has installed correctly, open your terminal and enter docker ps. If you have no running containers you’ll see something like this:

docker ps empty screenshot

  • To check if Node is installed, enter node -v. You should get npm automatically with your Node installation, but to check, try npm -v.

Start a Docker Container

Let’s create a new docker container using the mysql-latest official image hosted on Dockerhub. It will download this image from Dockerhub, so if you have a slow connection this make take a while! In your terminal, enter:

docker run --name test-mysql -p 3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

If all went well, you’ll see a very long number, which is the container’s ID.

docker-run screenshot

If you enter docker ps you’ll also see information about the image that was used to create the container (mysql:latest in this example), when the container was created and how long it’s been running, ports available (should be 3306/tcp) and the name we gave it (test-mysql).

docker ps test-mysql screenshot

Add Your Own Data

There are a few different ways you can add data to a docker container running MySQL.

Connect to your container using a local mysql shell client

When you created your container above, you exposed a port to the local environment (-p 3306). Docker mapped that port for you, and you can find that port by running docker ps.

docker ps test-mysql screenshot

Note the port number and enter the command below, where $PORTNUMBER is the port assigned by Docker (32783 in this case):

mysql -P $PORTNUMBER --protocol=tcp -u root -p

You’ll then have to enter the password you used when creating the container (whatever you entered in place of my-secret-pw).

mysql client screenshot

Now you’re connected to MySQL running in your container! This is helpful if you have a local datafile NOT in SQL format that you want to load into a table.

For example, if you wanted to turn the U.S. Geological Survey Geonames data into a MySQL database, first download the HTML data.

Note: Do a quick search-and-replace to change dates of format ’01/19/1981′ to dates of format ’01-19-1981′. If you want an already-corrected sample file to play with, you can find one here.

Then create a table in the mysql database:

CREATE TABLE geonames (FEATURE_ID INT(10), FEATURE_NAME VARCHAR(120), FEATURE_CLASS VARCHAR(50), STATE_ALPHA VARCHAR(2), STATE_NUMERIC VARCHAR(2), COUNTY_NAME VARCHAR(100), COUNTY_NUMERIC VARCHAR(3), PRIMARY_LAT_DMS VARCHAR(7), PRIM_LONG_DMS VARCHAR(8), PRIM_LAT_DEC DECIMAL(10,7), PRIM_LONG_DEC DECIMAL(11,7), SOURCE_LAT_DMS VARCHAR(7), SOURCE_LONG_DMS VARCHAR(8), SOURCE_LAT_DEC DECIMAL(10,7), SOURCE_LONG_DEC DECIMAL(11,7), ELEV_IN_M INT(5), ELEV_IN_FT INT(6), MAP_NAME VARCHAR(100), DATE_CREATED DATE, DATE_EDITED DATE);

Then load the data, one state at a time:

LOAD DATA LOCAL INFILE $PATH-TO-DATA' INTO TABLE geonames CHARACTER SET utf8 FIELDS TERMINATED BY '|' ;

Replace $PATH-TO-DATA with a full path to wherever the datafile lives on your machine (something like /Users/emckean/datasets/GeoNames/AllStates_20171201/AK_Features_20171201-fixdate.txt).

Once you have data loaded, you can exit your local mysql shell client and create a dump of that table. Here $tablename is the name of the table you want to dump, we could use geonames for instance.

docker exec test-mysql sh -c 'exec mysqldump mysql $tablename -p"$MYSQL_ROOT_PASSWORD"' > /path/to/my/sqlfile/$tablename.sql

You might be thinking “hey, why don’t I just docker commit this container as a new image with my data it, and run that?” Unfortunately, in the mysql base image, the data and configuration directories are set us as docker volumes, which means they are not copied when you do a docker commit. If you really want to create an image with your data baked into it, you can check out this StackOverflow question.

Remember, once you have your container built, you can stop and restart it anytime!

Add your data from a file on your machine

Create a new docker container with a new name:

docker run --name test-mysql-2 -p 3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

You can add data to a running container. Remember to change test-mysql-2 to the name of your new container if you called it anything else!

docker exec -i test-mysql-2 mysql -pmy-secret-pw mysql < /path/to/my/sqlfile/myfile.sql

Add your data when you create a container

Let’s create a new container and have it load a .sql file. (Maybe even the dump you created in a previous step!)

In your terminal, run the following. Replace my/own/datadir with the path to your own .sql file.

docker run --name test-mysql-3 -v /my/own/datadir:/docker-entrypoint-initdb.d -p 3306 -e MYSQL_ROOT_PASSWORD=my-secret-pw -e MYSQL_DATABASE=mysql -d mysql:latest

Run docker ps again to find the port to use to connect, and use your local mysql client to check on your new database.

If you run into errors with your .sql file and need to re-dump and re-load it, delete the entire directory before trying to re-run this command (see this issue).

Use Docker Compose to create your container and add data

If you don’t enjoy running long commands in the terminal, you might prefer to use a docker-compose.yml file.

version: '3'
services:
  mysql:
    image: mysql:latest
    ports:
      - 8083:3306
    volumes:
      - ./test-sql-2:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: pwd
      MYSQL_DATABASE: mysql

Then run docker-compose up -d in the same directory as the docker-compose.yml file. In this example we’ve assigned the port (8083). Docker will automagically generate a name for your container.

Connect to Your Container Using Another Container

If you don’t want to install the mysql shell client on your machine, you can create a container to run the mysql shell client, and connect it to your MySQL container.

In your terminal, enter:

docker run -it --link test-mysql:mysql --rm mysql sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

In the above string, test-mysql is the name of your MySQL container. When you created the test-mysql container you created a root password as an environment variable, and the above command references it (as $MYSQL_ENV_MYSQL_ROOT_PASSWORD).

You should see the mysql prompt:

mysql-container client

Connect to your Container from your Application

LoopBack is an open-source framework for creating APIs quickly and easily with Node.js. You can use LoopBack with just about any database, including MySQL. Creating a full set of CRUD APIs for our new Geonames database is very easy. If you’ve already installed the LoopBack cli (npm -g loopback-cli), enter lb app in your terminal:

lb-app screenshot

Wait for npm to finish installing, then cd into the directory name you chose.

Now create a connection to MySQL with lb datasource:

lb-datasource screenshot

And enter the connection information:

lb-datasource connection information screenshot

The connection string is mysql://root:pwd@localhost:8083/mysql in this case because we are using the container created by docker-compose up -d, which explicitly set port 8083 and the password pwd.

Next we’ll create our model with lb model:

lb-model screenshot

And add properties:

lb-model properties screenshot

If you want a list of all the properties and their types in LoopBack format for this sample dataset, you can find it here.

Check to make sure your MySQL container is up and running with docker ps:

docker ps screenshot

Because we want to use the FEATURE_ID property as our record id, open the model file (geonames-api/common/models/geonames.json) and edit it to add "id": true to the FEATURE_ID model.

FEATURE_ID screenshot

To run your api, just run the Node app that LoopBack created with node .:

run loopback screenshot

And open the Explorer URL in your browser:

loopback explorer screenshot

And explore your data!

loopback explorer regex screenshot loopback explorer regex response screenshot

You can find more information about using regex with LoopBack here.

Summary

In this guide, we learned how to: create a MySQL container with Docker, using both external and in-container data, and how to connect to that container from local clients and local development environments.

Erin McKean