Run MySQL in Docker

Docker has changed how I approach software development under many aspects. One of them is how I install software dependencies on my developer's machine. I use Docker to install and configure my dependencies, usually required for testing my application.

Docker provides a tool for implementing a repeatable process for creating an isolated environment which has the expected configuration. A very common use case is represented by installing a database, such as MySQL or PostgresSQL, creating schemas and users, and populating tables.

Docker helps in automating our process and it can be combined easily with our CI/CD. We can easily run Docker as part of a Jenkins pipeline or we can run Docker with Apache Maven when building any Java or JVM based application.

A very common use case is testing. We can run integration tests against the same artifact we run in production, in the same target operating system, reducing errors caused by software misconfiguration or incompatibility. Another very common problem is configuring a developer's machine. We can distribute Docker images for servers and tools, saving a lot of time when new members join a team.

I want to show how we can use Docker to install, configure and run server applications. I chose MySQL because it is a very popular database server, but a similar approach can be adopted with PostgresSQL or any other server application. The most important aspect to remember is that we can use this approach to create a repeatable process.

Pull MySQL image

MySQL image is available on Docker Hub and we can pull any version we want just passing a tag:

docker pull mysql:latest
docker pull mysql:8.0.3
docker pull mysql:8

Start MySQL server

We can start the server with a non empty root password:

docker run --name some-mysql --restart unless-stopped -e MYSQL_ROOT_PASSWORD=changeme -p 3306:3306 -d mysql:latest

Or we can start the server with a empty root password:

docker run --name some-mysql --restart unless-stopped -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -p 3306:3306 -d mysql:latest

We can check that the server is running and which port it is using:

docker ps -a | grep some-mysql

And we can tail the container's logs in case of problems:

docker logs -f some-mysql

We can start another server just changing the exposed port:

docker run --name another-mysql -e MYSQL_ROOT_PASSWORD=changeme -p 3307:3306 -d mysql:latest

Configure MySQL server

We can mount a configuration file to pass some parameters:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=changeme -v $(pwd)/mysql/conf.d/myconf.cnf:/etc/mysql/conf.d/myconf.cnf -p 3306:3306 -d mysql:latest

Or we can mount a configuration folder if we need:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=changeme -v $(pwd)/mysql:/etc/mysql -p 3306:3306 -d mysql:latest

We can also copy the existing configuration to Docker's host:

docker cp some-mysql:/etc/mysql mysql

Configure logging

We can change the logging driver of our docker container:

docker run --name some-mysql --log-drive syslog --log-opt labels=production -e MYSQL_ROOT_PASSWORD=changeme -p 3306:3306 -d mysql:latest

See documentation for logdriver's configuration options

Connect to MySQL server

We can connect to a running container and use MySQL client to execute SQL statements:

docker exec -it some-mysql mysql -u root -p
docker exec -it some-mysql mysql -u root

Or we can create another container and use MySQL client to execute SQL statements from the second container:

docker run -it mysql:latest mysql -h $(docker inspect --format '{{ .NetworkSettings.IPAddress }}' some-mysql) -u root -p

Execute SQL statements

We can execute SQL commands:

docker exec -i some-mysql mysql -u root --password=changeme -e 'show databases;'
docker exec -i some-mysql mysql -u root -e 'show databases;'

Or we can execute SQL scripts:

docker exec -i some-mysql mysql -u root --password=changeme < script.sql
docker exec -i some-mysql mysql -u root < script.sql

We can also avoid passing the password everytime:

docker exec -it some-mysql mysql_config_editor set --login-path=local --host=localhost --user=root --password
docker exec -i some-mysql mysql --login-path=local -e 'show databases;'
docker exec -i some-mysql mysql --login-path=local < script.sql

Change status and terminate

We can easily stop, start and restart our container:

docker stop some-mysql
docker start some-mysql
docker restart some-mysql

And we can terminate the container when we don't need it anymore:

docker rm -f some-mysql
docker rm -f $(docker ps -a -f name=some-mysql -q)
docker rm -f $(docker ps -a -f name=some-mysql -f status=exited -q)