How to spin MySQL server with Docker and Docker Compose (plus Adminer)
Tags: docker, dockercompose, mysql, adminer
Published: 2019-12-30 18:39:27

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`

```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/