DEV Community

loading...

How to use MySQL/Postgres with Docker

maxborysov profile image Max Borysov ・2 min read

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:

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

Make sure the container is running:

docker ps
Enter fullscreen mode Exit fullscreen mode

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

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

docker logs container-id
Enter fullscreen mode Exit fullscreen mode

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

docker logs $(docker ps | grep db-mysql | awk '{ print $1 }')
Enter fullscreen mode Exit fullscreen mode

This way you can check if docker volumes were created.

tree docker-volumes/mysql
Enter fullscreen mode Exit fullscreen mode

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

mysql -u root -p -h 0.0.0.0 -P 3306
Enter fullscreen mode Exit fullscreen mode

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

docker run --rm -it mysql mysql -u user -h host -p database
Enter fullscreen mode Exit fullscreen mode

Or you can step into the container:

docker run --rm -it mysql /bin/bash
Enter fullscreen mode Exit fullscreen mode

And(inside the container) connect to the server using production server credentials:

root@container-id:/# mysql -u prod-user -h prod-host -p database-name 
Enter fullscreen mode Exit fullscreen mode

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

List running containers to get mysql container-id:

docker ps
Enter fullscreen mode Exit fullscreen mode

Run mysql command inside the container:

docker exec -it container-id mysql -u root -h 0.0.0.0 -p
Enter fullscreen mode Exit fullscreen mode

Or we can make it in two steps:

Step inside the container:

docker exec -it 6c9c46111f94 mysql /bin/bash
Enter fullscreen mode Exit fullscreen mode

Connect to mysql in that container:

root@6c9c46111f94:/# mysql -u root -h 0.0.0.0 -p
Enter fullscreen mode Exit fullscreen mode

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):

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

How to run mysql service:

docker-compose up -d db-mysql
Enter fullscreen mode Exit fullscreen mode

Check status:

docker-compose ps
Enter fullscreen mode Exit fullscreen mode

If you have any issue, check the logs of the service:

docker-compose logs db-mysql
Enter fullscreen mode Exit fullscreen mode

Useful links:

Discussion (0)

pic
Editor guide