Skip to main content

Docker Database

Introduction​

Managing databases for development can be challenging - different projects may require different database systems, versions, or configurations. Docker simplifies this process by allowing you to run databases in isolated containers, making it easy to:

  • Run multiple database types (MySQL, PostgreSQL, MongoDB, etc.) simultaneously
  • Maintain different versions of the same database
  • Quickly set up and tear down database environments
  • Create consistent development environments across teams

In this guide, we'll explore how to use Docker to manage databases for your development projects. You'll learn how to set up, configure, and interact with database containers.

Prerequisites​

Before starting, make sure you have:

  • Docker installed on your system
  • Basic familiarity with Docker concepts (images, containers)
  • Basic understanding of databases

Setting Up Your First Database Container​

Let's start by creating a simple MySQL database container.

Running a MySQL Container​

bash
docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=mysecretpassword -p 3306:3306 -d mysql:8.0

This command:

  • Creates a container named my-mysql
  • Sets the root password to "mysecretpassword"
  • Maps port 3306 on your host to port 3306 in the container
  • Uses the MySQL 8.0 image
  • Runs the container in detached mode (-d)

Verifying the Container​

Check if your container is running:

bash
docker ps

Output should look similar to:

CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                               NAMES
a1b2c3d4e5f6   mysql:8.0   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   0.0.0.0:3306->3306/tcp, 33060/tcp   my-mysql

Connecting to Your MySQL Container​

You can connect to your MySQL container using the MySQL client:

bash
docker exec -it my-mysql mysql -uroot -pmysecretpassword

Once connected, you can run SQL commands:

sql
CREATE DATABASE mydatabase;
USE mydatabase;
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255));
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
SELECT * FROM users;

Database Persistence​

By default, Docker containers are ephemeral - if you remove the container, all data is lost. For databases, we need persistence. Let's explore how to make our database data persistent.

Using Docker Volumes​

Volumes are the preferred way to persist data in Docker:

bash
docker run --name mysql-persistent \
  -e MYSQL_ROOT_PASSWORD=mysecretpassword \
  -p 3307:3306 \
  -v mysql-data:/var/lib/mysql \
  -d mysql:8.0

This creates a Docker volume named mysql-data that persists your database files.

You can check your volumes with:

bash
docker volume ls

Now, even if you remove and recreate the container, your data will be preserved as long as you use the same volume.

Working with Different Database Types​

Docker makes it easy to work with various database systems. Let's explore a few popular options.

PostgreSQL​

bash
docker run --name my-postgres \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -v postgres-data:/var/lib/postgresql/data \
  -d postgres:13

Connecting to PostgreSQL:

bash
docker exec -it my-postgres psql -U postgres

Basic PostgreSQL commands:

sql
CREATE DATABASE myapp;
\c myapp
CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
INSERT INTO users (name, email) VALUES ('Jane Smith', '[email protected]');
SELECT * FROM users;

MongoDB​

bash
docker run --name my-mongo \
  -p 27017:27017 \
  -v mongo-data:/data/db \
  -d mongo:5.0

Connecting to MongoDB:

bash
docker exec -it my-mongo mongosh

Basic MongoDB commands:

javascript
use myapp
db.createCollection('users')
db.users.insertOne({ name: 'Mike Johnson', email: '[email protected]' })
db.users.find()

Creating a Database Service with Docker Compose​

For real-world applications, you'll often want to define your database as part of a larger application stack. Docker Compose is perfect for this.

Create a docker-compose.yml file:

yaml
version: '3.8'

services:
  db:
    image: mysql:8.0
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"
    volumes:
      - db_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  db_data:

Create an init.sql file to set up your database schema:

sql
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username, email) VALUES 
  ('user1', '[email protected]'),
  ('user2', '[email protected]');

Start your services:

bash
docker-compose up -d

Database Administration Tools​

Managing databases through command line can be challenging. Let's look at some admin tools you can run as Docker containers.

phpMyAdmin for MySQL​

bash
docker run --name phpmyadmin \
  -d --link my-mysql:db \
  -p 8080:80 \
  phpmyadmin/phpmyadmin

Access phpMyAdmin at http://localhost:8080 and log in with your MySQL credentials.

pgAdmin for PostgreSQL​

bash
docker run --name pgadmin \
  -e PGADMIN_DEFAULT_EMAIL=[email protected] \
  -e PGADMIN_DEFAULT_PASSWORD=adminpassword \
  -p 8081:80 \
  -d dpage/pgadmin4

Access pgAdmin at http://localhost:8081 and log in with the email and password you set.

Best Practices for Docker Databases​

When working with databases in Docker, follow these best practices:

1. Always Use Volumes for Persistence​

