MySQL on Docker

What is covered:
- run mysql as docker image
- using default or specific DB file when loading image

Advantages:
- do not need to install mysql or mysqld on dev machine
- can run any version of mysql on your dev machine
- can have many instances of mysql - one for each project - on any port you want

More info on official mysql docker image can be found on dockerhub.

MySQL on docker

Run MySQL

    docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 -d mysql

Runs mysql docker image in container with name "mysql".
This will download latest mysql version. If you want a specific version, then have "-d mysql:8.0.18".

Common environment (-e) variables:

• MYSQL_ALLOW_EMPTY_PASSWORD=yes : To have blank password for root. Use only on dev machines
• MYSQL_ROOT_PASSWORD : Set a password for "root"
• MYSQL_USER, MYSQL_PASSWORD : Use together to create a new user and password when creating docker image
• MYSQL_DATABASE : To create a database on startup

On M1 Mac

For MySQL v8+ (https://hub.docker.com/r/arm64v8/mysql/)

    docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d arm64v8/mysql:<tag>

e.g. to install latest version:

    docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -d arm64v8/mysql

For MySQL older versions

    docker run --name mysql --platform linux/x86_64 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 -d mysql:5.7.42

--platform linux/x86_64 allows Docker to emulate this image in x86_64 architecture. It has performance issues, but works like a charm.

Connect to MySQL

Connect on port 3306 using a client like MySQL workbench.

If you wish to use a command line, then run

    mysql -uroot -h 127.0.0.1

If you do not have mysql installed, no worries. We can use the one in docker image.

    docker exec -it mysql bash

where "mysql" was name of mysql container that is running.

With this command, a bash shell is opened inside container. Now fire mysql -uroot

This opens a mysql shell. We can fire mysql commands here.

[Update June 7, 2022]

New Mac ARM machines have an issue. They throw error that look like

WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested

Please run this command, and then run the "docker run" command

    docker pull --platform linux/x86_64 mysql

MySQL data storage

Default

MySQL stores data in special location on local hard disk.

If we use above mentioned command to run mysql, then docker manages the storage of database data by writing it to disk using its own internal volume management. So where is data on disk? We do not know. It is hidden from user.

User specified location

We can do this if we wish to control where MySQL data is stored. It comes in handy if we delete mysql container and image, and then need to recreate database later.

Step 1: Create folder where we wish data to be stored on hard disk.

Step 2: Mount this as a docker volume when initializing mysql docker container, mount this volume in /var/lib/mysql

Assuming that "/Users/hsingh/study/docker/docker-images/mysql/data_folder_1" is the folder in hard disk where I would want to create database files.

    docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -v /Users/hsingh/study/docker/docker-images/mysql/data_folder_1:/var/lib/mysql -p 3306:3306 -d mysql

-v is used to mount a local folder into docker container's folder as a volume.

Run this for M1 Mac

MySQL version 8+

    docker run --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -v /Users/hsingh/study/docker/docker-images/mysql/data_folder_1:/var/lib/mysql -p 3306:3306 -d arm64v8/mysql

    docker run --name mysql --platform linux/x86_64 -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -v /Users/hsingh/study/docker/docker-images/mysql/data_folder_1:/var/lib/mysql -p 3306:3306 -d mysql:5.7.42