Sometimes we need MySQL server for our testing environment. Instead of install full MySQL server on our computer, we can use Docker to accommodate that. Let's start with my scenario.
I have Linux Ubuntu 19.10 (eoan) on my desktop and I want to have MySQL server on my machine. Plus because I love Adminer for MySQL administration, I will need it as well. I will use Docker and Docker Compose for this purpose.
Preparation
I assume that you already have Docker and Docker Compose installed on your machine. If not, please refer to https://docs.docker.com/install/ for Docker installation and refer to https://docs.docker.com/compose/install/ for Docker Compose installation.
In this case I am on Linux Ubuntu, but the result I think should be similar for other OS.
Compose file
File docker-compose.yml
version: '3.7'
services:
mysql_db_container:
image: mysql:latest
command: --default-authentication-plugin=mysql_native_password
environment:
MYSQL_ROOT_PASSWORD: rootpassword
ports:
- 3306:3306
volumes:
- mysql_db_data_container:/var/lib/mysql
adminer_container:
image: adminer:latest
environment:
ADMINER_DEFAULT_SERVER: mysql_db_container
ports:
- 8080:8080
volumes:
mysql_db_data_container:
What is the Docker Compose file above means? It will have 2 (two) services. First is the MySQL server and the second is Adminer. Adminer is useful for MySQL simple administration, it based on PHP, just similar like phpMyAdmin.
Our MySQL server will have root user with password rootpassword. It also will expose the port to host machine on port 3306. Please make sure there is no service using that port on host machine.
For Adminer also quite simple, it will expose port to host machine on port 8080. Please make sure there is no service using that port on host machine. Later you can access this port via HTTP (web browser).
We also use Docker data container named mysql_db_data_container to store the MySQL data. It's useful because your data will not be deleted even later you called docker-compose down command.
Run it
docker-compose up -d
Above command will start the services on detach mode (similar like running in the background).
If everything OK then our MySQL server will start.
How to check MySQL container is running or not?
Type this command.
docker ps
It will look like this.
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8e08c39f6cbf mysql:latest "docker-entrypoint.s…" 8 minutes ago Up 8 minutes 0.0.0.0:3306->3306/tcp, 33060/tcp mysql_mysql_db_container_1
03d837980beb adminer:latest "entrypoint.sh docke…" 8 minutes ago Up 8 minutes 0.0.0.0:8080->8080/tcp mysql_adminer_container_1
Looks good and our MySQL and Adminer containers are running well.
To check our data container, check with command below.
docker volume ls
It will show like below.
DRIVER VOLUME NAME
local mysql_mysql_db_data_container
It also looks good.
How to connect to MySQL server
Via Adminer
Go to http://localhost:8080 or http://127.0.0.1:8080 and fill the credential. User root and password rootpassword.
Via command line
Make sure you have MySQL client installed and mysql command in CLI available.
mysql -uroot -prootpassword -h 127.0.0.1
or
mysql -uroot -prootpassword --protocol=TCP
Now we can enjoy our local MySQL database server for any purpose we want. For me this setup is fine for testing purpose.
How to stop the MySQL server
To shutdown database without delete all containers.
docker-compose stop
To shutdown database and delete all containers.
docker-compose down
That's it and we are done. Relax, if you already create some data it will not gone.
The code above also available on my GitHub repository at https://github.com/sonyarianto/docker-compose-mysql-with-adminer
Thank you and I hope you enjoy it.
Reference
- https://hub.docker.com/_/mysql
- https://hub.docker.com/_/adminer/
- https://docs.docker.com/
- https://docs.docker.com/compose/
Credits
Cover image from photo by panumas nikhomkhai from Pexels at https://www.pexels.com/photo/bandwidth-close-up-computer-connection-1148820/
Top comments (2)
hi, nice blog post, but I think you pasted the wrong snippet for docker-compose
aw aw nice catch mas, thank you, now already fixed.