How to Auto-Initialize MySQL with Docker: Build a Custom Image with SQL Scripts

1. Introduction

Docker is increasingly used in development. In a Spring Boot app we also run Mysql in Docker, and we need to initialize SQL scripts and data.

Two traditional solutions exist: manual import after container start (unsatisfactory) and initializing via Spring Boot client using Flyway (requires client capability). This article shows how to let the MySQL container initialize the database automatically when it starts.

2. Principle

When the MySQL container starts for the first time, it scans the /docker-entrypoint-initdb.d directory for .sh, .sql, and .sql.gz files and executes them in alphabetical order. By default they initialize the database named in the MYSQL_DATABASE variable, e.g.:

docker run --name some-mysql -e MYSQL_DATABASE=REGION_DB -d mysql:tag

If no database is specified in the start command, the DDL script must declare and use the database, otherwise MySQL throws:

ERROR 1046 (3D000) at line 7: No database selected

We will use this mechanism to initialize the database when the Docker container starts.

3. Custom Dockerfile

We create a custom Dockerfile based on mysql:5.7 . The script is:

FROM mysql:5.7
LABEL OG=felord.cn
COPY utf8mb4.cnf /etc/mysql/conf.d/utf8mb4.cnf
COPY ./sql  /tmp/sql
RUN mv /tmp/sql/*.sql /docker-entrypoint-initdb.d
RUN rm -rf /tmp/sql

Step 1: Pull the official mysql:5.7 image.

Step 2: Add author/organization information (no functional impact).

Step 3: Set character set and time zone (+8:00) to avoid Chinese character garbling.

Step 4: Copy the folder containing SQL scripts to /tmp/sql in the image.

Step 5: Move all .sql files to /docker-entrypoint-initdb.d so MySQL will execute them on startup.

Step 6: Remove the temporary directory.

Build the custom image:

# Do not forget the final dot
docker build -t mysql:5.7c .

Run a container from the new image:

docker run --name mysql-service -v d:/mysql/data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7c

Tip: Use SHOW VARIABLES LIKE 'character%' to verify the character set is utf8mb4 , and SHOW VARIABLES LIKE '%time_zone%' to check the time zone is UTC+8.

4. Summary

We have built a custom MySQL image that automatically runs initialization scripts when the container starts, simplifying deployment. The same approach can be applied to create other customized Docker images.

Full demo is available by following the public account and replying “mysqldocker”.