Containerized MySQL Database

Containerization is the good approach than running as manual.
You can run your database as container. If you’ve installed your database in your machine, it will run in background the whole time and it will take some space of RAM in your machine. To avoid that case, you can containerized your database.

Here is the tutorial for running MySQL database as container.

1. Install Docker Desktop from docker official website. If you are using window, you need to enable Hyper-V in your laptop. https://www.docker.com/products/docker-desktop/
   - After installation was success, please note that docker will open when you turn on your computer as default and it will take space on RAM of your machine. So, you need to set the setting that not to open always when you turn on your computer (login account) to reduce unnecessary taking space when you are not using related with docker.
   - Open docker desktop and you can see docker icon which are at noti bar. (Window user need to expand arrow which bottom right corner to see docker icon whereas Mac user can see at top noti bar).
   - Right click on docker icon and click Dashboard, after docker desktop was open, click setting icon (top right corner) and uncheck (untip) mark on “Start Docker Desktop when you login” in General tab. Click “Apply & Restart”.
   - With that setting, you can close, open whatever you want and it will not open always when you turn on your computer anymore.
2. We need below steps to run MySQL Database as container.
   * Create Network
   * Create Volume
   * Run MySQL as Container
   * Check MySQL container
   * Check Network driver and Volume
   * Connect DB Container From Other Containers

First of all, please uninstall your MySQL Database if you already installed MySQL db with installer.
Please type in your command prompt (CMD) or Terminal for below commands as this is step by step guide. (Please make sure you already opened Docker Desktop).

1. Create network

• One of the reasons of using Docker Network is Docker containers and services are so powerful is that you can connect them together, or connect them to non-Docker workloads. (ref — docker official side)
• Whatever, you don’t use custom network or use network is ok. But here, I will use my custom network. *There are 5 network drivers, Bridge, Host, Overlay, MacVLAN, None.
• Bridge is default network and all containers linked this network if not specify exactly and it have an internal IP address which they (containers) communicate with each other easily.
• Host is public network which use host ip address and multiple containers can't run on this host network. You can go search for more about network types in google.
• Here, I will go my MySQL db with my custom Bridge network name called mysql which will go under Bridge network driver.

docker network create mysql

• here, you can give network name as you want.
• here, you can check your network by typing docker network ls
• here, you can delete docker network by typing docker network rm {your_network_name}, to know the active network, you can check by typing above docker network ls command.
• If you want to delete docker network, you have to disconnect or delete container first which connect to this network.

2. Create volume

• For some case, what if we delete the database container and if we start the container from image again?
• Yes, it will delete your old database data too.
• For that case, we need volume to backup our data (persist our data)
  eg. sharing our data to other folder which outside of our db container or take data from outside of our db container like store data in other directory.
• There are two ways to backup our data.
  1. Volume
  2. Bind mount
• Volumes are easier to back up or migrate than bind mounts and volumes is control by docker as it’s created inside docker.
• Bind mount is a just directory (folder) on the host (laptop) file system which is like storage area which doesn’t depend (own) on the container.
• Here, I will use Volume for persisting our data.

docker volume create mysql-volume

• You can check your volume by typing docker volume ls.
• If you want to delete volume, you can type docker volume rm {your_volume_name}, to know the active volume, you can check by typing above docker volume ls command.

3. Run MySQL container

