How to Create a MySQL Database in Docker Using Command and Docker Compose

Have you ever experienced that feeling of frustration when you have to install a database on two different machines?

On one, it works fine; on the other, there are bugs you can't explain?

Even though they are both the same version?

Setting up a MySQL database environment can often be a cumbersome task, especially when dealing with compatibility issues across different operating systems and dependencies.

Docker provides a streamlined solution to this problem by allowing you to containerize your MySQL database, ensuring consistent behavior regardless of the underlying system.

In this comprehensive guide, I will walk you through the step-by-step process of creating a MySQL database using Docker with command but also with docker compose.

Install Docker

In order to use docker, first you will need to install it. As I’m a Mac user, I installed docker on Mac. But It’s also available for Windows or Linux users. Once the installation is complete, start your docker application and let’s jump into the tutorial.

Running MySQL with one command

You can execute your MySQL docker instance using one simple command as follows

docker run \
  -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=mydatabase \
  --name mysqldb \
  -p 3306:3306 \
  -d mysql:8.0

If you're wondering why backslashes are used, I wrote a blog post recently on how to make your docker commands more readable. Now that you have full concise command to start your SQL database, you can skip the tutorial here.

However, if you are keen to learn how to create a professional and reusable database for your project or SQL practice, you can follow the sections below.

Create a Dockerfile

First to start a MySQL database in a docker container, we have to create a Dockerfile.

# Use an official MySQL image as the base
FROM mysql:8.0

LABEL author="Your Name here"
LABEL description="MySQL Image for training"
LABEL version="1.0"

# Copy the SQL dump file into the container (if needed)
COPY *.sql /docker-entrypoint-initdb.d/

# Configuration files if necessary (uncomment and adjust as needed)
# COPY ./my.cnf /etc/mysql/conf.d/my.cnf

Note that the line COPY below will copy all the sql files in our source folder, where we have our Dockerfile, and add them in the /docker-entrypoint-initdb.d/

COPY *.sql /docker-entrypoint-initdb.d/

This repository in MySQL container is where you can add additional initialization scripts (creating the directory if necessary).

You can add one or more *.sql, *.sql.gz, or *.sh.

The container will run any *.sql files, run any executable *.sh scripts, and source any non-executable *.sh scripts found in that directory to do further initialization before starting the service. Those files will be executed in alphabetical order.

Create a docker-compose file

Once you have created your docker file, now to run the MySQL container in a clean way, you can create a docker-compose.yml file.

services:
  mysql:
    build:
      context: .
      dockerfile: mysql.dockerfile
    image: "mysql-tutorial"
    container_name: ${MYSQL_CONTAINER_NAME}
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
    volumes:
      - mysqldata:/var/lib/mysql
    ports:
      - "3306:3306"
    restart: unless-stopped
volumes:
    mysqldata:
      external: true

The values which are in this form ${MYSQL_ROOT_PASSWORD} have been defined in an env file; in order to be managed easily. To do so, create a .env file in your source folder and add all the environment variables, like below.

MYSQL_CONTAINER_NAME='mysqldb'
MYSQL_ROOT_PASSWORD='secret'
MYSQL_DATABASE='mydb'

Create SQL scripts files

Now, let’s imagine we have a simplified task management app we want to build as a practice project. To do so we will need 3 main tables → tasks, users, and project assignments.

We can create our SQL schema scripts file that must be copied in the folder /docker-entrypoint-initdb.d/ in your database container.

To initiate database data when the container launch, we will create 2 files:

• 01-create-db-schemas.sql
• 02-load-db-data.sql

-- CREATE Users table
DROP TABLE IF EXISTS Users;
CREATE TABLE IF NOT EXISTS Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);


-- CREATE Projects table
DROP TABLE IF EXISTS Projects;
CREATE TABLE IF NOT EXISTS Projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    description TEXT
);


-- CREATE Tasks table
DROP TABLE IF EXISTS Tasks;
CREATE TABLE IF NOT EXISTS Tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(255) NOT NULL,
    description TEXT,
    project_id INT NOT NULL,
    user_id INT,
    due_date DATE,
    status ENUM('TODO', 'IN_PROGRESS', 'DONE') DEFAULT 'TODO',
    FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);


