How to Set Up and Configure MySQL in Docker

MySQL is a relational database management system based on the Structured Query Language (SQL), the most widely used language for accessing and managing database records. Under the GNU license, MySQL is open-source and free software. Oracle Corporation backs it up.

This blog talks about the 3 steps you can follow to configure and seamlessly set up a Docker MySQL Container for your business requirements. It also gives a brief introduction to Docker and MySQL before diving into the advantages, Docker Install MySQL process, configuration, updating, and management tips for a Docker MySQL Container. 

How to Install MySQL Docker Container?

Here are the steps you can follow to install the Dockerhub MySQL Container:

• Step 1: Pull the Docker Image for MySQL
• Step 2: Deploy and Start the MySQL Container
• Step 3: Connect with the Docker MySQL Container

Step 1: Pull the Docker Image for MySQL

• Begin by taking the appropriate Docker Image for MySQL. You can either download a specific version or choose the latest release, as depicted in the following code snippet:

sudo docker pull mysql/mysql-server:latest

• However, if you want a specific version of MySQL, you need to amend the latest value with the version number as shown in the figure below:

• Finally, you need to verify that the image is now stored locally by listing the downloaded Docker images:

sudo docker images

• The output should contain mysql/mysql-server as a constituent of the listed images:

Step 2: Deploy and Start the MySQL Container

• Now that you have extracted the image from the Docker repository, you can move on to deploying a new MySQL Container with the following code snippet:

sudo docker run --name=[container_name] -d [image_tag_name]

• Amend the [container_name] with your desired name. If you don’t give a name, Docker chooses one for you, by default.
• The -d option tells Docker to run the Container as a Service in the background. Next, amend the [image_tag_name] with the name of the image you downloaded in the previous step. For this example, you can create a container called mysql_docker with the latest version tag based on the following code snippet:

sudo docker run --name=[container_name] -d mysql/mysql-server:latest

• To check if the Docker MySQL Container is running use the following command:

docker ps

• With this command, you can view the newly created container listed in the output. This consists of essential container details, for instance, the status of the virtual environment. The status is modified from health: starting to healthy, once you have completed the setup process. 

Step 3: Connect with the Docker MySQL Container

• Before you go about connecting the MySQL Server Container with the host, you need to ensure that the MySQL Client Package has been installed with the following command:

apt-get install mysql-client

•  Next, you need to open the log files for the Docker MySQL Container to find the root password you generated:

sudo docker logs [container_name]

• Now that you have the mysql_docker container run the following command:

sudo docker logs mysql_docker

• Look through the output and locate the line ‘[Entrypoint] GENERATED ROOT PASSWORD:’. Next, copy and paste the password into a text editor or a notepad for future use. 

• Next, hover to the bash shell of the MySQL Container and type out the following command:

sudo docker exec -it [container_name] bash

• For the container you created as an example, you can run this command:

sudo docker -it mysql_docker bash

• Give the root password that you just copied from the logs file whenever you get prompted. This completes the connection process from the MySQL Client to the Server.

• Having completed the previous step for the Docker MySQL Container setup, change the root server password to protect your sensitive information with this snippet:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '[newpassword]';

• For the above command, replace the [newpassword] clause with a desired strong password.

How to Configure Your Docker MySQL Container?

If you wish to take a peek at the configuration of your Docker MySQL Container, you can find it in the ‘/etc/mysql/my.cnf=’ directory. If you want to modify the configuration, you need to create an alternative config file on the host machine and mount them inside the Docker MySQL Container.

• Step 1: Create a new directory on the host machine with this code snippet:

sudo mkdir -p /root/docker/[container_name]/conf.d

• Step 2: Next, you need to create a customized MySQL config file within that directory with the following command:

sudo nano /root/docker/[container_name]/conf.d/my-custom.cnf

• Step 3: Having opened the file, you can add lines with your desired configuration. For instance, if you want to increment the maximum number of connections to 250 (as opposed to the default value 151), you can add the following lines to the configuration file:

[mysqld]
max_connections=250

You can change the MySQL instance’s configuration by passing one or more environment variables to the docker run command line when starting the MySQL image. If you start the container with a data directory that already contains a database, none of the variables below will have any effect: any pre-existing database will be ignored when the container starts.

