How can I initialize a MySQL database with schema in a Docker container?

Initializing a MySQL database with a schema in Docker typically involves the following steps:

Step 1: Create the Dockerfile and configuration files

First, you need to create a Dockerfile to customize the MySQL image. This typically involves setting up initial configurations and importing initialization SQL scripts.

For example, you can create a Dockerfile as follows:

dockerfile
FROM mysql:5.7
ENV MYSQL_DATABASE company
ENV MYSQL_ROOT_PASSWORD example
ADD schema.sql /docker-entrypoint-initdb.d/
EXPOSE 3306

In this Dockerfile, we start with the official MySQL 5.7 image, set the environment variables MYSQL_DATABASE to specify the database name (in this example, company), and MYSQL_ROOT_PASSWORD to define the root user password. Then, we add the schema.sql file containing the database schema to the container's /docker-entrypoint-initdb.d/ directory. This directory is where the MySQL image searches for scripts to execute at container startup.

Step 2: Write the database schema file

The schema.sql file contains SQL statements that define the database schema. For example:

sql
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(255) NOT NULL,
    salary DECIMAL(10, 2),
    hired DATE NOT NULL
);

This SQL script creates the employees table during database initialization.

Step 3: Build and run the Docker container

Once you have the Dockerfile and schema.sql file, use the following command to build the Docker image:

bash
docker build -t my-custom-mysql .

After building, start the MySQL container with:

bash
docker run -d -p 3306:3306 --name my-mysql-instance my-custom-mysql

This command maps port 3306 from the container to port 3306 on the host and runs the container in the background.

Step 4: Verify the database

Once the container is running, connect to the MySQL server to confirm that all tables and initial data have been configured according to the schema.sql file. You can use MySQL client tools or the command line:

bash
mysql -h localhost -P 3306 --protocol=tcp -u root -p

Then, check the database:

sql
USE company;
SHOW TABLES;
DESCRIBE employees;

These steps should enable you to successfully initialize a MySQL database with a schema in a Docker container.