• Normally, if you want to run container, you need to have docker images first.
• You can get images by pulling images by `docker pull {image_name} from docker hub if exist or creating (build) your own images if not exist in docker hub.
• In here, MySQl images is already in Docker Hub cloud and if exist in docker hub, you don’t need docker pull command. It will pull automatically when docker container run.
• So, you can skip pulling MySQL image from Docker hub because if docker can’t find existing local images, it will auto pull from docker hub.
• But if you want to go step by step, you can type docker pull mysql:latest and after pulling was finished, you can type below docker run command.
• You can just directly type docker run command rather than docker pull {image name} command like below.

docker run --name=yw_mysql -p3306:3306 --network mysql -v mysql-volume:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root --restart unless-stopped -d mysql:latest

• here, — name is your container name and you can give as you want.
• -p mapping container port
• — network is to run our app under our custom define network which is in first step. You can also use — net and it’s same.
• -v or — volume is to connect our created volume which is in above step and give directory of the volumn which volume directory is inside docker as I mentioned in above docker will control that volume. Path is inside container and you can give as your want.
• -e option is ENVIRONMENT variable which is to define, root password, user, password, database, etc. (if you create user with MYSQL_USER environment, you need to give permission). You can set with .env file for adding environment values than adding on the fly (here, I don’t use .env file).
• — restart need to add policy, there are 4 policy, no (default, if no set), on-failure, always, unless-stopped.
• I used unless-stopped policy because I don't want auto restart my MySQL container on both manually stopped condition and docker daemon stopped conditions. You can use always policy if you want auto restart after docker application was started. for more details about restart policy, you can check in Docker restart policy.
• -d is for detach mode (run in background and hiding logs), you can look logs for your container by typing docker logs -f -n 100 {container_id}
• latest is for MySQL latest version and you can add other version as you want like mysql:5.7, etc. (current is 8.0.31)

4. Check MySQL Container

• Check MySQL images is already in or not by typing

docker images

• Here, you can see image name, image id, etc.
• Check MySQL container is running well or not by typing

docker ps

• Here, you can see image name, container name, container id, ports, etc.
• Go inside MySQL docker container

docker exec -it {mysql_container_id} /bin/bash

• here, you can leave container by typing exit command inside container.
• you can also get inside container by container name like docker exec -it {mysql_container_name} /bin/bash
• So, you can use container name for clear text instead of using long container id for doing the actions with related with container.
• after you get inside container, enter MySQL console

mysql -uroot -proot

• here, -u is for username and -p is for password.
• here, if you get login into MySQL Constol with above, you can go directly to below Successfully Login section by skipping below steps.
• but, if you can't go MySQL console by authentication root you need to keep go down as below step by step.
• type exit to leave container.

Clear

• Clear everything by below steps.
• Delete Container, before deleting container, you need to stop MySQL container by typing docker stop {mysql_conatiner_id}, to know the mysql container id, you can type docker ps.
• Delete(remove) MySQL container by typing docker rm {mysql_conatiner_id}.
• After container was deleted, you can delete volume by typing docker volume rm {volume_name}, to know your volume that you created in above No.1, you can type docker volume ls command.
• After container was deleted, you can delete image by typing docker rmi {mysql_image_id}, to know the mysql image id, you can type docker images.
• You can even delete the network, actually, network is no need to delete if you want clear your existing db cache. Above steps is enough.
• After everything was clear, Run below command with create user (name-user, password-root) while running MySQL container like below.

docker run --name=yw_mysql -p3306:3306 --network mysql -v mysql-volume:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=root -e MYSQL_USER=user -e MYSQL_PASSWORD=root --restart unless-stopped -d mysql:latest

• Here, I added user and password while running MySQL container by using environment.
• After running MySQL container with adding environment user and password again, check container and go inside MySQL container.
• After you get inside container, you can test login into mysql console by typing mysql -uroot -proot again or type mysql -uuser -proot.
• If you get inside Mysql Console with root user it’s ok and do below process, but you can only get inside only with user, you need to grant access to this user as that user doesn’t has any privileges for now. for more details, find in google.

Successfully Login into MySQL Console

• After you get inside MySQL console successfully with root user, please type below command to check host is allow for public or not.
• Type in MySQl console (inside MySQL container, inside MySQL console).

SELECT host, user FROM mysql.user;

• here, if you saw like below, it’s ok.

• Please note that, % under host tab is bind with root user should be seen. It's mean allow any host with root user.
• If you see that % with host like above table,
• That means it’s ok and you can go directly to below check network and volume section by skipping below steps.
• But if you don’t see % under host tab,
• You need to to allow any host to connect db to your user eg. root user.
• Type

UPDATE mysql.user SET host='%' WHERE user='root';

• and type FLUSH PRIVILEGES; to reload all changes.
• and type SELECT host, user FROM mysql.user; again and check host is % for root user or not.
• If you see that % host with root user, you can go directly to below check network and volume section by skipping below steps.
• But if you still don’t see % in host or your above update query is not ok, Create other users like below and update to that user for host to %.

Create other user

• You can create other users in MySQL console to connect db by typing

CREATE USER 'test'@'%' IDENTIFIED BY 'testpassword';

• Here, sample username is test and password is testpassword and you can change username and password as you want.
• Give grant access permission for creating table, etc to that new user by

GRANT ALL PRIVILEGES ON *.* TO 'test'@'%';

• here, test is username, and you need to use your given username in there.
• For some case with creating new users, if you want to set host to % with that users, you can update like below

UPDATE mysql.user SET host='%' WHERE user='test';

• Update changes by typing below query,

FLUSH PRIVILEGES;

• If you create other users with localhost instead of % like
  CREATE USER 'test'@'localhost' IDENTIFIED BY 'root'; query or and grant permission with GRANT ALL PRIVILEGES ON *.* TO 'test'@'localhost'; .
• For that above case, you need to set host to % like UPDATE mysql.user SET host='%' WHERE user='test'; and type FLUSH PRIVILEGES; to reload all changes.
• If you ok with root user and host is %, you can use that root user from outside like your internal IDE properties file to connect MySQL db or you can connect with GUI by using that user. (db host - localhost, db username - root, db password - root).
• If you ok with other new user for host is %, you need to connect by using that new username and password from outside. (db host - localhost, db username - your given name, db password - your given password).
• If you still error with host to %.
• Type exit to leave MySQL console and type exit again to leave container and do clear all (container, volume, images) that I already dropped step by step in above clear section.
• And run docker mysql container with below command and test like above steps.

docker run - name=yw_mysql -p3306:3306 - network mysql -v mysql-volume:/var/lib/mysql -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=root -e MYSQL_USER=user -e MYSQL_PASSWORD=root - restart unless-stopped -d mysql:latest

• - Here, I added root host to %.
  - But you need to do testing above steps for sure like testing host by SELECT query. If not still ok with root user, you can test by creating other user like above steps.
• If everything was fine, type exit to leave MySQL console and type exit again to leave container.

5. Check Network driver and Volume

• If you are in MySQL console or MySQL container, type exit to leave MySQL console and type exit again to leave container.
• You can check Network driver and Volume as below command

docker inspect {mysql_container_id}

• here, you may see a lot of setting for your container,
• scroll down and you can see Networks tab under NetworkSettings in there and check mysql is under Networks that we already create network in first step.
• Which mean our container is going under our custom define network.
• scroll up and you can see Binds": [ "mysql-volume:/var/lib/mysql" ] under HostConfig tab and you can see mysql-volume that we already create volume in second step.
• Which mean our container is attach with our custom volume.
• If not see mysql network or mysql-volume, please do clear all like above clear section.
• And run mysql container again and please make sure in run command for --network mysql and -v mysql-volume:/var/lib/mysql are same with your created network name and volume name.

6. Connect DB Container From Other Containers

• If everything was fine for running MySQL db as container.
• Add database username to root or your given name and password to root or your given password in your application properties file.
• If you don’t want to use root user, you can create user as per above create other user section.
• You can connect Containerized MySQL db from outside application (your host, like running in IDE or run as jar) by adding localhost or 127.0.0.1 in database datasource url connection string in your application properties file.
• But you can’t connect to Containerized MySQL db with that localhost or 127.0.0.1 when your application was running as a container.
• There are two ways to call containerized MySQL db from containerized application which to add in your application properties file in your db connection string
• 1. Using container name
  - You need to add mysql container name to connect between container.
  - Add below db url connection string in your application properties file.
  eg.spring.datasource.url=jdbc:mysql://yw_mysql:3306/{your_database_name} . Here, I used yw_mysql because I set name for container as yw_mysql in docker run command.
• 2. Using container ipaddress
  - I suggest to use above container name instead of using this below container ipaddress.
  - You can find ipaddress to connect between containers
  - Type in cmd or terminal

docker inspect {mysql_container_id} | grep -i ipaddress

• - Here, you can see address like “IPAddress”: “172.20.0.2”.
  - Add below db url connection string in your application properties file.
  eg.spring.datasource.url=jdbc:mysql://172.20.0.2:3306/{your_database_name} .
• I recommend to use container name instead of using ipaddress while connecting to containerized db from containerized application.
• Please make sure adding --net=mysql (network name which we created in step 1) while running your application as container to connect this MySQL container.
• If not so, you can’t connect to Containerize MySQL db from your Containerized application as network is not same.
• If you don’t want to run your application as container, you can still use localhost or 127.0.0.1 which I mentioned in above.
• You can’t connect to MySQL from your localhost (application which running in IDE or command line) by using above your db container name or container ip address.
  So, use localhost or 127.0.0.1 to connect this containerized MySQL DB when you run your application in your local or connect from you local Database GUI client like DBeaver.

Example project to connect Containerized MySQL DB can be found here, spring-boot-jpa-docker.

7. Conclusion

Overview Diagram for this tutorial

• Finally, you could run MySQL database as container.
• If you don’t want to do things which related with database,
• You can manage with start or stop your container by docker stop {mysql_container_id}, docker start {mysql_container_id}.
• You can get your stopped MySQL container id by docker ps -a because you can't get container id by docker ps, if it is stopped.
• So, If you want to use MySQL, you can start. No need to re-run the whole process, it's just docker start {mysql_container_id} to get that stopped container id, you need to type docker ps -a.
• If you don’t want MySQL as for a while, you can stop. And if you want back, you can start again like above docker ps -a and docker start {mysql container_id}.
• So, it will reduce RAM space on your laptop to get better performance than you install MySQL database with installer because installer will run application in background the whole time.
• Please note that if you run your db as container, your db will run the whole time in background.
  So, you should stop when you are not use that db. This is one of the good approach of using containers that we can manage our containers as we want.
• Database won’t run when Docker was close. But if you open docker, it will automatically run as background unless you don’t stop container manual with Docker stop keyword.
• You can do containerize other db like PostgreSQL, etc as you want. All you need is your want db image is existed in docker hub.

Below is the sample Spring Boot JPA Docker project to connect to this containerized MySQL DB as for reference. You can check it out.

GitHub - yewin-mm/spring-boot-jpa-docker

Below is my GitHub profile and you can checkout all of my projects under repository tag, If you satisfied with my projects or you got some help from my projects, please help me also by giving star on GitHub and give recommendation in LinkedIn as I dropped my LinkedIn profile in below Contact Me section.

Ye Win Github — https://github.com/yewin-mm

✉️ Contact Me

Name — Ye Win
LinkedIn profile — Ye Win
Email Address — yewin.mmr@gmail.com
WhatsApp — +959252656065
Website — https://yewin.me

Project Link: MySQL DB Container

🥰 Becoming a Sponsor

If you like any of my projects or if you want to support my work, please kindly consider becoming a sponsor. It gives me great motivation and I can relentlessly maintain my projects and contribute to the open-source community.

Buy Me A Coffee ˗ˏˋ☕ˎˊ˗