Docker With Databases

As you know installing databases in our computers is taking many time and they are also complicated. So In this topic I will instroduce ways to create database by using Docker which will help you save more time.

We will use docker-compose to create database containers

Docker For MySQL

• Let's create a folder with anyname you like as mysql docker and in this folder we will create a folder name mysql-data and a file docker-compose.yml and put scripts as belows:

version: '3.7'

services:
  mysql_db_container:
    # using latest image mysql from dockerHub
    image: mysql:latest
    # container name: check by command: docker container ls
    container_name: mysql_container
    command: --default-authentication-plugin=mysql_native_password
    # default username is root
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: sys
    # map port between real machine and container    
    ports:
    - 3306:3306
    # map data between real machine and container
    # which will help us avoid lost data when container is down
    volumes:
    - ./mysql-data:/var/lib/mysql:rw

volumes:
  mysql-data:

• Save the docker-compose.yml and open terminal and use commands sudo docker compose up -d to start the container and sudo docker compose down to stop the container.

• After your mysql DB has started you can use these conection information to connect to your DB

Field Name	Value
USERNAME	root
PASSWORD	defined in docker-compose file: password
PORT	defined docker-compose file, default 3306
HOST	localhost

Docker For Oracle DB

• We don't have many oracle database image on DockerHub so you can use my oracle image on my dockerhub by command docker pull minhducnguyen189/oracle-xe-18-4:latest. Or you will create an docker image for yourself to use.

Step 1: Prepare Environment

• You should clone this repo here
• Then you need access oracle page to download the installation file. Note, you have to download the file used for Linux x64
• If you are using Windows, you should install Git and use Git Bash to execute the script.

Step 2: Execute The Build Script

• Let's copy your oracle database installation file into the source repo of step 1 following this path docker-images\OracleDatabase\SingleInstance\dockerfiles\<your oracle database xe version>\

• Now let go to this path of source repo docker-images\OracleDatabase\SingleInstance\dockerfiles. Then open the Git Bash and execute the build script with command below

• ./buildContainerImage.sh -v 18.4.0 -t <oracle image name (Ex: oracle-xe)> -x -i

• Ex: ./buildContainerImage.sh -v 18.4.0 -t oracle-xe -x -i

• You can view more configuration here

• Note that when the console show the message below, you need to wait for 5 minutes or more. So, let's patient.

  #8 215.0
  #8 215.0 Complete!
  #8 215.2 Loaded plugins: ovl

• When you see this message in the console. So everything has done!

  #8 1174.5 Complete!
  #8 DONE 1179.2s

  #9 exporting to image
  #9 sha256:e8c613e07b0b7ff33893b694f7759a10d42e180f2b4dc349fb57dc6b71dcab00
  #9 exporting layers
  #9 exporting layers 129.3s done
  #9 writing image sha256:14716a3a18ce456d8ae8062ca43ebb5ea180fad97a4dfef830a164be5c70d363 0.0s done
  #9 naming to docker.io/library/oracle-xe
  #9 naming to docker.io/library/oracle-xe 0.0s done
  #9 DONE 129.4s

• Then you can open terminal and use command docker images to check your oracle image has created or not

Step 3: Create A Docker Compose File

• Let's create a folder with anyname you like as oracle docker and in this folder we will create 2 folders name oradata and setup. Then we create a file docker-compose.yml and put scripts as belows:

version: '3.7'

services:
  database:
    # oracle database image from DockerHub
    image: oracle-xe
    # container name
    container_name: oracle_database
    environment:
      # Oracle password for SYS, SYSTEM and PDBADMIN accounts
      ORACLE_PWD: password
    volumes:
      # mapping data of container to real machine
      - ./oradata:/opt/oracle/oradata
      # mapping db scripts to container if we have
      - ./startup:/opt/oracle/scripts/startup
    ports:
      - 1521:1521

volumes:
  oradata:
  startup:

Step 4: Test Docker Image

• Then use command sudo docker compose up to start your container.
• You should note the the first time starting DB will make many time. After your orcal DB has started, you can use these conection information to connect to your DB

Field Name	Value
DATABASE (Service Name)	XEPDB1
USERNAME	sys
PASSWORD	defined docker-compose file, default password)
PORT	defined docker-compose file, default 1521
HOST	localhost
ROLE	SYSDBA

