DEV Community

Cover image for How to spin MySQL server with Docker and Docker Compose (plus Adminer)
Sony AK
Sony AK

Posted on • Updated on

How to spin MySQL server with Docker and Docker Compose (plus Adminer)

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:
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Looks good and our MySQL and Adminer containers are running well.

To check our data container, check with command below.

docker volume ls
Enter fullscreen mode Exit fullscreen mode

It will show like below.

DRIVER              VOLUME NAME
local               mysql_mysql_db_data_container
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

or

mysql -uroot -prootpassword --protocol=TCP
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

To shutdown database and delete all containers.

docker-compose down
Enter fullscreen mode Exit fullscreen mode

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

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)

Collapse
 
prstyocode profile image
utopictown

hi, nice blog post, but I think you pasted the wrong snippet for docker-compose

Collapse
 
sonyarianto profile image
Sony AK

aw aw nice catch mas, thank you, now already fixed.