How to use MySQL/Postgres with Docker
Tags: database, mysql, postgres, docker
Published: 2021-04-10 21:46:34

---
title: How to use MySQL/Postgres with Docker
published: true
description: I will share how I use MySQL and docker locally and on production
tags: database, mysql, postgres, docker
---

Here are different ways you can use mysql with docker.

### Start mysql-server in docker with a mounted volume

Let's say you are in a `/home` directory. Create `docker-volumes/` folder and run the following command:

```shell
docker run \
       --name db-mysql \
       -e MYSQL_ROOT_PASSWORD=root \
       -v $(pwd)/docker-volumes/mysql:/var/lib/mysql \
       -d \
       -p 3306:3306 \
       mysql
```

Make sure the container is running:
```
docker ps
```

If all good you'll see smth like that:
```
CONTAINER ID   IMAGE     COMMAND                  CREATED         STATUS         PORTS                               NAMES
72aea1d629a6   mysql     "docker-entrypoint.s…"   2 seconds ago   Up 2 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   db-mysql
```

If there are any issues(for instance, `STATUS: Exited`), check the logs:

```shell
docker logs container-id
```

Or you can get container-id dynamically(where `db-mysql` is a name we gave to our container):

```shell
docker logs $(docker ps | grep db-mysql | awk '{ print $1 }')
```

This way you can check if docker volumes were created.

```shell
tree docker-volumes/mysql
```

**Why do we need volumes?** If you create a database, tables in a running container, and then you stop it, **all your data will be lost**. 
It is completely ok you did it for demo or testing purposes.
In all other cases, **use volumes** to persist your data.

### Connect to mysql-server using local mysql-client

```shell
mysql -u root -p -h 0.0.0.0 -P 3306
```

### Connect to remote mysql-server using mysql-client from docker

```shell
docker run --rm -it mysql mysql -u user -h host -p database
```

Or you can step into the container:
```shell
docker run --rm -it mysql /bin/bash
```

And(inside the container) connect to the server using production server credentials:
```bash
root@container-id:/# mysql -u prod-user -h prod-host -p database-name 
```

### Connect to mysql-server in docker using mysql-client in docker

List running containers to get mysql container-id:

```bash
docker ps
```

Run mysql command inside the container:

```bash
docker exec -it container-id mysql -u root -h 0.0.0.0 -p
```

Or we can make it in two steps:

Step inside the container:
```bash
docker exec -it 6c9c46111f94 mysql /bin/bash
```

Connect to mysql in that container:
```bash
root@6c9c46111f94:/# mysql -u root -h 0.0.0.0 -p
```

### Use docker compose tool
`docker compose` is a really nice tool to configure, group, and manage your containers/services.

Create `docker-compose.yml` file.

The example how it might look like (it includes example for postgres as well):
```yaml
version: "3.7"
services:
  db-mysql:
    image: mysql:5.7
    container_name: db-mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - "3306:3306"
  db-psql:
    image: postgres:9
    container_name: db-postgres
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
    ports:
      - "5432:5432"
```

How to run mysql service:

```shell
docker-compose up -d db-mysql
```

Check status:
```shell
docker-compose ps
```

If you have any issue, check the logs of the service:
```bash
docker-compose logs db-mysql
```

---

### Useful links:
- [docker hub mysql](https://hub.docker.com/_/mysql)
- [docker hub mysql-server](https://hub.docker.com/r/mysql/mysql-server/)
- [Basic Steps for MySQL Server Deployment with Docker](https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/docker-mysql-getting-started.html)-
- [Run your image as a container](https://docs.docker.com/language/nodejs/run-containers/)