Tutorials

Setup MySQL in a Docker Container

If you are new to MySQL or someone who wishes to quickly and effortlessly install MySQL database, then this article is for you. In Setup MySQL in a Docker Container article, we will learn how to setup MySQL with Docker and Docker compose in Linux.

Let’s start by setting up the docker environment to launch the MySQL container.

1. Install Docker

Before spinning up the MySQL docker container, you need to have docker and docker-compose installed in your machine. If you have not installed docker and docker-compose, refer the following guides:

You can run the following commands to check the docker and docker-compose versions you are running with.

$ docker --version
Docker version 20.10.11, build dea9396
$ docker-compose --version
docker-compose version 1.29.2, build unknown

2. Download MySQL Docker Image

Go to the Docker Hub to get the MySQL docker image. The important thing is you have to decide what version of MySQL you are going to run.

Run the following command to pull the MySQL image from the docker hub to your machine.

$ docker pull mysql:latest

Heads Up: It is not necessary to use the tag "latest", by default it will pull the latest image.

To check if the image is available locally, you can run the following command:

$ docker images mysql

Sample output:

REPOSITORY   TAG       IMAGE ID       CREATED       SIZE
mysql        latest    b05128b000dd   12 days ago   516MB

Now the image is ready to spin up the container. You can also skip this step and run the "docker run" command as shown in the next section which will pull the image if not available locally.

3. Launch MySQL Container

Run the following command to spin up the MySQL docker container:

$ docker run --name mysql -p 3306:3306 -v mysql_volume:/var/lib/mysql/ -d -e "MYSQL_ROOT_PASSWORD=temp123" mysql
Launch MySQL Container
Launch MySQL Container

Let’s break down the above command and see what each flag does.

--name → To give a name to your container. If you are not specifying this flag, docker will assign some randomly generated names.

-p → Port mapping. MySQL will listen at port 3306 so we are mapping ports (3306 → 3306) from your host machine to the docker container. It is not necessary for the host port to be 3306, it can be anything that is available to be used.

Run the following netstat command in your local machine to check if the mapped port is listening or not once the container is launched.

$ netstat -tlnup | grep -i 3306

Sample output:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      168870/docker-proxy 
tcp6       0      0 :::3306                 :::*                    LISTEN      168878/docker-proxy 

-v → Attach a volume to the container. The default behavior of docker is it will not persist the data once the container is removed,so you will lose all your data.

To create persistent storage, I have created volume named "mysql_volume". MySQL stores the data in /var/lib/mysql/ inside the container and here it is mapped to localhost directory /var/lib/docker/volumes/mysql_volume1/_data, so your data will be persistent.

-d → Will start and run the container in detached mode. If you omit the -d flag, then you will see the container startup logs in the terminal and you have to open a new terminal session to connect to the container.

-e → Environmental variables. You have to set up mysql root user password using any one of the below parameters.

  • MYSQL_ROOT_PASSWORD → Setup your own password using this environment variable.
  • MYSQL_ALLOW_EMPTY_PASSWORD → Blank or Null password will be set. You have to set MYSQL_ALLOW_EMPTY_PASSWORD=1.
  • MYSQL_RANDOM_ROOT_PASSWORD → random password will be generated when the container is started. You have to set MYSQL_RANDOM_ROOT_PASSWORD=1 to generate the random password.

If you skip this step, then an error will be thrown as shown below.

Environmental Variable Error

4. Check MySQL Container Status

You can check the launched container status using the following command:

$ docker ps

Sample output:

CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
e1fb895f6f0f   mysql     "docker-entrypoint.s..."   3 minutes ago   Up 3 minutes   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql

5. Connect To MySQL Database

Run the following command to connect to the MySQL container.

$ docker exec -it mysql bash

Connect to the MySQL database as the root user by running the following command. In my case, I have set up my own password through MYSQL_ROOT_PASSWORD.

$ mysql -u root -p
Connect To Database

If you have used MYSQL_RANDOM_ROOT_PASSWORD=1 when launching the container, then you can get the automatically-generated password from the logs.

$ docker logs
$ docker logs mysql
Generated Root Password

The automatically generated root password will be long and it is not something to be remembered. You can reset the root password by running the following query.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysqlpassword';

Replace the password 'mysqlpassword' with your own password. You can also create your own user and grant him privileges as required.

6. Load Sample Data

The main objective of setting up mysql is to load some data and run queries against it. There are a few ways you can load the data. I have a sample file named "load_data.sql" with the following queries in it.

