If you are new to MySQL or someone who wishes to quickly and effortlessly install MySQL database, then this article is for you. In this 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.
Table of Contents
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 also refer the official Docker documentation links given below to know how to setup Docker and Docker compose.
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
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.
If you wish to know more about docker volumes take a look at our detailed article on same.
-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 setMYSQL_ALLOW_EMPTY_PASSWORD=1.MYSQL_RANDOM_ROOT_PASSWORD→ random password will be generated when the container is started. You have to setMYSQL_RANDOM_ROOT_PASSWORD=1to generate the random password.
If you skip this step, then an error will be thrown as shown below.
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
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
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;
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
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.
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.
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.
Before connecting to the database you can press "Test Connection" to check MySQL workbench was able to connect to the database instance successfully.
Now you can start running your queries against the tables you created.
9. Conclusion
In this article, we have 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.
