Structured Query Language (SQL) is the most commonly used language for accessing and maintaining database records, and MySQL is a relational database management system built on top of SQL. MySQL is available as open-source software without cost under the GNU license. It’s supported by Oracle Corporation. So, here is a guide on how to create MySQL with Docker. Let’s dive in!
What Is MySQL?
Over 39% of developers around the world used MySQL in 2019, making it the most popular Database. While it doesn’t have as many bells and whistles as PostgreSQL, it’s nevertheless suitable for a wide variety of applications. MySQL is the preferred option for scalable web applications since it is included by default in the LAMP stack (Linux, Apache HTTP Server, MySQL, and PHP).
MySQL is also a small database that can be used by programmers in huge multi-tier applications on production application servers. There are many benefits to integrating MySQL into your process. The following are the benefits of integrating MySQL;
- MySQL supports functions like Master-Slave Replication and Scale-Out.
- MySQL also supports offload reporting and geographical data distribution.
- When utilized for read-only purposes, the MyISAM Storage Engine has minimal overhead.
- MySQL’s Memory Storage Engine can speed up access to frequently used tables.
- A Query Cache is available for frequently used statements.
- Given the abundance of blogs, white papers, and books on the subject, MySQL is simple to understand and debug.
- MySQL can also adapt to your needs and grow as your business does.
What Is a Docker?
Docker’s goal is to streamline and standardize the software development process. Docker accomplishes this by eliminating time-consuming configuration steps across the development lifecycle, making it possible to create mobile applications for both the Cloud and the Desktop with ease, speed, and portability. Docker’s complete end-to-end platform includes command line interfaces, graphical user interfaces, application programming interfaces, and stringent security features, all designed to function together throughout the whole application delivery lifecycle.
Docker Containers are a standardized piece of software that allows programmers to separate their app from their host system’s environment, resolving the issue of confined operations. Docker has rapidly become the de facto standard for millions of developers across all platforms, from the Cloud to Desktops, when it comes to sharing and constructing containerized applications. It facilitates rapid deployment by requiring only a single package installation. Docker allows you to maintain uniformity across development and production environments while testing and coding locally.
Read Also: TOP 11 BEST DOCKER ALTERNATIVES 2023: Reviewed
Prerequisites
The following are the prerequisites:
- Having a terminal or command prompt available to you
- A user who can log in as root or who has sudo capabilities
- Preexisting Docker setup
Running a MySQL Docker Container
Deploying MySQL in a container is a fast and efficient alternative if you need to set up a database quickly and without using too many resources. You shouldn’t use this for anything larger than a medium-sized project. A MySQL Docker container is not also powerful enough to handle the demands of enterprise software.
Docker is a popular tool for setting up databases for lightweight applications. Also, a MySQL database container can be used in place of a dedicated server to house databases. Your machine can host several containers simultaneously. The deployed program or software is packaged into individual containers, which also share the host’s kernel and libraries. Because of this, the database is incredibly small and quick to boot up.
How to Create Mysql With Docker
Docker’s database setup process entails nothing more than creating a container from a preexisting MySQL image. To get your MySQL container set up and operating, just follow the instructions. Here is how to create Mysql with Docker:
#1. Pull the MySQL Docker Image
To begin, get the right MySQL Docker image. The following command demonstrates how to download either a specific version or the most recent update:
sudo docker pull mysql/mysql-server:latest
Simply substitute the desired MySQL version number for “latest” in the preceding statement.
List the downloaded Docker images to make sure the image has been successfully saved locally:
sudo docker images
Mysql/mysql-server ought to be one of the specified images in the output.
#2. Deploy the MySQL Container
If you already have the image, you can proceed to deploy a new MySQL container by using:
sudo docker run –name=[container_name] -d [image_tag_name]
- Substitute a different name for the container for [container_name]. Docker will assign a random name if you don’t specify one.
- When the -d flag is used, Docker operates the container in the background as a service.
- Substitute the name of the image you downloaded in Step 1 for [image_tag_name].
In this example, we make a container called mysql_docker that has a tag for the latest version:
sudo docker run –name=[container_name] -d mysql/mysql-server:latest
The next step is to verify the operation of the MySQL container:
docker ps
The newly generated container should appear in the output. The current state of this virtual environment is one of the container details included. Once the initial configuration is finished, the status will change from healthy: starting to healthy.
#3. Establish a Connection to the MySQL Docker Image
- The MySQL client package must be set up before the MySQL server container can communicate with the host.
apt-get install mysql-client
- The generated root password can be found in the logs file for the MySQL container, which you should access next.
sudo docker logs [container_name]
- When using the mysql_docker container, we perform:
sudo docker logs mysql_docker
Navigate the output and locate the line beginning with [Entrypoint]. Copy and paste the generated root password into a text editor or notepad for safekeeping.
- To access the MySQL container’s bash shell, type:
sudo docker exec -it [container_name] bash
The following commands are executed on the example container:
sudo docker -it mysql_docker bash
When prompted, enter the root password you retrieved from the logs file. Now the MySQL client may communicate with the server.
As a final step in data security, you should update the server’s root password.
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘[newpassword]’;
Substitute a secure password of your choosing for [newpassword].
How to Configure Your Docker MySQL Container?
You can view the current settings for your Docker MySQL Container by going to the ‘/etc/mysql/my.cnf=’ directory. If you want to make changes to the configuration, you’ll need to create a new config file on the host system and then mount it inside the container.
Step 1: This code snippet will generate a new directory on the host system.
sudo mkdir -p /root/docker/[container_name]/conf.d
Step 2: Then, issue the following command inside that directory to generate a unique MySQL config file:
sudo nano /root/docker/[container_name]/conf.d/my-custom.cnf
Step 3: After the file is opened, new lines can be added with the necessary settings. For instance, the following lines can be added to the configuration file to increase the maximum number of connections from the default value of 151 to 250.
[mysqld]
max_connections=250
By appending one or more environment variables to the docker run command line before beginning the MySQL image’s execution, you can alter the MySQL instance’s configuration. The following settings will have no effect on the container’s behavior if it is started with a data directory that already contains a database.
Mysql Root Password
Password for the MySQL root superuser account; a mandatory variable. In the previous instance, my-secret-pw was used.
Mysql Database
You can specify the name of a database to be established at image startup by setting this optional variable. This database will treat the user who supplied the username and password as a superuser, giving them full access (the same as GRANT ALL).
Mysql User and Mysql Password Are Two Variables in Mysql
Create a new user and specify their password with these optional settings. This user will be given superuser privileges (as described above) on the database set by the MYSQL DATABASE variable. For a user to be made, both conditions must be met.
The root superuser is automatically established with the password set in the MYSQL ROOT PASSWORD variable, therefore there’s no need to use this procedure to do so.
Mysql Allow Empty Password
This is a voluntary parameter. To enable container startup without the root user’s password, set it to a non-empty value such as yes. WARNING: If you set this variable to yes, any user will be able to get full superuser access to your MySQL instance.
Mysql Random Root Password
This is a voluntary parameter. A non-empty value, such as yes (using pwgen), will cause the system to generate a random password for the root user. Generated root password: (GENERATED ROOT PASSWORD:…..) is output to standard output.
SQL One-Time Password in Mysql
After init finishes, the root (not the MYSQL USER!) user will be listed as expired, so the first time you log in, you’ll need to change your password. Any non-null value will trigger this preference. Please take note that MySQL 5.6 or later is required to use this function. During MySQL 5.5’s initialization, a useful error will be thrown if this setting is used.
Mysql Initdb Skip Tzinfo
By default, the entry point script will load the necessary timezone data for the CONVERT TZ() function. If a non-empty value is provided, time zone loading will be inhibited.
Step 4: Keep your work and log out. Now that the MySQL Container has been deleted, you must restart it for the modifications to take effect. To accomplish this, the container makes use of both the new configuration file and the existing container configuration files. The following code snippet will launch the MySQL Docker Container and set up the volume mapping:
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 you can run this command to see if the Docker MySQL Container has successfully imported the host’s configuration:
mysql -uroot -pmypassword -h127.0.0.1 -P6603 -e ‘show global variables like “max_connections”‘;
Step 6: Now you can see how many concurrent connections your Docker MySQL Container can handle. The recommended number of links is 250.
Start, Stop, and Restart MySQL Container
When the process inside the container terminates, the container exits as well.
- It is possible to launch the MySQL container by typing:
sudo docker start [container_name]
- To terminate the MySQL container:
Type: sudo docker stop [container_name]
- The MySQL container can be restarted by typing:
sudo docker restart [container_name]
Delete MySQL Container
You must first terminate a running MySQL container before deleting it.
After that, you may uninstall the docker container by:
sudo docker rm [container_name]
Why Would You Want to Use a MySQL Container in Docker?
You may significantly shorten the time it takes to create new features by using a Docker MySQL Container to create code with consistent settings and infrastructure. Keep in mind that Docker Images are typically quite compact to begin with. New application containers also arrive quickly and can be deployed in less time. The ease of upkeep is another major plus for using Docker MySQL Containers. When an app is containerized, it is separated from other programs using the same server. Since programs don’t interact with one another, this simplifies application upkeep.
Should I Run MySQL in Docker?
You can keep your database and code completely independent by running MySQL in a Docker container. In addition to scaling your API server instance separately from MySQL, you can use a container orchestrator like Kubernetes to do so. Consistency is another advantage of using containers.
What Is the Difference Between MySQL and MariaDB?
MariaDB outperforms MySQL in both scalability and query speed. Because of this, it is useful for handling massive amounts of data. MariaDB also has additional capabilities beyond those of MySQL, such as sequence storage engines and virtual columns. However, multiple engines can be used in a single table.
Can I Run a Database in Docker?
Docker allows you to simulate a remote server environment and test your application’s connectivity to a database.
What Is the Best Platform to Run Docker on?
The following are the best platforms to run Docker on:
- Kamatera.
- Amazon ECS.
- AppFleet.
- Heroku.
- Google Cloud Run.
- A2 Hosting.
- Digital Ocean.
Is Docker Still the Best Container?
Docker is not required as a container runtime in Kubernetes, but it still plays an important role in the Kubernetes ecosystem and in your process. Even today, Docker is also widely used as a tool for creating and executing container images locally.
Final Thoughts
You should be able to open a MySQL container after reading this article. Docker and MySQL together can be a great tool for a lightweight program. The time has come to begin investigating MySQL container capabilities.
- Best Yard Management Software in 2023:(Reviewed)
- How To Share WiFi Password to Mac
- TOP OCTOPUS DEPLOY COMPETITORS & ALTERNATIVES 2023
- APT-GET UPDATE VS APT-GET UPGRADE: Whats the Difference?
References