-- CREATE ProjectAssignment table
DROP TABLE IF EXISTS ProjectAssignments;
CREATE TABLE IF NOT EXISTS ProjectAssignments (
    assignment_id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('MEMBER', 'MANAGER') DEFAULT 'MEMBER',
    FOREIGN KEY (project_id) REFERENCES Projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE
);

Once the SQL schemas have been created, you can add the following scripts to load the data.

-- Insert into Users table
INSERT INTO Users (user_id, username, full_name, email) VALUES
(1, 'jsmith', 'John Smith', 'john.smith@example.com'),
(2, 'emiller', 'Emma Miller', 'emma.miller@example.com'),
(3, 'apatel', 'Amit Patel', 'amit.patel@example.com'),
(4, 'llee', 'Liam Lee', 'liam.lee@example.com'),
(5, 'rchambers', 'Rachel Chambers', 'rachel.chambers@example.com');

-- Insert into Projects table
INSERT INTO Projects (project_id, project_name, description) VALUES
(1, 'Website Redesign', 'Redesign the corporate website'),
(2, 'Mobile App Launch', 'Launch the new mobile application'),
(3, 'Cloud Migration', 'Migrate services to the cloud'),
(4, 'Marketing Campaign', 'New product marketing campaign'),
(5, 'Infrastructure Improvement', 'Upgrade IT infrastructure');

-- Insert into Tasks table
INSERT INTO Tasks (task_id, task_name, description, project_id, user_id, due_date, status) VALUES
(1, 'Create wireframes', 'Design initial wireframes for the website', 1, 1, '2024-09-01', 'IN_PROGRESS'),
(2, 'Develop backend', 'Set up backend services for the app', 2, 2, '2024-09-15', 'TODO'),
(3, 'Set up cloud environment', 'Prepare environment for migration', 3, 3, '2024-10-01', 'DONE'),
(4, 'Design campaign assets', 'Create visuals for the campaign', 4, 4, '2024-09-10', 'IN_PROGRESS'),
(5, 'Conduct system audit', 'Review existing infrastructure', 5, 5, '2024-09-20', 'TODO');

-- Insert into ProjectAssignments table
INSERT INTO ProjectAssignments (assignment_id, project_id, user_id, role) VALUES
(1, 1, 1, 'MANAGER'),
(2, 1, 2, 'MEMBER'),
(3, 2, 3, 'MANAGER'),
(4, 3, 4, 'MEMBER'),
(5, 5, 5, 'MANAGER');

We start the names of those 2 files with 01-* and 02-* because these initialization files will be executed in alphabetical order. So we want the database to be created first, then load the data.

Run our MySQL container

Before running our MySQL container, we have specified in our docker-compose file that we will use an external volume.

# Here ↓
volumes:
    mysqldata:
      external: true

In order to have an external volume, we have to create it:

docker volume create mysqldata

Now we can launch our MySQL database with docker compose:

docker-compose up -d

MySQL in action

Once our service is up we can now open our favorite SQL Management software and enter the credentials to connect to the container instance, personally I use DataGrip from JetBrains.

So this is what it’s look once the container is fully started.

In the password field, enter the one which is in the .env file

Once the credentials have been validated and the login is ok, now we can run our SELECT command to test that all the SQL file in the SQL scripts have been executed.

That's it, now you are ready to play with your fully configured docker container.

Wrap up

In this tutorial, we have covered:

• How to create a MySQL database with Docker
• How to configure a docker file and docker compose file
• How to create an external volume to store your SQL data
• How to run SQL scripts when starting a MySQL container with a docker-compose file

You can found all the code snippets of this article on my GitHub down below

BLOG-TUTORIALS/Docker/mysql-docker at master · 1kevinson/BLOG-TUTORIALS

Curated list of repositories and links attached to my blog articles - 1kevinson/BLOG-TUTORIALS

GitHub1kevinson