Use Docker to run a MySQL Database Server for Development

When you need to run a MySQL database as the backend for an application it’s not great to have to install the server directly on your machine for each project so this is a perfect use case for using Docker to spin up a containerised version of MySQL configured how you need it. You can get install instructions for Docker from the Docker documentation at https://docs.docker.com/install/. The Community Edition for your OS is the version you want.

Once installed, to launch a Docker MySQL server the simplest workable version of this would be:

docker run \
-e MYSQL_ALLOW_EMPTY_PASSWORD=true \
-p 3306:3306 \
-d mysql

We can add a couple of additional options to make it easier to identify the container later and create a database to use, so as a starting point you probably want something more like this:

docker run \
--name mysql_demo \
-e MYSQL_ALLOW_EMPTY_PASSWORD=true \
-e MYSQL_DATABASE=mysql_demo \
-p 3306:3306 \
-d mysql

--name name is the name you use in commands like docker stopdocker stop and docker start to interact with a specific container. It is optional, and each container is assign an id which you can find from docker ps so you could use docker stop container_id but obviously it is easier to set a name that makes sense for you.

-e is the option to pass in an env variable into the container, you can use it multiple times if you need to pass several environment vars.

-e MSQL_ALLOW_EMPTY_PASSWORD=true allows you to connect as root and no password.

If you would rather connect to root with a password, you can specify the password to use with -e MYSQL_ROOT_PASSWORD=rootSecret If you are planning on creating and connecting as a different user as outlined next, but want to leave the root user secure you could use -e MYSQL_RANDOM_ROOT_PASSWORD=true

If you would like to create a user, you can use:

-e MYSQL_USER=my_user_name and -e MYSQL_PASSWORD=somethingSecret.

When creating a user, you must specify a password for it and one of the three “root” password options.

-e MYSQL_DATABASE creates this default database, when you create a user it is given superuser privileges to this database automatically.

-p maps a port from the host (your machine) to the docker container. By mapping 3306 on your machine to 3306 inside the container, your MySQL server will appear the same way it would if you were running the service normally.

If you are planning on running multiple MySQL containers at the same time, then you will need to given them different ports, for example -p 3307:3306 on one, -p 3308:3306 on another.

Some docker containers come configured with an EXPOSE option that automatically, but for the MySQL Docker container you need to bind it yourself. -d detaches the process, if you don’t include this the process will run inside your terminal. This can be useful for debugging, as you’ll be able to see all the commands running but if you close the terminal window the process will be killed along with it. Under normal use, detached mode is what you want.

With the user and database settings, you can create additional users and databases as normal once the server has started. These just initial state. However, the advantage of containerisation is that starting new containers is trivial, so it is often better to compose multiple single use containers rather than place all your configurations in one place.

Additional options: Automatically Restart after a reboot

If you want your MySQL contain to automatically restarted when you reboot, you can use the –restart flag. The most predictable is to use –restart always . But you could also use: –restart unless-stopped which is useful if you want to use this container for a while, but then leave it stopped in the future without destroying it. or –restart on-failure which is useful for containers which crash on certain errors – more useful for application servers where you don’t want a bad request to kill the whole server if it throws an exception that crashes it.

If you have a created container, and you decide that you now want it to restart automatically, you can add the restart flag using docker update. For instance using our example docker update –restart always mysql_demo

Interacting with databases on the Docker MySQL server

One gotcha is that you need to connect via the TCP port as no unix socket is available for the containerised mysql server. When connecting to localhost the default method is to use the unix socket so you should specify either mysql -h 127.0.0.1 , mysql -h 0.0.0.0 or mysql —protocol=TCP to prompt the command line client to behave correctly.

If you get an error message like ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) then you need to ensure you’re connecting via the port correctly.

If you’ve bound the server to a different port to 3306 then use -P when connecting. Note the capitalisation.

Cleaning up

To stop the container, run docker stop containerName, and then once stopped if you don’t need it any more you can remove it with Docker rm containerName.

Debugging

When you first start the container, it will take a moment for the database to fully initialise and so even though the container is showing as running you might not be able to connect.

To check the container is running use docker ps -a. The -a option will list all the containers on your system, including stopped ones.

If the container doesn’t start for some reason, you can see get the logs with docker logs containerName or docker logs containerId eg, docker logs mysql_demo might show you have an error like

error: database is uninitialized and password option is not specified You need to specify one of MYSQL_ROOT_PASSWORD, MYSQL_ALLOW_EMPTY_PASSWORD and MYSQL_RANDOM_ROOT_PASSWORD

Setting default collation

You can pass additional config flags to mysqld via the command line so if you want to set the default to utf8mb4 then you can use: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

For example:

docker run \
--name mysql_utf8mb4 \
-e MYSQL_ALLOW_EMPTY_PASSWORD=true \
-e MYSQL_DATABASE=mysql_utf8mb4 \
-p 3309:3306 \
-d mysql \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci