MySQL Authentication in Docker Containers: A Step-by-Step Guide

Setting up MySQL authentication in Docker requires defining environment variables like MYSQL_ROOT_PASSWORD and creating specific users with limited privileges. This guide demonstrates how to configure database1 and database2 with distinct users such as user1, ensuring secure access control within isolated container environments. Proper configuration prevents unauthorized access while maintaining necessary permissions for application development and data management tasks.

Understanding Docker MySQL Environment Variables

The official MySQL Docker image uses environment variables to initialize the database on first start. These variables are critical for setting up authentication from the beginning. The most important variable is MYSQL_ROOT_PASSWORD, which sets the password for the root superuser account. Without this variable, the container will fail to start or leave the root account without a password, creating a significant security vulnerability. You can also define additional databases using the MYSQL_DATABASE variable. If you need multiple databases, you must create them via SQL scripts after initialization or use custom entrypoint scripts. For our scenario, we want two separate databases: database1 and database2. While MYSQL_DATABASE only accepts a single database name, you can extend this by placing SQL files in the /docker-entrypoint-initdb.d/ directory inside the container. Another useful variable is MYSQL_USER and MYSQL_PASSWORD. These create a non-root user with full privileges on the specified database. However, for more granular control, it is better to create specific users for each database manually after the initial setup. This approach allows you to assign different permission levels to different applications accessing the same MySQL instance. For teams working across different languages, understanding these basic configurations is essential. You might find it helpful to review Setting User Privileges in Docker for a Team Using Different Development Languages to see how these principles apply in broader team settings.

Creating Databases and Users via Init Scripts

To create multiple databases and specific users, you need to provide initialization scripts. Create a directory on your host machine, for example, ./init-scripts. Inside this directory, create a file named 01-create-databases.sql. This script will run automatically when the MySQL container starts for the first time. Write the following SQL commands in this file:

CREATE DATABASE IF NOT EXISTS database1;
CREATE DATABASE IF NOT EXISTS database2;

Next, create another file named 02-create-users.sql. This script will define the users and their permissions. It is crucial to create users that connect from localhost within the Docker network. Use the following structure:

CREATE USER 'user1'@'%' IDENTIFIED BY 'password1';
GRANT ALL PRIVILEGES ON database1.* TO 'user1'@'%';
FLUSH PRIVILEGES;

In this example, user1 has all privileges on database1. The '%' wildcard allows connections from any host, which is convenient for Docker networking but should be restricted to specific IPs in production. You can create similar entries for other databases and users as needed. Remember to always flush privileges after making changes to ensure they take effect immediately. If you encounter connection issues during this process, check if your MySQL server is properly exposing ports. Refer to MySQL Server Won't Connect to Docker Container on Ubuntu 22.04 for troubleshooting common connectivity problems.

Docker Compose Configuration

Using Docker Compose simplifies the management of MySQL containers and their associated volumes. Create a docker-compose.yml file in your project root. This file defines the services, networks, and volumes required for your setup. Here is a basic configuration:

version: '3.8'
services:
  mysql:
    image: mysql:8.0
    container_name: my-mysql
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: database1
    ports:
      - "3306:3306"
    volumes:
      - ./init-scripts:/docker-entrypoint-initdb.d
      - mysql-data:/var/lib/mysql

volumes:
  mysql-data:

This configuration mounts the ./init-scripts directory into the container's init directory. It also persists data using a named volume called mysql-data. The MYSQL_DATABASE variable creates the default database, while the init scripts create additional ones. Note that the root password is set directly in the environment section. In production, consider using Docker secrets or external secret managers instead of plain text passwords. When developing applications, especially those involving C#, you may face permission errors. Ensure your user accounts have the correct network access. See Resolving Permission Errors while Developing Dockerized MySQL Application in C# for insights on handling such scenarios.

Verifying Authentication Setup

After starting the container with docker-compose up -d, verify that the databases and users were created correctly. Connect to the MySQL shell using the root credentials:

docker exec -it my-mysql mysql -u root -p

Enter the root password when prompted. Once inside, run the following command to list all databases:

SHOW DATABASES;

You should see database1, database2, and possibly information_schema and mysql. To verify the user creation, switch to the new user:

exit
docker exec -it my-mysql mysql -u user1 -ppassword1 database1

If the connection succeeds, the authentication is working correctly. Try running a simple query like SELECT 1; to confirm read access. If you receive an access denied error, double-check the username, password, and host specifications in your SQL scripts.

Frequently Asked Questions

How do I reset the MySQL root password in Docker?

You can reset the root password by stopping the container, removing the data volume, and restarting with a new MYSQL_ROOT_PASSWORD. Alternatively, connect as root and run ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; after bypassing authentication temporarily.

Can I use a single user for both database1 and database2?

Yes, you can grant privileges on multiple databases to a single user by listing them in the GRANT statement. However, it is best practice to isolate users per database to minimize security risks if one application is compromised.

Why does my Docker MySQL container fail to start?

Common causes include missing MYSQL_ROOT_PASSWORD environment variable, incorrect file permissions on mounted volumes, or port conflicts with existing services. Check the container logs using docker logs container_name for specific error messages.

Is it safe to allow connections from '%' host?

Allowing connections from '%' is convenient for development but insecure for production. It is recommended to restrict user hosts to specific IP addresses or Docker network subnets to prevent unauthorized access from external sources.

How do I backup the MySQL data in Docker?

Use the mysqldump utility inside the container or mount a host directory to export dumps. For persistent storage, rely on Docker volumes or bind mounts to ensure data survives container restarts and updates.