Learn more >
Erin McKean | Published January 22, 2018
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:
To follow this How-to guide, you need to have:
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).
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 (test-mysql).
There are a few different ways you can add data to a docker container running MySQL.
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.
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).
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!
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
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).
If you don’t enjoy running long commands in the terminal, you might prefer to use a docker-compose.yml file.
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.
docker-compose up -d
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:
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 -g loopback-cli
Wait for npm to finish installing, then cd into the directory name you chose.
Now create a connection to MySQL with lb datasource:
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 pwd.
Next we’ll create our model with lb model:
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 docker ps:
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.
To run your api, just run the Node app that LoopBack created with node .:
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.
March 19, 2019
January 9, 2019
Get the Code »
Back to top