Docker: MySQL, MariaDB, PostgreSQL

docker-compose.yml

version: '3.4'

services:
  mysql:
    image: mysql:latest
    container_name: mysql
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_db
      MYSQL_USER: test
      MYSQL_PASSWORD: test
    ports:
      - 3306:3306
    volumes:
      - mysql_db_data_container:/var/lib/mysql:rw
      - ${PWD}/docker/mysql/initdb_sql_scripts:/docker-entrypoint-initdb.d/:ro
      - ${PWD}/docker/mysql/mysql.conf:/etc/mysql/conf.d
    healthcheck:
      test: "/usr/bin/mysql --user=root --password=password --execute \"SHOW DATABASES;\""
      interval: 2s
      timeout: 20s
      retries: 10

  mariadb:
    image: mariadb:latest
    container_name: mariadb
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_db
      MYSQL_USER: test
      MYSQL_PASSWORD: test
    ports:
      - 3307:3306
    volumes:
      - mariadb_data_container:/var/lib/mysql:rw
      - ${PWD}/docker/mariadb/initdb_sql_scripts:/docker-entrypoint-initdb.d/:ro
      - ${PWD}/docker/mariadb/mariadb.conf:/etc/mysql/conf.d

  postgres:
    image: postgres:latest
    container_name: postgres
    restart: always
    environment:
      POSTGRES_DB: test_db
      POSTGRES_USER: test
      POSTGRES_PASSWORD: test
    ports:
      - 5432:5432
    volumes:
      - postgres_db_data_container:/var/lib/postgresql/data:rw
      - ${PWD}/docker/postgres/initdb_sql_scripts:/docker-entrypoint-initdb.d:ro
      - ${PWD}/docker/postgres/postgres.conf:/etc/postgresql/postgresql.conf

volumes:
  mysql_db_data_container:
  mariadb_data_container:
  postgres_db_data_container:

MySQL

Run MySQL Docker Container

docker run -d \
    --name mysql-docker \
    -e MYSQL_ROOT_PASSWORD=root \
    -e MYSQL_DATABASE=test_db \
    -e MYSQL_USER=test \
    -e MYSQL_PASSWORD=test \
    -p 3306:3306 \
    -d \
    mysql:latest

Run MySQL Docker Container for MacOS M1 Chip

docker run -d \
    --name mysql-docker \
    -e MYSQL_ROOT_PASSWORD=root \
    -e MYSQL_DATABASE=test_db \
    -e MYSQL_USER=test \
    -e MYSQL_PASSWORD=test \
    -p 3306:3306 \
    -d \
    --platform linux/amd64 \
    mysql:latest

Run MySQL Docker Container with a mapped volume to host machine

docker run -d \
    --name mysql-docker \
    -e MYSQL_ROOT_PASSWORD=root \
    -e MYSQL_DATABASE=test_db \
    -e MYSQL_USER=test \
    -e MYSQL_PASSWORD=test \
    -v $PWD/docker/mysql/data:/var/lib/mysql:rw \
    -p 3306:3306 \
    -d \
    mysql:latest

Connect MySQL Docker Container in interaction mode

docker exec -it command can run a program in container interactively so running /bin/bash or /bin/sh command will start shell interactively.

docker exec -it mysql-docker /bin/bash

run the following code to connect to MySQL database.

mysql -h 127.0.0.1 -P 3306 -u root -p

then run the following command to show databases for testing.

SHOW DATABASES;

Connect MySQL Docker Container in interaction mode

docker exec -it can run mysql command interactively.

docker exec -it mysql-docker mysql -u root -p

Connect MySQL Docker Container from host machine

if you have mysql client in your local, then you can connect to MySQL database.

mysql -h 127.0.0.1 -P 3306 -u root -p

Execute SQL Statements in MySQL Docker Container

mysql -h 127.0.0.1 -P 3306 -u test_user -p -e "SHOW DATABASES;"

mysql -h 127.0.0.1 -P 3306 -u test_user -p -e "SELECT * FROM test.test_table;"

Redirect SQL file to table in MySQL Docker Container

mysql -h 127.0.0.1 -P 3306 -u root -p test_db < school.sql

Leave a Reply