Docker has popularized a lightweight way of isolating dependencies and achieving portability for our software. It's become a standard practice to deploy software components in Docker containers in the context of Continuous Integration and Deployment (CI/CD). But Docker is also a great tool for setting up your local development environment.

It's probably a good goal to use Docker to make your local development as similar to the production one as possible, and to achieve that, you can dockerize virtually all components of your application. This post only covers serving your database from a Docker container in a way that you can connect to it from your application running locally and your local database client.

At work I recently needed to contribute to an application that uses a MySQL database. For the production database the team used a managed service in the cloud. The local development setup expected that developers run a specific old version of the MySQL server on their machine. In order to keep this development setup nicely separated from other projects, I thought it a good idea to setup the local test database in a docker container running that particular MySQL version.

Let's say we want to run MySQL 5.6 and connect to it from our machine via the standard default port 3306.

docker run -p 3306:3306 --name mysql-server -e MYSQL_ROOT_PASSWORD=admin123 -d mysql:5.6

Wait a few seconds until the server is up and running.

Now we can connect to the server and setup a database for our application. You can either use your local MySQL command line client or the one in the Docker container. Note the --protocol=tcp option when connecting with your local MySQL client. Also, I ran into issues at first because I had a local MySQL server running that was listening to the same port as the Docker one.  To avoid conflicts, you'd either have to reconfigure or shutdown the local server (which is what I did as I didn't need it) or map your Docker container port 3306 under a different host port.

# Connect to MySQL server using the MySQL cli in the Docker container
[email protected]$ docker exec -it mysql-server mysql -uroot -padmin123

# Connect to MySQL server using the MySQL cli on the Docker host
[email protected]$ mysql -hlocalhost -uroot -p123456 --protocol=tcp

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.6.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

mysql> CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> CREATE USER "testuser"@"%" IDENTIFIED BY "password";
mysql> GRANT ALL PRIVILEGES ON test.* to "testuser"@"%";

Passing in SQL statements via a a command line parameter also works.

mysql -hlocalhost -uroot -p123456 --protocol=tcp -e "CREATE DATABASE testdb CHARACTER SET utf8 COLLATE utf8_general_ci;"

Voilà your dockerized MySQL instance that you can connect to from your application. As long as you don't remove the container with docker rm mysql-server, the data will persist across docker stop mysql-server and docker start mysql-server.