Run MySQL in Docker on Windows

#mysql #docker #windows #aws

Summary

Resolving MySQL/MariaDB database and table name case sensitivity on Windows.

Details

I develop locally on my Windows 10 laptop. When I started my first project that used MySQL as the backend, I installed MySQL on my Windows but noticed that the database and table names were created in lower case. However, the same SQL scripts were creating them with intended casing in AWS RDS.

Here is a sample SQL script.

CREATE DATABASE IF NOT EXISTS `Sandbox`;
USE `Sandbox`;

CREATE TABLE IF NOT EXISTS `MyTable` (
  `Id` int(11) DEFAULT NULL,
  `Name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Running this script in MySQL on Windows creates the following as shown in this screenshot from MySQL Workbench. Database and table names in lower case.

To resolve this, I chose to go the Docker route and install/run MySQL from within a Linux Docker container on Windows.

Here are the steps I took to get this working.

Visit Docker Desktop on Windows for detailed steps. Check your machine configuration and ensure you meet the requirements.
Download and install Docker Desktop on Windows.
Once installed, it should look something like the screenshot shown below. (My Docker version is Version 4.27.2 (137060) at the time of this writing)
With Docker Desktop installed, you can run Docker commands from the Windows Command line.
In this example, I'm going to pull down the Docker MySQL 8.0.36 image by running the following command from the Windows Command Line.

docker pull mysql:8.0.36
Once complete, you can see the image in Docker Desktop when you click on the Images icon on the left as shown here.
Next, we will create a container using this image with the command below. The --name is the name of the container, --env used to set the environment variables for the container, --publish is to publish a container's port(s) to the host and finally --detach is to run container in background and print container ID. Please see Docker CLI for more information.

docker run --name mysql8036-container --env MYSQL_ROOT_HOST=% --env MYSQL_ROOT_PASSWORD=passwordgoeshere --publish 15002:3306 --detach docker.io/library/mysql:8.0.36
You should now see the container named "mysql8036-container" running in Docker Desktop when you click the "Containers" icon on the left.
Next we will connect to this new MySQL instance using MySQL Workbench.
Open MySQL Workbench, go to menu option Database - Manage Connections. Click "New". (My version is MySQL Workbench 8.0 Version 8.0.33 build 2947366 CE)
Enter the following information.
a. Connection Name = MySQL8036-Docker
b. Connection Method = Standard (TCP/IP)
c. Hostname = localhost
d. Port = 15002 (The published port from the Docker run command)
e. Username = root
f. Password = passwordgoeshere (The value passed to MYSQL_ROOT_PASSWORD in the run Docker run command)
g. Then click the "Test Connection" to verify connectivity.
In MySQL Workbench, use the menu option Database - Connect to Database and the connection we created above to connect to the new database instance.
Run the SQL script from above and voila we now have the database and table names with casing as intended.