Introduction

There are three ways to have a database service for development:

  1. Install a database service in your system.
  2. Connect to a Cloud database service like AWS RDS, Google Cloud SQL, Azure SQL, etc.
  3. Use Docker

This note will provide some steps for the last one: Docker.

When you are using Docker, you don’t have to install a database service in your system. Just use the public database image from Docker Hub, configure, create a container from images by the requirements. And you can run multiple versions of any database if needed.

The most convenient thing when using Docker is, it’s easy to share the project with other developers to start a local development environment without configuration and deploy to any host server in any system or environment.

Before we start, you’ll need to know these terms:

  1. Image: An object to store the programs and the configurations
  2. Container: Created from an image, a close environment with its own system and configurations
  3. Docker Hub: Docker version of GitHub, a hub for storage images on the Cloud

Requirement

  • Install Docker from the official website

  • Or use Homebrew:

    brew cask install docker

Getting Started

We can pull the database image from Docker Hub by running docker pull:

MySQL

# for amd64
docker pull mysql

# for arm64
docker pull mysql/mysql-server

PostgreSQL

docker pull postgres

There are different versions listed in “Tags” on Docker Hub.

You can choose the version you want, then install it by specifying the version tag:

docker pull postgres:13.2-alpine

# This equals to postgres:latest
docker pull postgres

Configuration of Container

After we get the image, we can create and run a container from the image. Before doing that, we should have some configurations for our container.

Environment

The environment in a container is separate from the outside, so we can create an independent environment we need to pass the arguments into the container as parameters when we run the container.

To configure the environment variables, add the -e flag when running the container.

Here are some basic environment variables for MySQL and PostgreSQL:

MySQL
  • MYSQL_ROOT_PASSWORD: Required. For setting the password of the superuser(root).
  • MYSQL_DATABASE: Optional. Set the database name when creating the container.
  • MYSQL_USER, MYSQL_PASSWORD: Optional. Create a new user with the password. The user will be the superuser of the database above. See GRANT for more information.
PostgreSQL

  • POSTGRES_USER: Optional. The username of the superuser. Default to postgres.

  • POSTGRES_PASSWORD: Required. For setting the password of the superuser.

  • POSTGRES_DB: The database name when you first launch the container, the default value will equal to POSTGRES_USER.

For more about the environment variables: MySQL, Postgres

Launch the Container

Run the following command to create and launch the container:

MySQL

Specify the username, password, and database name:

docker run --name <container_name> -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<root_password> -e MYSQL_USER=<username> -e MYSQL_PASSWORD=<user_password> -e MYSQL_DATABASE=<database_name> -d mysql

*You can replace mysql to mysql/mysql-server for arm64

Use the default superuser, which is root:

docker run --name <container_name> -p 3306:3306 -e MYSQL_ROOT_PASSWORD=<root_password> -d mysql

PostgreSQL

Specify the username, password, and database name:

docker run --name <container_name> -p 5432:5432 -e POSTGRES_USER=<username> -e POSTGRES_PASSWORD=<user_password> -e POSTGRES_DB=<database_name> -d postgres

Useful Parameters

  • --name: Set the container name
  • -p: Publish the port, map the port inside and outside the container, so we can reach the port of the database from outside. MySQL uses 3306 and PostgreSQL uses 5432 as default
  • -d: Run the container in the background

For more information about docker run please visit the Official Document.

Inspect

You can see the running container by running the following command:

docker ps

To inspect the log of a running container:

docker logs <container_name>

# You can also use the container ID instead of the container name

Execute Commands in A Container

To start a shell inside the container:

# bash
docker exec -it <container_name> bash

# use `/bin/bash` if `bash` doesn't work

# sh
docker exec -it <container_name> /bin/sh

Then you can see the prompt of the command line. Once you get into the shell, you can run any commands in the container.

To access Postgres inside the container from the shell:

psql <database> <username>

Replace <database> and <username> by your own configurations.

Connection Issue of MySQL

If you want to access MySQL, you may need to use root with the flag -p to tell docker to use the external port for connection to the internal port.

When we connect to the MySQL inside the container, MySQL recognizes it as external access, not a local one. So if we would like to access the database as root, we should get into the shell inside the container.

If we want to access MySQL from outside as a superuser, we should create another one as root.

To run the command to access MySQL as root:

mysql -u root -p

Now we can create another superuser for MySQL:

-- MySQL 5.*
CREATE USER '<username>'@'%' IDENTIFIED BY '<password>';

-- MySQL 8.*
CREATE USER '<username>'@'%' IDENTIFIED WITH mysql_native_password BY '<password>';

The % means to allow the user we created can be used for an external connection, or you can replace it with IP addresses as a whitelist.

Grant the superuser permission to a user:

GRANT ALL ON "database_name".* TO <username>@'%';

You can also give an existing user the permission to be used for an external connection:

UPDATE mysql.user SET HOST='%' WHERE USER = "username" LIMIT 1;

The last step, if we want to let external users execute the functions inside MySQL, run the following command:

SET GLOBAL log_bin_trust_function_creators=1;

Conclusion

Using Docker to create a database container for development can avoid environmental issues. You should try it!