MySQL Docker Image: A Step-by-Step Guide

This post briefly documents the process of pulling a MySQL Docker image from docker hub and running few basic commands to interact with it.

Introduction

MySQL is a relational database management system based on SQL – Structured Query Language.

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.

Docker is a set of platform as a service products that use OS-level virtualization to deliver software in packages called containers. Containers are isolated from one another and bundle their own software, libraries and configuration files; they can communicate with each other through well-defined channels.

Goals

By the end of this post, you will know how to:

1. Pull a MySQL Docker Image from Docker Hub
2. Run the downloaded MySQL Docker Image
3. Connect to the Database via CLI
4. Interact with the MySQL Database via CLI
5. Interact with the MySQL Database via MySQL Workbench
6. Container Management (Stop, Restart, Stats)
7. Cleanup (Remove)

Minimum Software Requirements

Before starting, ensure you have the following installed on your machine:

• Docker
• MySQL Workbench

Getting Started

Setup

1. Start your local Docker instance if it’s not already running.
   

2. Check for existing MySQL images on your local machine by running: docker images

Step 1 - Pull a MySQL Docker Image from Docker Hub

1. Check all of the available tags that can be downloaded from this link. https://hub.docker.com/_/mysql?tab=tags

2. Pull the MySQL Docker image. For this tutorial, we’ll use version 8.0.26:

docker pull mysql:8.0.26

3. Verify that the MySQL Docker image has been successfully pulled:

docker images

Step 2 - Run the downloaded MySQL Docker Image

Run the MySQL Docker image using the following command:

docker run --name mysql-docker -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=sbat -e MYSQL_USER=sbat -e MYSQL_PASSWORD=sbat -d mysql:8.0.26

In the above command, the following parameters can be configured as per your need.

Parameter	Example Value
--name	mysql-docker
-p	3306:3306 (Ports expose)
MYSQL_ROOT_PASSWORD	password
MYSQL_DATABASE	sbat
MYSQL_USER	sbat
MYSQL_PASSWORD	sbat

List all the running containers to Verify if your container is running.

docker ps

Step 3 - Connect to the Database via CLI

1. Connect to the MySQL container using an interactive CLI session:

docker exec -it mysql-docker mysql -u sbat -p sbat

In the above command, the following parameters are set as configured in the previous step.

Parameter	Example Value
-u	sbat
-p	sbat

2. Other ways to connect to the Database docker image without an interactive session.

Command	Description
docker exec mysql-docker mysql -usbat -psbat -e 'show databases;'	connect to MySQL image without interactive CLI.
docker exec -it mysql-docker mysql -usbat -psbat -e 'show databases;'	connect to MySQL image without interactive CLI.

Step 4 - Interact with the MySQL Database via CLI

Once connected, you can run various SQL commands. Here are a few examples:

1. Lists the databases on the MySQL server host.
   show databases;

2. A synonym for show databases;
show schemas;

3. Select any existing database in the SQL schema.
   use [database_name];

4. List tables in a Database.
   show tables;

5. Quit MySQL shell.
   exit

Step 5 - Interact with the MySQL Database via MySQL Workbench

1. Open MySQL Workbench and create a new connection.

2. Enter the connection details:

Key	Value
Connection Name	Any name of your choice
Hostname	192.168.99.102 (use docker-machine ip to find your Docker Toolbox IP address. usually 192.168.99.102)
Username	sbat
Password	sbat

Step 6 - Container Management (Stop, Restart, Stats)

1. Stop the container.
   docker stop [container_id]

2. List all the containers, including the ones that have finished executing to check if your container has been stopped.
   docker ps -a

3. Restart the container.
   docker restart [container_name]

Cleanup (Remove)

1. Remove the container.
   docker rm [container_name]

2. List all the containers, including the ones that have finished executing to check if your container has been stopped and removed. There wont be any containers listed now.
   docker ps -a

Conclusion

This guide covered the essentials of using a MySQL Docker image, including pulling the image, running it, connecting to it via CLI and MySQL Workbench, managing the container, and cleaning up. With these steps, you can easily set up and manage a MySQL database in a Docker container, providing a robust and flexible environment for development and testing.

Tags

• Docker
• MySQL