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. Here’s how:

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

Use your favorite text editor to create the file. For example, using VS Code:

  1. Open VS Code and create a new file named .my.cnf.
  2. Add the following content:
[client]
user=root
password=<YOUR_PASSWORD>
  1. Save the file in your working directory.

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

Now, copy the file into the container and set the proper permissions:

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;

This creates a new user, grants the necessary permissions, and sets up your database.

Step 4: Create an alias

Now that you have your conf file, it’s time to add an alias to make your life easier

Adding an alias

Instead of typing:

docker exec -i mysql mysql

You can just type mysql to connect to your server To do that, you just need to create an alias like this:

alias mysql="docker exec -i mysql mysql"

Persist new aliases

To keep aliases between sessions, you can save them in your user’s shell configuration profile file. This can be:

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

Then don’t forget to source your config file or start a new terminal

source ~/.bashrc
source ~/.zshrc

Optimize with custom aliases for specific databases

If there’s a database that you found yourself connecting a lot, you can set a new alias to connect directly to it.

Instead of

mysql
use database mydb

Just create a new alias

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

And then run this to connect to it:

mysql-mydb

Step 5: Load a Backup into the Container

If you have an existing backup, importing it into the container is straightforward. On your host machine:

  1. Place the .sql backup file in your working directory.
  2. Use the following command to load it into MySQL:
 docker exec -i mysql mysql your_db < your_backup.sql

This imports the contents of your_backup.sql into the MySQL instance.

Step 6: Backup Your Data

6.1: Manual Backup

Regularly backing up your data ensures you can recover it in case of accidental loss or corruption. To manually back up your MySQL database, use the following command to export it to a .sql file:

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

To automate this process, you can set up a daily backup task with the current date included in the filename. Here’s how to schedule it on macOS and Linux:

6.2: Automate Backups with Cron (macOS and Linux)

1. Open the crontab editor:

 crontab -e

2. Add the following lines to schedule the backup daily at 2 AM:

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

Explanation:

  • 0 2 * * *: The cron schedule to run the command every day at 2 AM.
  • docker exec mysql /usr/bin/mysqldump your_db: Executes the MySQL dump command inside the container.
  • >: Redirects the output to a file.
  • ~/backups/your_db_$(date +\%Y-\%m-\%d).sql: The path and filename for the backup file, including the current date in the format YYYY-MM-DD.

3. Save and exit the editor.

This command creates a backup file in the ~/backups directory with the current date in its name (e.g., your_db_2024-12-20.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.