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
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:
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:
docker exec -it my-mysql mysql -uroot -pmysecretpassword
Once connected, you can run SQL commands:
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:
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:
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
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:
docker exec -it my-postgres psql -U postgres
Basic PostgreSQL commands:
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
docker run --name my-mongo \
-p 27017:27017 \
-v mongo-data:/data/db \
-d mongo:5.0
Connecting to MongoDB:
docker exec -it my-mongo mongosh
Basic MongoDB commands:
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:
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:
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:
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
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
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
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:
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:
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:
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:
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:
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:
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:
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:
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:
-
Check if the container is running
bashdocker ps | grep mysql
-
Verify port mapping
bashdocker port my-mysql
-
Try connecting from inside the container
bashdocker exec -it my-mysql mysql -uroot -p
Data Persistence Issues
If your data isn't persisting between container restarts:
-
Check if you're using volumes correctly
bashdocker inspect my-mysql
-
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
- Create a Docker Compose file that sets up MySQL and phpMyAdmin.
- Configure a PostgreSQL container with custom configuration settings.
- Set up a MongoDB replica set using Docker Compose.
- Create a backup and restore strategy for a Docker database.
- 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! :)