You can change the MySQL instance’s configuration by passing one or more environment variables to the docker run command line when starting the MySQL image. If you start the container with a data directory that already contains a database, none of the variables below will have any effect: any pre-existing database will be ignored when the container starts.

• Step 4: Save the file and exit. Now, for the changes to take effect, you need to rerun the MySQL Container after removing it. For this, the container leverages a combination of configuration settings from the newly created file along with the default config files. You can run the Docker MySQL Container and map the volume path with the following code snippet:

docker run 
--detach 
--name=[container_name] 
--env="MYSQL_ROOT_PASSWORD=[my_password]" 
--publish 6603:3306 
--volume=/root/docker/[container_name]/conf.d:/etc/mysql/conf.d 
mysql

• Step 5: Now, to check if the Docker MySQL Container has loaded the configuration from the host, you can run the following command:

mysql -uroot -pmypassword -h127.0.0.1 -P6603 -e 'show global variables like "max_connections"';

• Step 6: You can now check the maximum number of connections for your Docker MySQL Container. It should show 250 connections.

Bonus Setup: How to Connect Host Machine with Docker MySQL?

• Step 1: Start the container and map it to a local port to begin the process. In this step, you will be mapping the port from the Docker Container to a Docker MySQL port on localhost which can be used to connect to MySQL. First, you need to remove the container you created with the following set of commands:

docker stop learn-mysql-docker
docker rm learn-mysql-docker

• Step 2: After removing the container, you can start a new container with the same MySQL Image and map the Docker port to the localhost port. You can do this with the help of the following command:

docker run -p 13306:3306 --name mysql-docker-local -eMYSQL_ROOT_PASSWORD=Password -d mysql:latest

• Step 3: Next, you need to make sure that there are no errors after the above command has been executed. If everything works out fine, the above command will print out a generated container ID. 
• Step 4: After starting the container, you need to connect to MySQL from localhost by either using a GUI tool like MySQL Workbench or the command line.
• Step 5: For the command line, you can use this snippet to get things started:

mysql --host=127.0.0.1 --port=13306 -u root -p

• Step 6: After you’ve executed the command, you will see that you are connected to a Docker-based MySQL instance. You can run any command to check if it’s working fine. 

Managing your Docker MySQL Container

• Step 1: Docker stores data in its internal volume by default, so to check the location of Docker’s internal volumes you can use the following code snippet:

sudo docker inspect [container_name]

• Step 2: For this command, you can see the /var/lib/mysql mounted in the internal volume.

• Step 3: You can even modify the location of the data directory and make one that exists on the host of your Docker MySQL Container instead. Having a volume outside the container lets other tools and applications access the volumes when required.
• Step 4: Next, you need to find an appropriate volume of the host and make a data directory on it with the following code snippet:

sudo mkdir -p /storage/docker/mysql-data

• Step 5: After executing the previous command, start the container again by mounting the previously created directory with this command:

docker run 
--detach 
--name=[container_name] 
--env="MYSQL_ROOT_PASSWORD=my_password" 
--publish 6603:3306 
--volume=/root/docker/[container_name]/conf.d:/etc/mysql/conf.d 
--volume=/storage/docker/mysql-data:/var/lib/mysql 
mysql

• Step 6: If you inspect the container, you should be able to peek at the data within Docker MySQL Container and realize that it’s stored on the host system now.

sudo docker inspect [container_name]

Here are a few essential components to help you manage your Docker Compose MySQL Container:

• How to Start the Container for MySQL?
• How to Restart the Container for MySQL?
• How to Stop the Container for MySQL?

How to Start the Container for MySQL?

The code snippet you can use to start the Docker MySQL Container is as follows:

sudo docker start [container_name]

How to Restart the Container for MySQL?

The code snippet you can use to restart the Docker MySQL Container is as follows:

sudo docker restart [container_name]

How to Stop the Container for MySQL?

The code snippet you can use to stop the Docker MySQL Container is as follows:

sudo docker stop [container_name]

How to Delete Your Docker MySQL Container?

