This tutorial explains the step-by-step method to set up and use the MySQL Docker container with examples:
Docker is a container orchestration platform that enables us to run an image of any application (in this case MySQL server) on a host system (which could behave in any OS installed – example: Linux, Windows, MacOS, etc)
Using MySQL with docker would help a developer or anyone wanting to learn or experiment with MySQL without going through the hassle of any installation or configuration.
Running through docker just requires you to have docker installed in your machine, and then you can use the readily available docker images of MySQL.
=> Click here for the complete MySQL tutorial series
Table of Contents:
Understanding MySQL Docker
Running MySQL with Docker containers is a widely used mechanism, especially for the microservices architecture where usually each microservice works with its own database/tables and the isolated integration/component tests can be executed on the containerized versions of the databases.
MySQL Container Concepts
Think of docker container as a lightweight virtual machine that can run independently of any other application (and can live within its own context). Any application – for example, MySQL, in this case, can be bundled together as a container and can be deployed on a local or remote host.
Now this container is a mini-ecosystem and has all the components bundled together that would be required for MySQL to execute properly.
Prerequisite: Installing Docker
For creating MySQL as a docker container, the host machine should have docker installed.
>> Recommended reading for installing Docker=> Installation And Introduction To Docker.
To validate the docker installation, simply run the below command:
docker version
If the output displays like below, this means the docker installation was successful.
Steps To Setup MySQL With Docker
#1) Pull the desired docker image
Docker images for MySQL can be pulled from the docker hub. We will be pulling the latest image of mysql-server from the docker hub in our example. Docker container images are published through official sources on docker hub.
>> Click here to find all available docker images/versions of MySQL
Unless a specific version is required, we can always specify a tag as ‘latest’ which will pull the latest available version of MySQL from the docker hub registry commonly called docker hub.
Syntax to pull docker image:
docker pull mysql:latest
Once executed, you will see the output as below:
Once the container image is fully downloaded on the host, you can see all the downloaded images by running the below command.
docker image ls
#2) Start the container with the pulled image
Now, let’s start a container using the downloaded docker image for MySQL.
By default, if no root password is specified along with the docker command while creating the container, there would be a random password created for the root user and to fetch the root password, we would need to access the logs of the docker container.
a) Let’s first run the container with the downloaded image
Syntax:
docker run --name {optional-name-for-the-container} -d {name of the image tag}
In the above syntax,
- –name is an optional field and can be used to specify a friendly name with which the container can be referred to later. If this is not specified – Docker assigns a randomly generated name to the container.
- -d represents the name of the image with which the container is built. If the image was pulled earlier, the same will be reused. If the image is not existing on the local system, then this will first pull the image from the docker hub and then create a container.
Example: We will create a container named learn-mysql-docker from the previously used image tag – mysql:latest
docker run --name learn-mysql-docker -d mysql:latest -e MYSQL_RANDOM_ROOT_PASSWORD=1
Notice the use of -e flag, which is nothing but specifying an environment variable. Here we have specified the value for MYSQL_RANDOM_ROOT_PASSWORD=1
This means that while starting up the MySQL server, the root password would be set to a randomly generated String (we will later see in the next section, how we can retrieve that dynamically generated random password through Docker container logs)
Note: There are other options as well for the default setting of the root password.
The available options are as follows:
- Specifying a custom password: Use an environment variable named MYSQL_ROOT_PASSWORD and set it to the desired password for the root user.
- Getting a Random password created: Use an environment variable named MYSQL_RANDOM_ROOT_PASSWORD and set the value to 1.
- Setting a blank password for root user: Use environment variable named MYSQL_ALLOW_EMPTY_PASSWORD and set this to 1. This will set a blank/null password for the root user.
There’s another environment variable available, which is MYSQL_ONETIME_PASSWORD. This, when set to true (1), will expire the root user password and expect the user to create a new password after the first login. (Please note that this env variable is applied only in MySQL versions later than 5.6)
Output:
Once the above command is executed, you will see docker displaying the ID of the created container.
For example:
Let’s validate whether the container was created successfully by listing the available docker containers on the host and filtering the container name that we had specified:
docker container ls -a | grep "learn-mysql-docker"
You will see an output as below (if the container was successfully started)
b) Fetch the root password: In order to connect with the root user, we would need to enter the password. Since we had mentioned the env variable to set/allow a random password, we will be fetching this password from the Docker container logs.
Execute the command below to fetch the logs and the randomly generated password:
docker logs learn-mysql-docker | grep "GENERATED ROOT PASSWORD:"
Output:
As highlighted in the above image, you can see a random password was generated. In further sections, we will use this password to log in with the root user.
c) How to start a docker container with the desired password?
If we don’t want a random password to be generated when the container is started, we can specify a different environment variable named MYSQL_ROOT_PASSWORD and specify the value of this variable as the desired password we want to set for the root user.
In this case, the command to create the container is as below:
docker run --name learn-mysql-docker -d mysql:latest -e MYSQL_ROOT_PASSWORD=`password123`
#3) Log in to the created container
In order to do so, we will use the created container name (or id) and login to the bash shell application within the container.
docker exec -it learn-mysql-docker bash
If the container is up and was successfully created, after running the above command you will see a bash shell inside the container.
#4) Start the MySQL shell
Once logged in to the container, we will start the MySQL shell with the root user and specify the obtained randomly generated password.
#5) Execute MySQL command examples
Once we are logged in with the root user, let’s now try to execute some simple commands to ensure everything is working fine.
a) SHOW all databases: Execute the below command:
SHOW DATABASES;
b) Change the password of the root user to ‘Password’ – Execute the command below and retry logging in with the root user (against the new password i.e. Password, which is set by this command)
Connecting To MySQL Docker From Host Machine
In the above section, we have logged into the MySQL shell that was inside the docker container. We can also expose the port from MySQL container in the docker to a local port and use that for local development to connect to MySQL.
Let’s try to understand the different steps involved here:
#1) Start the container and map to a local port. In this step, we will map the port from the docker container to a port on localhost which would be used to connect to MySQL.
a) First stop/remove the container we have created in previous sections. This is required to avoid confusion and getting port address-related errors.
Commands that need to be executed are:
docker stop learn-mysql-docker
Once the container is stopped, we can remove the container using the command below:
docker rm learn-mysql-docker
b) Once the container is removed, we can start a new container with the same MySQL image and also map the docker port to local host port.
This can be done by running the command below:
docker run -p 13306:3306 --name mysql-docker-local -e MYSQL_ROOT_PASSWORD=Password -d mysql:latest
Let’s try to understand the above command
- -p represents port and 13306:3306 represents mapping port 3306 from a docker container to 13306 on the localhost (or the host machine where the container is present).
- –name is the friendly name for the docker container that we are initializing.
- -e represents Environment variables. We are setting MYSQL_ROOT_PASSWORD to ‘Password’ which is nothing but the password for the root user.
- -d represents the name of the docker image. In this case, mysql:latest would be the image that would be used to create the container.
c) Ensure that there are no errors once the above command executes. If everything goes well, the above command would print a generated container ID.
Refer to the image below:
#2) Once the container is successfully started, connect to MySQL from localhost -using command line or GUI tools like MySQL workbench.
a) Let’s run/connect to the MySQL database through the command line (Prerequisite – please ensure that localhost has MySQL Command-line tools installed as well)
mysql --host=127.0.0.1 --port=13306 -u root -p
Once the command is executed, you will see that you are connected to docker based MySQL instance. You can try running any command like:
SHOW DATABASES;
And ensure that the setup is working fine.
b) To connect through MySQL workbench, you can simply specify the hostname as localhost and port no. as the mapped port which was used.
Set up a new connection with localhost and port as 13306.
Store password in keychain as shown in the below image.
Then, connect to the database.
Setting Up MySQL Docker Using Docker Compose File
Docker compose is the details required to set up a docker image/container in a template form, which is in YAML format. This makes it easy to share and check it into version control systems like TFS or GIT, where anyone working on the project can simply take this file and execute it to set up the required docker container with the required information.
There are a couple of handfuls of utilities and web pages that allow you to create docker-compose files easily.
We can use the one from here and just paste the docker command we have used to create the container.
Refer to the image below:
We can copy the content of the created file and save the contents with the file named docker-compose.yaml in a folder on your desktop/laptop.
Once the file is saved, navigate to the folder (where the file is saved) and just run the below command.
docker-compose up
If the contents of the compose file were correct and the image is available, a new docker container would be started (and could be used in the same way as explained in the previous sections of this article)
Frequently Asked Questions
Q #1) How do I create a database in MySQL Docker?
Answer: MySQL Docker in a container instance can be connected through the command line by logging into the container. Once the connection is established, any SQL commands can be executed.
To connect to MySQL inside the container, here is the syntax:
docker exec -it {container-name or container-id} mysql -u root -p
Once the above command is executed, you will be prompted to enter the database password. And once the password is verified, you can execute any SQL based command.
Q #2) How to expose a particular port from docker for MySQL?
Answer: A port mapping can be specified while the docker container is being created. For example, if you want to map the docker port to a particular port on your localhost, you can use a command like:
docker run -p 13306:3306 --name mysql-docker-local -e MYSQL_ROOT_PASSWORD=Password -d mysql:latest
Here 13306 is the port in the host machine, and 3306 is the port in the container.
Q #3) How to restart/stop and remove a docker based MySQL container?
Answer: There are a couple of handy and useful commands for the docker containers.
- Restart: This command is used to restart a running container.
docker restart {container-name or container-id}
- Stop: This command will temporarily stop the running container (and can be resumed/restarted later).
docker stop {container-name or container-id}
- Remove: This will remove the docker container and any resources associated with that container.
docker rn {container-name or container-id}
Q #4) How to connect to a dockerized MySQL instance from the host machine?
Answer: Once the docker port and localhost ports are mapped, we can connect to the MySQL instance sitting inside the docker from the localhost through the command line or through UI tools like MySQL workbench.
For starting through the command line – MySQL command-line tools should be installed on the host machine as well.
The below command can be used to connect to MySQL instance in docker through the host:
mysql --host=127.0.0.1 --port=13306 -u root -p
Q #5) How can we run a specific version of MySQL using Docker?
Answer: While specifying the docker image (using -d) option, while creating the container, we can specify the available images/versions of the container.
If the latest is specified, it would fetch the latest published image of the given container.
For example, for MySQL we can specify mysql:latest to fetch the latest version and something like mysql:8.0.22 for example to fetch version 8.0.22.
>> Refer to the Docker Hub documentation for MySQL to view the complete list of all the available versions.
Conclusion
In this tutorial, we have learned about setting up a MySQL server using Docker instead of the actual host machine.
Docker is a powerful container orchestration system, which allows a user to host a number of applications as containers and allows them to be used as an isolated system in itself. It also requires minimal setup and provides fast means to avoid all setup hassles with just a couple of commands.
These days, with microservices architecture where different microservices rely on their own databases, deploying MySQL servers as Docker containers have become quite a common practice.
It’s also widely used for any local execution, creating proof of concepts or even for running end to end integration tests in an isolated environment.