bash
docker run --name mysql-prod \
  -v mysql-prod-data:/var/lib/mysql \
  -d mysql:8.0

2. Set Resource Limits​

Limit memory and CPU to prevent a database container from consuming all host resources:

bash
docker run --name mysql-limited \
  -m 512m --cpus=0.5 \
  -d mysql:8.0

3. Use Environment Variables for Configuration​

Store sensitive information in environment variables:

bash
docker run --name mysql-secure \
  -e MYSQL_ROOT_PASSWORD=$DB_ROOT_PASSWORD \
  -e MYSQL_DATABASE=$DB_NAME \
  -e MYSQL_USER=$DB_USER \
  -e MYSQL_PASSWORD=$DB_PASSWORD \
  -d mysql:8.0

4. Use Health Checks​

Add health checks to ensure your database is functioning correctly:

yaml
version: '3.8'
services:
  db:
    image: mysql:8.0
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-p$$MYSQL_ROOT_PASSWORD"]
      interval: 30s
      timeout: 10s
      retries: 5

Advanced Database Configurations​

Let's explore some advanced database configurations for specific use cases.

Custom MySQL Configuration​

Create a my.cnf file:

[mysqld]
max_connections = 250
innodb_buffer_pool_size = 256M
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Use it in your container:

bash
docker run --name mysql-custom \
  -v ./my.cnf:/etc/mysql/conf.d/my.cnf \
  -d mysql:8.0

Setting Up Database Replication​

Create a primary-replica setup with Docker Compose:

yaml
version: '3.8'

services:
  mysql-primary:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
    volumes:
      - mysql-primary-data:/var/lib/mysql
      - ./primary.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - "3306:3306"

  mysql-replica:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
    volumes:
      - mysql-replica-data:/var/lib/mysql
      - ./replica.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - "3307:3306"
    depends_on:
      - mysql-primary

volumes:
  mysql-primary-data:
  mysql-replica-data:

Creating a Multi-Database Application​

Modern applications often use multiple databases. Let's create a setup with both MongoDB and Redis:

yaml
version: '3.8'

services:
  mongodb:
    image: mongo:5.0
    volumes:
      - mongodb_data:/data/db
    ports:
      - "27017:27017"
    restart: unless-stopped

  redis:
    image: redis:6.2
    volumes:
      - redis_data:/data
    ports:
      - "6379:6379"
    restart: unless-stopped

volumes:
  mongodb_data:
  redis_data:

Visualizing Database Architecture​

Let's visualize a typical Docker database architecture:

Practical Example: Web Application with Database​

Let's create a complete example with a web application and a database:

yaml
version: '3.8'

services:
  app:
    image: node:14
    volumes:
      - ./app:/app
    working_dir: /app
    command: npm start
    ports:
      - "3000:3000"
    depends_on:
      - db
    environment:
      DB_HOST: db
      DB_USER: appuser
      DB_PASSWORD: apppassword
      DB_NAME: appdb

  db:
    image: mysql:8.0
    volumes:
      - db_data:/var/lib/mysql
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: apppassword
    ports:
      - "3306:3306"

volumes:
  db_data:

Troubleshooting Common Issues​

Container Won't Start​

If your database container won't start, check the logs:

bash
docker logs my-mysql

Common issues include:

  • Port conflicts
  • Insufficient permissions on volume mounts
  • Invalid configuration parameters

Connection Refused​

If you can't connect to your database:

  1. Check if the container is running

    bash
    docker ps | grep mysql

  2. Verify port mapping

    bash
    docker port my-mysql

  3. Try connecting from inside the container

    bash
    docker exec -it my-mysql mysql -uroot -p

Data Persistence Issues​

If your data isn't persisting between container restarts:

  1. Check if you're using volumes correctly

    bash
    docker inspect my-mysql

  2. Look for the Mounts section to verify volume configuration

Summary​

Docker provides a flexible and powerful way to manage databases for development and production environments. In this guide, you've learned:

  • How to create and run database containers
  • Different ways to persist database data
  • Working with various database systems (MySQL, PostgreSQL, MongoDB)
  • Using Docker Compose for multi-container applications
  • Best practices for Docker databases
  • Troubleshooting common issues

By containerizing your databases, you gain portability, consistency, and isolation, making your development workflow smoother and more efficient.

Exercises​

  1. Create a Docker Compose file that sets up MySQL and phpMyAdmin.
  2. Configure a PostgreSQL container with custom configuration settings.
  3. Set up a MongoDB replica set using Docker Compose.
  4. Create a backup and restore strategy for a Docker database.
  5. Build a simple Node.js application that connects to a dockerized MySQL database.

Additional Resources​


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)