Docker MySQL Containers: Learn How to Run MySQL on Docker

MySQL is the most commonly used open source relational database management system. In this tutorial we will learn how to run MySQL Containers on Docker Engine.

• Download Docker MySQL image.
• Start a New MySQL Container.
• Docker MySQL Environment Variables.
• Link with another docker container.
• Start MySQL container with Remote Access.
• Using a custom MySQL configuration file.
• Using a custom Data Directory.
• Backup Databases from the Host.

Download Docker MySQL Image

To run MySQL containers we need a MySQL Image. We can get the latest version of MySQL image with docker pull command.

docker pull mysql:latest

Or we can download a specific version of the mysql docker image, Following command will download the "MySQL 5.7" image from the Docker MySQL repository.

docker pull mysql:5.7

To find available versions go to docker hub and search for the MySQL.

Start a New MySQL Container

Docker MySQL Containers are very straight forward. Starting a new server instance is very simple.

Following Example will start a new MySQL instance called "mysql_server" from the "MySQL 5.7" image.

docker run -d --name mysql_server -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

Note that most important option is the MYSQL_ROOT_PASSWORD, We also use the -d option to run the container as a background process.

You can get an interactive shell session to the MySQL instance with the docker exec command.

docker exec -it mysql_server bash

To get direct interactive session to mysql console from the host, type:

docker exec -it mysql_server mysql -uroot -p

Docker MySQL Environment Variables

When we start a new instance, we can adjust the configuration of the MySQL instance by passing one or more environment variables with docker run command.

Following Table shows environment variables used with Docker MySQL.

• MYSQL_ROOT_PASSWORD - Set Password for the MySQL root user. This variable is mandatory.
• MYSQL_USER, MYSQL_PASSWORD - Create new MySQL user and Set user password. This is optional.
• MYSQL_DATABASE - Create a New Database on startup. This is optional. If a user is created, the user will be granted superuser access to the database.
• MYSQL_ALLOW_EMPTY_PASSWORD - If set to "yes", New container can start with empty root password.
• MYSQL_RANDOM_ROOT_PASSWORD - If set to "yes", a random initial password for the root user will be created.

In the Following example, we create a new container with a new MySQL database called "test_db".

docker run -d --name mysql_server -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=test_db  mysql:5.7

Start a container with a new MySQL user:

docker run -d --name mysql_server -e MYSQL_ROOT_PASSWORD=123456 -e MYSQL_DATABASE=test_db -e MYSQL_USER=myuser -e MYSQL_PASSWORD=mypass  mysql:5.7

Start a mysql container with an empty root password:

docker run -d --name mysql_server -e MYSQL_ALLOW_EMPTY_PASSWORD="yes" -e MYSQL_ROOT_PASSWORD="" mysql:5.7

Link with another docker container

Often you will need to link your mysql instance with other containers. For example, following command will start a new Apache HTTPD container with a link to a mysql instance called "mysql_server".

docker run -d --name apache-web --link mysql_server:mysql httpd:latest

The apache container now has access to the mysql server inside mysql container. When connecting to the mysql server, mysql host should be the Name or ID of the MySQL container.

mysql -p -h mysql_server

Start a MySQL container with Remote Access

In Order to allow remote access to the MySQL container, we need to map host port 3306 with container port 3306, when we create a new instance.

docker run -d -p 3306:3306 --name mysql_server -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

From the host machine you can access the MySQL Console with 127.0.0.1 as the MySQL host.

mysql -p -h 127.0.0.1

Note that port mapping should be done when a container is created.

Using a custom MySQL configuration file

The startup configuration file is /etc/mysql/my.cnf file, and that file in turn includes any files found in the /etc/mysql/conf.d or /etc/mysql/mysql.conf.d directory that end with .cnf extension.

When we start a new container, we can replace the configuration directory with a directory on the host machine. For example, Create a directory called /var/config on the host machine.

mkdir /var/config

Then create the mysqld.cnf file inside the /var/config directory and add any mysql configurations to the mysqld.cnf file.

touch /var/config/mysqld.cnf

Now we can mount /var/config directory as /etc/mysql/mysql.conf.d inside the mysql container.

docker run -d --name mysql_server -v /var/config:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

As per the above example, we use -v flag to replace /etc/mysql/mysql.conf.d directory with local /var/config directory.

Using a custom Data Directory

Same way we can also mount the MySQL data directory from the host machine. Default data directory on docker mysql is "/var/lib/mysql".

First, create the data directory on the host computer:

mkdir -p /mysql/data

Then start the server instance with the local data directory:

docker run -d --name mysql_server -v /mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7

Backup Databases from the Host

With docker exec command we can create database dumps from the host machine.

Following command will dump 'example_db' database from the "mysql_server" instance.

docker exec mysql_server sh -c 'exec mysqldump -u root -p"$MYSQL_ROOT_PASSWORD" example_db' > example_db.sql

The following command will dump all databases from the mysql container.

docker exec mysql_server sh -c 'exec mysqldump --all-databases -u root -p"$MYSQL_ROOT_PASSWORD"' > all_databases.sql