Gabriel Márquez

Field note / Docker Containers

Running MySQL in Docker Like a Pro

A complete guide to setting up, managing, and automating MySQL in Docker containers.

2 min read

  • Docker Containers
  • MySQL Backup and Restore
  • Automation and Tutorials
  • Tutorials

In the fast-paced world of software development, setting up a reliable, portable database shouldn’t be a struggle. Enter MySQL in Docker—a hassle-free way to manage databases for development and testing. Whether you’re new to Docker or looking to fine-tune your workflow, this guide has your back.

Let’s get started and turn database setup into one less thing to worry about.

Why Run MySQL in Docker?

Docker takes the complexity out of setting up MySQL. It lets you:

  • Quickly spin up isolated MySQL instances for testing or debugging.
  • Share your database setup without the infamous “it works on my machine” problem.
  • Ensure consistency across development, staging, and production environments.
  • Simplify database backup and restore processes.
  • Run multiple MySQL versions simultaneously (e.g., MySQL 5.8 and 8.0) for testing compatibility or migrating applications.

With Docker, switching between MySQL versions becomes as simple as updating an image tag, making it a powerful tool for developers juggling diverse project requirements.

Step 1: Create Your MySQL Docker Container

Run the following command to start a new MySQL container:

docker run \
    --name=mysql \
    -p 3306:3306 \
    -v mysql-volume:/mysql \
    -e MYSQL_ROOT_HOST="%" \
    -e MYSQL_ROOT_PASSWORD="yourpassword" \
    -d mysql/mysql-server:latest

Step 2: Use a .my.cnf File for Secure Credential Management

Managing your credentials securely is critical. Instead of passing them directly in commands, we’ll use a .my.cnf file.

1. Create the .my.cnf File on Your Host

[client]
user=root
password=<YOUR_PASSWORD>

2. Copy the .my.cnf File to the Container

docker cp .my.cnf mysql:/root/.my.cnf
docker exec mysql chmod 600 /root/.my.cnf

With this setup, you can securely manage your MySQL credentials without typing them in every time. To log in, just run:

docker exec -it mysql mysql

Step 3: Grant External Access

To allow external applications to connect to the database, open the MySQL shell inside the container and run the following SQL commands:

CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'your_user'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
CREATE DATABASE your_db;

Step 4: Create an Alias

Instead of typing:

docker exec -i mysql mysql

You can just type mysql to connect to your server. Create an alias:

alias mysql="docker exec -i mysql mysql"

Persist new aliases

To keep aliases between sessions, save them in your shell configuration:

# Bash – ~/.bashrc
# ZSH – ~/.zshrc

Then source your config file:

source ~/.zshrc

Optimize with custom aliases for specific databases

alias mysql-mydb="docker exec -i mysql mysql -D mydb"

Step 5: Load a Backup into the Container

docker exec -i mysql mysql your_db < your_backup.sql

Step 6: Backup Your Data

Manual Backup

docker exec mysql /usr/bin/mysqldump your_db > your_backup.sql

Automate Backups with Cron

crontab -e

Add the following line to schedule daily backups at 2 AM:

0 2 * * * docker exec mysql /usr/bin/mysqldump your_db > ~/backups/your_db_$(date +\%Y-\%m-\%d).sql

Conclusion

You’ve just leveled up your database management skills by setting up MySQL in Docker with a secure and efficient workflow. By automating backups with timestamps, you’re adding an extra layer of reliability to your development and production environments.

← All writing