If you wish to delete the Docker MySQL Container, you need to make sure that you’ve stopped it first. Next, you can remove the Docker Container with the following code snippet:

sudo docker rm [container_name]

How to Upgrade your Dockerised MySQL Container?

To upgrade a Docker MySQL Container, you can follow the steps mentioned below:

• Step 1: Stop the MySQL Server for this instance using the code snippet mentioned below:

docker stop mysql57

• Step 2: Next, download the MySQL 8.0 Server Docker image and make sure that you have the right tag for MySQL 8.0.
• Step 3: Start a new MySQL 8.0 Docker Container with the old configuration and server data that have been applied on the host. Run the following command for the same:

docker run --name=mysql80 
   --mount type=bind,src=/path-on-host-machine/my.cnf,dst=/etc/my.cnf 
   --mount type=bind,src=/path-on-host-machine/datadir,dst=/var/lib/mysql 
   -d mysql/mysql-server:8.0

• Step 4: Now, you need to wait for the server to finish startup. You can check the status of the server with the docker ps command.
• Step 5: For earlier versions of MySQL, you need to run the mysql_upgrade utility in the MySQL 8.0 Server Container as follows:

docker exec -it mysql80 mysql_upgrade -uroot -p

• Step 6: Enter the root password for your old MySQL Server and finish the upgrade by restarting the MySQL Server Container as follows:

docker restart mysql80

Key Considerations When Using MySQL with Docker

To use MySQL with Docker effectively, keep the following points in mind:

• Data Storage Options:
  • Default Option: Allow Docker to manage storage with its internal volume management. It’s easy but limits access from the host system.
  • Custom Directory: Create a directory on the host and mount it inside the container. This makes data accessible to host tools but requires ensuring proper permissions and security configurations.
• Database Initialization:
  • MySQL initializes a default database if one isn’t present, which delays incoming connections until initialization is complete.
  • Tools like docker-compose may need a retry loop to handle this startup delay gracefully.
• Handling Pre-Existing Databases:
  • If your data directory already contains a database, omit the $MYSQL_ROOT_PASSWORD variable. It will be ignored, and the pre-existing database remains unchanged.
• Running with Arbitrary Users:
  • To run MySQL with specific user permissions (UID/GID), use the --user flag. Ensure directory permissions are correctly set beforehand.
• Creating Database Dumps:
  • Back up all databases using docker exec to run mysqldump within the container:

$ docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /path/on/host/all-databases.sql

• Restoring from Dumps:
  • Restore databases using the -i flag with docker exec:

$ docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /path/on/host/all-databases.sql

By following these practices, you can ensure reliable storage, efficient backups, and seamless database management with MySQL in Docker.

What is the Advantage of Running a Docker MySQL Container?

• Portability: Consistent performance across development, testing, and production environments.
• Quick Setup: Launch MySQL with a single docker run command.
• Isolation: Avoid conflicts with other services or databases on the host.
• Scalability: Easily spin up multiple MySQL instances.
• Version Control: Seamlessly switch between MySQL versions.
• Backup & Recovery: Simplify data management with volume mounting and built-in tools.
• Lightweight: Lower resource usage compared to virtual machines.

Use Cases for MySQL in Docker

• When you need to quickly bring up isolated database instances, MySQL in Docker works well in development and staging. You can benefit from the fast and easy starting of a database in Docker. Configuring a conventional MySQL installation in a full virtual machine takes much longer.
•  Running MySQL locally on your host will have limitations when your work involves many applications simultaneously. Containers provide full separation of each system’s data and give a unique MySQL server configuration for each of them.
• When you have a demanding production environment, it’s better to go for a dedicated MySQL server. Why because Docker’s performance overheads can stack up in I/O-intensive workloads. Also, it makes the instance accessible to stakeholders unfamiliar with Docker. 

Conclusion

This blog explores the steps for setting up Docker MySQL, highlighting its key features and the advantages of using Docker MySQL Containers. It also provides practical tips for efficient management and configuration.

For seamless data extraction and integration, Hevo offers a no-code, real-time ELT platform that automates your data pipelines. With support for 150+ data sources, Hevo enables effortless data export, transformation, and enrichment, ensuring your data is analysis-ready without the need for coding. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQs