How to run MySQL in Docker?

By | November 15, 2020

Getting MySQL up and running in Docker is very easy and fast but there are a few subtle things to note. Firstly there are 2 apparent options.

docker run mysql

or

docker run mysql/mysql-server

These handful of words are deceptively simple but you really need to understand what is happening under the covers.

docker run mysql

Firstly, the docker run mysql command will most likely return this 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

The password

This is because the command is incomplete. You need to add the -e flag which is the environment variable. From the docs, This variable is mandatory and specifies the password that will be set for the MySQL root superuser account. This is the command you want to run:

docker run -e MYSQL_ROOT_PASSWORD=password mysql

The problem hassle with this is that you render the command line useless because you are running mysql in the foreground and need to open a new terminal to continue issuing commands.

To gain control of the terminal you need to open another terminal and issue the command:

docker ps //to get the container ID
docker stop <container_ID>

The -d flag

A better option is to run in detached mode by adding the -d flag. This runs the process in the background (instead of the foreground) allow you to continue entering commands into the same screen.

docker run -d -e MYSQL_ROOT_PASSWORD=password mysql

Given your container a name

You will have noticed that your containers are given weird and wonderful names such as hopeful_pare or youthful_bohr. You can change this by adding a –name flag. Yes it is a double dash and not single dash because there is no single character “shortcut”.

docker run --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql
From the command line
From Docker dashboard

An older version

What if you want to install a different version? Simply add the version (actually officially called the tag) after the mysql keyword like so:

docker run --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql:5.6
docker run --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql:5.7.32

In actual fact, mysql defaults to mysql:latest so I could have been more explicit above!

Connecting to it

Now that you have MySQL running on a Docker container, how do you connect to it?

docker exec -it <my_image_name> bash

This takes you into the docker container where you can run standard command line like ls.

docker run -p 3306:3306 --name memories -e MYSQL_ROOT_PASSWORD=password -d mysql:5.6

Then connect with:

mysql -u root -p

where the password is “password” that we set above.

Connecting with another application

What if you want to use phpMyAdmin or MySQL workbench to connect? Easy. You just have to open the port from the container to the outside world.

Just add the port flag (-p) like so:

docker run -p 3306:3306 --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql

Note that I can’t get this to work using on MySQL 8.0 (aka mysql:latest) but it works on v5.6. In other words, I can connect with MySQL Workbench if I run:

docker run -p 3306:3306 --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql:5.6

Connecting with phpMyAdmin

If you have phpMyAdmin running locally already, then you can connect with username: root, password: password, and 127.0.0.1:3306. But, why not spin up phpMyAdmin in another container and connect to it that way? Cool right?

docker run --name myadmin -d --link seanMySQL:db -p 8080:80 phpmyadmin

If you get this error:

docker: Error response from daemon: Ports are not available: listen tcp 0.0.0.0:8080: bind: address already in use.

Trying running this command to figure out what else might be on the same port.

sudo lsof -i:80

This is what you should see.

Does order of Docker arguments matter?

Do both these statements execute correctly?

docker run -p 3306:3306 --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql
docker run --name seanMySQL -e MYSQL_ROOT_PASSWORD=password -d mysql -p 3306:3306

The last one does not because the port flag needs to be after the run as options. The format is:

docker run [OPTIONS] IMAGE [COMMAND] [ARG...]

which means the options first, then the image (which is mandatory by the way) and then the arguments if any.

Leave a Reply

Your email address will not be published. Required fields are marked *