Running MySQL Server in Docker

Introduction

In order to facilitate faster development on some of my projects I’ve been trying out running databases in docker containers.

This is advantageous because it means I don’t have to install anything else on my machine and I can spin up and shutdown the database at will, ensuring that it does not consume any unnecessary resources.

Anyway, I was facing some issues connecting to these databases, so I thought I’d write a guide to refer back to incase I need to do this again in the future.

Setup

Okay so the first step should obviously be downloading the MySQL Server image from DockerHub.

docker pull mysql/mysql-server:latest

This will pull the latest docker image that is maintained by Oracle.

Then we will need to spin up a new container using this image.

docker run --name=mysql_server_1 -p 3305:3306 -d mysql/mysql-server:latest

Note: For some reason when I run the command as docker run --name=mysql_server_1 -d mysql/mysql-server:latest -p 3305:3306 (Swapping the -p and -d flags) the container fails to run.

The -p flag is important as this will bind the host machine’s port to the port on which the database is available on the machine.

I chose port 3305 because it is possible that some machines will already be running MySQL already on this port.

We then need to get the root password for the container’s root user.

We can do this by filtering the logs, which will have previously output the automatically generated password.

docker logs mysql_server_1 2>&1 | grep "GENERATED ROOT PASSWORD"

Login to Database

We now need to log into the database and make some changes.

Connect to the container either via the CLI or using the Exec tab on Docker Desktop.

From CLI:

docker exec -it mysql_server_1 mysql -uroot -p

From Docker Desktop

mysql -p

Then copy and paste the password we got from before.

Because the password we are using has been autogenerated we must reset it in order to interact with the database.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password_goes_here';

Now, we should be able to run queries on the database as normal.

Test it out by running the following.

SHOW DATABASES;

Headaches

Okay so at this stage we have a database and we can interact with it without issues through Docker itself.

But what we probably want to do is connect to it using a MySQL client or separate application.

But the issue is that running the following command:

mysql -h localhost -P 3305 -u root -p

Will return to us the following error:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

It’s the correct host, the correct port and the correct user, but for some as yet unknown reason we aren’t able to connect.

So after some research it turns out that we need to specify a protocol to use when connecting to the database because is running in a container, in this case TCP.

mysql -h localhost -P 3305 --protocol=tcp -u root -p

But this only gives us a new error to contend with.

ERROR 1130 (HY000): Host '172.17.0.1' is not allowed to connect to this MySQL server

We can investigate this issue by checking the hosts of our users in the database.

SELECT host
FROM mysql.user
WHERE User = 'root';

In my case this only returned one result, localhost, meaning that the root user was only allowed to connect from within the docker container.

The solution is that we need to create a new root user for our host machine’s IP, and grant it the same privileges.

CREATE USER
'root'@'ip_address'
IDENTIFIED BY 'password_goes_here';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'ip_address';

Note: The host IP address that we should use is contained in the output of the previous error (Host '172.17.0.1').

FINALLY We can connect to the database from our MySQL client without issues.

Try running this again and you should be greeted with the MySQL shell.

mysql -h localhost -P 3305 --protocol=tcp -u root