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.
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.
To follow this How-to guide, you need to have:
- Docker (installation instructions)
- Node and npm (installation instructions)
- mysql shell client (to connect to your container using a local shell client) (installation instructions)
loopback-cli(to follow the example for connecting from Loopback) (installation instructions)
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.
Note: This how-to was tested using Node 8.9.1, npm 5.5.1, and macOS Sierra (10.12).
- 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:
- To check if Node is installed, enter
node -v. You should get npm automatically with your Node installation, but to check, try
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.
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 (
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
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
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
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
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
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
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
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
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
You should see the mysql prompt:
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:
npm to finish installing, then
cd into the directory name you chose.
Now create a connection to MySQL with
And enter the connection information:
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
Next we’ll create our model with
And add properties:
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
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
To run your api, just run the Node app that LoopBack created with
And open the Explorer URL in your browser:
And explore your data!
You can find more information about using regex with LoopBack here.
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.