Understanding Database Containerization

No local MySQL installation necessary

---

Setting up MySQL with Docker

1. Download MySQL image

   • $ docker pull mysql:latest

2. Confirm image

   • $ docker images

3. Create a container

   • $ docker run -d --name test-mysql -e MYSQL_ROOT_PASSWORD=strong_password -p 3307:3306 mysql

     • run: creates a new container or starts an existing one

     • --name CONTAINER_NAME: gives the container a name. The name should be readable and short. In our case, the name is test-mysql.

     • -e ENV_VARIABLE=value: the -e tag creates an environment variable that will be accessible within the container. It is crucial to set MYSQL_ROOT_PASSWORD so that we can run SQL commands later from the container. Make sure to store your strong password somewhere safe (not your brain).

     • -d: short for detached, the -d tag makes the container run in the background. If you remove this tag, the command will keep printing logs until the container stops.

     • image_name: the final argument is the image name the container will be built from. In this case, our image is mysql.

Establish connection between MySQL container and VS Code on local:

1. Ensure that root user of the table mysql.user has an entry for host else, add with update mysql.user set host='%' where user='root';

   • 

2. Connect on your client (MySQL Client, VS Code, etc) with the following parameters:

   • 

The database should now connect to the client.

---

Questions I faced

answered by Google Gemini

1. When I see the port of MySQL on docker with$ docker port test-mysql, we get3306/tcp -> 0.0.0.0:3307. But to connect to this from my local, I need to use the IP 127.0.0.1:3307. Why?

   • Listening on 0.0.0.0, it indicates that it's willing to accept connections from any network interface on the host.

   • In the context of docker port, 0.0.0.0 represents the Docker host's network interface. This means the MySQL service inside the container is listening on port 3306, and Docker is exposing it to the outside world through port 3307.

   • 127.0.0.1 on the other hand is a loopback address referring to the current machine itself (localhost)

2. "When I checked the IP address of the docker container, it was something like 174... So why can I not use that IP, and have to use 127.0.0.1, orlocalhost?"

   • Network Isolation: Docker containers are typically isolated from the host network- and are for internal use of Docker. The IP address you see is internal to the Docker network and not accessible from the host directly.

   • "Also, when MySQL's port is 3306, why do many people do the port mapping from 3306 of docker to 3307 of the local?"

   • Avoiding Conflicts

     • Existing services: If there's already a service running on port 3306 on your host machine, mapping it to a different port prevents conflicts.

     • Multiple MySQL instances: If you're running multiple MySQL instances, using different ports helps differentiate them.

   • Security

     • Reducing attack surface: Some security experts argue that using a non-standard port can deter potential attackers who might target the default port. However, this alone doesn't guarantee security; it's just an additional layer.

3. Why is necessary to have an entry of root:% in user:host of mysql.user table to connect to serve data outside the Docker container?"

   • The root:% entry in the mysql.user table signifies a MySQL root user account that can be accessed from any host (represented by the % wildcard). This configuration is often the default setup in MySQL installations.

   • It is a security risk -- ⚠️ Careful on the PROD

   • Alternate appraoch

     • Create specific user accounts: For example, user1@localhost, user2@192.168.1.100.

     • Use IP address restrictions: Grant access to specific IP addresses instead of %.

     • Leverage hostnames: Use hostnames to restrict access to trusted hosts.

---

Resources

1. Article

2. YouTube Video