CREATE DATABASE IF NOT EXISTS football;

USE football;

CREATE TABLE IF NOT EXISTS players (
    player_name     VARCHAR(16)     NOT NULL,
    player_age      INT             NOT NULL,
    player_club     VARCHAR(16)     NOT NULL,
    player_country  VARCHAR(16)     NOT NULL
);

INSERT INTO players VALUES ("Messi",34,"PSG","Argentina");
INSERT INTO players VALUES ("Ronaldo",36,"MANU","Portugal");
INSERT INTO players VALUES ("Neymar",29,"PSG","Brazil");
INSERT INTO players VALUES ("Kane",28,"SPURS","England");
INSERT INTO players VALUES ("E Hazard",30,"MADRID","Belgium");

The first approach will be to copy the data from the local machine to the docker container using the "docker cp" command.

$ docker cp load_data.sql mysql:/tmp
$ docker exec -it mysql bash
$ ls -l /tmp/

Now you can connect to the mysql client and run the source command or redirect the file to the mysql client.

$ mysql -u root -p
mysql> source /tmp/load_data.sql

Or

$ mysql -u root -p < /tmp/load_data.sql

Connect to the database and query your table.

$ mysql -u root -p
mysql> show databases;
mysql> use football;
mysql> show tables;
mysql> select * from players;

Players Table

The second approach would be to redirect the .sql file when running the docker exec command.

$ docker exec -i mysql mysql -u root -p < load_data.sql

7. Setup MySQL Container Using Docker-Compose

Instead of pulling the image and running the docker run command, you can use docker-compose to quickly spin up the container. Docker-compose is best suited when you have more than one container to be created.

Create a file named docker-compose.yml or docker-compose.yaml file. Copy and paste the following yaml code. This is the same as what I have run manually in the previous sections.

version: '3.8'
services:
  database:
    image: mysql:latest
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: temp1234
    ports:
      - "3306:3306"
    volumes:
      - mysql_volume:/var/lib/mysql
volumes:
  mysql_compose_volume:

Now run the following command which will spin up the MySQL docker container.

$ docker-compose up

Launch MySQL Container With Docker Compose

Run the following command to check the status of the launched container using docker-compose:

$ docker-compose ps

Sample output:

Name              Command             State                          Ports                       
-------------------------------------------------------------------------------------------------
mysql   docker-entrypoint.sh mysqld   Up      0.0.0.0:3306->3306/tcp,:::3306->3306/tcp, 33060/tcp

8. Connect to MySQL Database Using MySQL Workbench

Till now we have seen how to connect to the database using mysql client from inside the container. You can also connect to the database using any GUI clients like mysql workbench, dbeaver, Heidi SQL, etc.

The important thing to note irrespective of what GUI client you are using is, what port was mapped when you are launching the container.

In my case I have mapped localhost (3306) -> container (3306). So If I have to establish a connection to the database, I have to connect to localhost:3306 or 127.0.01:3306.

I am using MySQL Workbench as my GUI client. If you have not installed MySQL workbench in your machine, then you can use the below instruction to install the same.

8.1. Install MySQL Workbench in Linux

Navigate to the official site to download the package file. You have to choose your operating system to download the .deb or .rpm file.

Choose Operating System For MySQL Workbench

In the next step, it will ask you to Login or Signup. You can skip login or signup by clicking "No thanks, Just Start my download" option.

Skip Login Or Signup Option

On Debian-based systems, install MySQL Workbench like below:

$ cd <path to downloaded .deb file>
$ sudo apt install ./mysql-workbench-community_8.0.27-1ubuntu21.04_amd64.deb

On RHEL-based systems, install MySQL Workbench like below:

$ cd <path to downloaded .rpm file>
$ sudo dnf localinstall ./mysql-workbench-community-8.0.27-1.el8.x86_64.rpm

Once the installation is completed, you can launch mysql workbench either from the menu or dash.

Launch MySQL Workbench

8.2. Connect to MySQL Database

Connect to the database running inside a docker container. You should try to connect to localhost:3306 or localhost:<port-no> depending upon how your container is set up.

Manage MySQL Connection

Before connecting to the database you can press "Test Connection" to check MySQL workbench was able to connect to the database instance successfully.

Test MySQL Connection

Now you can start running your queries against the tables you created.

Run Sample Query In MySQL Workbench

9. Conclusion

In this article, we have briefly seen how to launch MySQL containers in docker using docker run and docker-compose methods. If you want to setup MySQL for testing or learning purposes, Docker is more than enough.

Hope this helps.