• to access the container you can use this command
• docker exec -it oracle_database /bin/bash

Docker For PostgreSQL

• Let's create a folder with anyname you like as postgres docker and in this folder we will create a folder name data and a file docker-compose.yml and put scripts as belows:

version: '3.7'

services:
  db:
    image: postgres
    container_name: postgres_database
    restart: always
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
    volumes:
      - ./data:/var/lib/postgresql/data
    ports:
      - 5432:5432

volumes:
  data:

• After your prosgres database has started you can use these conection information to connect to your DB

Field Name	Value
USERNAME	user
PASSWORD	defined in docker-compose file: password
PORT	defined docker-compose file, default 27017
HOST	localhost

Docker For MongoDB

• Let's create a folder with anyname you like as mongo docker and in this folder we will create a folder name data and a file docker-compose.yml and put scripts as belows:

version: '3.7'

services:

  mongo:
    # use mongo image from dockerhub
    image: mongo
    # restart if service is failed
    restart: always
    container_name: mongodb
    environment:
      MONGO_INITDB_ROOT_USERNAME: root
      MONGO_INITDB_ROOT_PASSWORD: example
    ports:
      - 27017:27017
    # sync data from container to real machine
    volumes:
      - ./data:/data/db

  mongo-express:
    # use image from dockerhub
    image: mongo-express
    container_name: mongo-express
    environment:
      ME_CONFIG_MONGODB_ADMINUSERNAME: root
      ME_CONFIG_MONGODB_ADMINPASSWORD: example
      ME_CONFIG_MONGODB_SERVER: mongodb
    # restart if service is failed
    restart: always
    depends_on:
      - mongo
    ports:
      - 8081:8081

volumes:
  data:

• After your mongodb has started you can use these connection information to connect to your DB

Field Name	Value
USERNAME	root
PASSWORD	defined in docker-compose file: example
PORT	defined docker-compose file, default 27017
HOST	localhost

Note that: every time you start you container, it will create an anonymous volume. So you should delete it after you shut down your container by command docker volume rm <volume name> -f.

Docker MariaDB

• Let's create a folder with any name you like as mariadb and in this folder we will create a folder name mariadb and a file docker-compose.yml and put scripts as below:

version: '3.1'

services:
  db:
    image: mariadb:latest
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: mydb
      MYSQL_USER: user
      MYSQL_PASSWORD: password
    ports:
      - 3306:3306
    volumes:
      - mariadb:/var/lib/mysql

volumes:
  mariadb:

• After your mariadb has started you can use these connection information to connect to your DB.
• If you use root user, you will have permission to access all database and create any database that you want. The root user credentials are showed as below.

Field Name	Value
USERNAME	root
PASSWORD	defined in docker-compose file: rootpass
PORT	defined docker-compose file, default 3306
HOST	localhost

• If you use non root user that defined in the docker-compose.yaml then you can only access to the database that you defined mydb.

Field Name	Value
USERNAME	user
PASSWORD	defined in docker-compose file: password
PORT	defined docker-compose file, default 3306
HOST	localhost
DATABASE	mydb
- If you want to grant all permission for the non root user as the root user, you can access the database with root user and run SQL scripts below to grant permissions.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'user'@'%';

• GRANT ALL PRIVILEGES: This command grants all possible privileges to a specific database user. Privileges include the ability to perform operations like SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and more.

• ON .: The *.* syntax specifies that the privileges apply to all databases (*) and all tables (*) within those databases. This means the user will have full access to every database and table on the server.

• TO 'user'@'%': This specifies the user and host to which the privileges are granted.

  • 'user' is the username of the account being granted privileges.
  • '%'is a wildcard for the host part, meaning the user can connect from any IP address or hostname.

• IDENTIFIED BY 'password': This clause sets the password for the specified user. If the user already exists, their password will be updated to the provided value.

• FLUSH PRIVILEGES;: This command tells the database server to reload the grant tables in memory. This is necessary because MySQL or MariaDB caches user privileges, and changes made by the GRANT command won't take effect until the cache is refreshed. FLUSH PRIVILEGES ensures that the new privileges are applied immediately.

• SHOW GRANTS FOR 'user'@'%';:This command displays the privileges that have been granted to the specified user. It shows the exact privileges that the user 'user' has when connecting from any host ('%'). This is a way to verify that the GRANT command worked as intended.