Run MySQL in Docker Container

Docker changed my approach to software development for many aspects. One of them is how I maintain 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 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 my process and it can be combined easily with CI/CD tools. We can easily run Docker as part of a Jenkins pipeline or we can run Docker with Apache Maven when building a Java application or any 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)

Run integration tests with Maven

So far we have seen how to execute MySQL server using Docker command. However, most developers don't need to use Docker command directly. Java developers, for example, can use Maven for creating Docker images and containers.

At the time of writing, there are two plugins for Docker: Fabric8 Docker Maven Plugin and Spotify Docker Maven Plugin. I'll show you how to use the Fabric8 plugin to create and populate a MySQL database, before executing the integration tests, and destroy it, when the tests are completed. We want to create a database and a user with some password, and we want to populate the database using a SQL script.

All we need to do is configure the plugin in order to start a database during the pre-integration phase, and stop it during the post-integration phase. Also we need to pass some environemt variables and mount a folder containing our SQL script. The POM file should contain a configuration like:

<plugin>
	<groupId>io.fabric8</groupId>
	<artifactId>docker-maven-plugin</artifactId>
	<version>${docker-maven-plugin.version}</version>
	<configuration>
		...
	</configuration>
	<executions>
		<execution>
			<id>start-mysql</id>
			<phase>pre-integration-test</phase>
			<goals>
				<goal>start</goal>
			</goals>
			<configuration>
				<showLogs>true</showLogs>
				<images>
					<image>
						<alias>database</alias>
						<name>mysql:8.0.2</name>
						<run>
							<ports>
								<port>${database.port}:3306</port>
							</ports>
							<env>
								<MYSQL_DATABASE>mydb</MYSQL_DATABASE>
								<MYSQL_USER>myuser</MYSQL_USER>
								<MYSQL_PASSWORD>mypass</MYSQL_PASSWORD>
								<MYSQL_ROOT_PASSWORD>myrootpass</MYSQL_ROOT_PASSWORD>
							</env>
							<volumes>
								<bind>
									<volume>${project.basedir}/scripts:/docker-entrypoint-initdb.d</volume>
								</bind>
							</volumes>
							<wait>
								<log>/usr/sbin/mysqld: ready for connections. Version: '8.0.2-dmr'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306</log>
								<time>30000</time>
							</wait>
						</run>
					</image>
				</images>
			</configuration>
		</execution>
		<execution>
				<id>stop-mysql</id>
				<phase>post-integration-test</phase>
				<goals>
					<goal>stop</goal>
				</goals>
		</execution>
	<executions>
</plugin>
						

Please notice there is an element <volume> which provides the SQL script that is executed to populate the database, and there is an element <wait> which is required to ensure the database server inside the container is up and running before executing the integration tests.