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 tutorial, 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


To follow along, you need to have:

Estimated time

To complete this how-to should take about 90 minutes. We estimate 30 minutes for installing the prerequisites and another 60 minutes to follow the steps.


NOTE: This was tested using Node 8.9.1, npm 5.5.1, and macOS Sierra (10.12).

Step 1. 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.

Step 2. Start a Docker container

Let’s create a new Docker container using the mysql-latest official image hosted on Docker Hub. It will download this image from Docker Hub, 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 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

Step 3. 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:


Then load the data, one state at a time:


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, replacing 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 rerun 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'
    image: mysql:latest
      - 8083:3306
      - ./test-sql-2:/docker-entrypoint-initdb.d
      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 auto-magically generate a name for your container.

Step 4. 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

Step 5. 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

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

Open the Explorer URL in your browser.

loopback explorer screenshot

Explore your data.

loopback explorer regex screenshot loopback explorer regex response screenshot

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

Summary and next steps

In this tutorial, we explained 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.

For more Docker coolness, sign up for the weekly newsletter or check out the archives. And you can find more Docker content on IBM Developer — including tutorials, articles, and code patterns.