We are earthly. We make construction software simpler and therefore faster. This article is about data management for MySQL containers. If you’re interested in a simple, containerized approach to building software, check us out.
Docker is one of the most popular platforms for developing and deploying containerized applications. Containers are isolated environments that contain an application along with all the software packages it needs. With Docker, you can run or scale your application in any environment.
MySQL is one of the most popular SQL-compatible relational databases. Running MySQL inside a Docker container allows you to separate your database from your code. You can also use a container orchestrator such as Kubernetes to scale MySQL independently of your API server instance.
Using containers gives you the benefit of consistency. Once you’re done building your system, you can deploy your containers to the cloud without manually installing and configuring MySQL on bare metal hardware.
In this article, you’ll learn how you can Dockerize your database, what you need to know first, and why you should try it.
While using Docker with MySQL simplifies many aspects of deployment, such as installing the server and creating a database, it comes with some technical issues. The most important is data storage: Docker is primarily designed around stateless containers, whereas a MySQL database is inherently stateful.
You must use Docker volumes when deploying a MySQL container. Volumes provide a mechanism to preserve files after the container is stopped. You will lose your database if you restart a MySQL container that does not use volumes.
Volumes store data outside of a single container. After your MySQL container stops, files stored on your mounted volumes will remain accessible on your host. You can remount volumes into new containers, preventing data loss after replacing your MySQL instance with a new image version.
Use cases for MySQL on Dockerized Docker MySQL
works well in development and staging environments where you want to quickly display isolated database instances. It’s much faster and easier to start a database in Docker than setting up a conventional MySQL installation on a full VM.
Although you can run MySQL locally on your host, this becomes limiting when you are working on multiple applications simultaneously. Using containers offers complete separation of data from each system and the ability to provide a unique MySQL server configuration for each.
There are a few scenarios where choosing to Dockerize your database can be less impactful. Demanding production environments might be better off with a dedicated MySQL server. Docker’s performance overheads are modest, but can add up to I/O-intensive workloads such as those on a write-heavy database. A full production server also keeps the instance accessible to people with database maintenance roles who are unfamiliar with Docker.
However, Docker is perfectly capable of supporting MySQL database deployments, from local development environments to production. Using it for your entire cycle ensures consistency. If your production instance uses the same Docker image as the development, you can be confident that your live systems will behave predictably. Here’s how to run a MySQL server in a Docker container.
MySQL container startup
MySQL has an official Docker image available on Docker Hub. First identify the image tag to use. MySQL versions 5.6, 5.7 and 8.0 are available.
The last label points to the latest version, currently 8.0. Avoid using this tag, as it means you might unintentionally receive a major MySQL version update in the future. Referring specifically to the version you want allows for a more controlled approach to updates.
When you start a MySQL container for the first time, an initial root user will be automatically created. You must provide a password for this user or ask MySQL to generate one. Here is an example of running a basic
MySQL container with a specified root password:
This command starts a container with MySQL 8. The password for the root user is set manually. The -d flag means that the container will run in the background until it stops, regardless of the terminal session. You can view container startup logs with docker logs mysql -follow. When “ready for connections” appears, the MySQL database can be accessed.
The -restart parameter tells Docker to always restart the container. This means that your MySQL database will run without intervention after the host machine reboots or Docker daemon updates. The stop policy unless used here will not start the container if you manually stopped it with docker stop.
Docker’s -p flag allows port forwarding to the container, so you can access your database at localhost:3306. This is the default MySQL port; This example forwards port 3306 on the host to the same port inside the container. Use your favorite MySQL client to connect through this port with root and your chosen password as user credentials.
Without port forwarding enabled, you will only be able to access your database from the container. You can do this at any time using docker exec to get a shell inside the container:
This command executes mysql -p inside the mysql container. -it flags mean that the input stream from your terminal will be forwarded to the container as an interactive TTY.
a Docker container
Persistence of data with
volumes While the container
created above is a fully functional MySQL server, you must configure volumes so that your data is not lost when the container stops. The MySQL Docker image is configured to store all its data in the /var/lib/mysql directory. Mounting a volume in this directory will allow persistent data storage that survives any single container instance.
Stop and delete your old container to avoid naming conflicts
: Next, start a new container
with the revised configuration:
Using this command to start your MySQL container will create a new Docker volume named mysql. It will mount to the container in /var/lib/mysql, where MySQL stores its data files. All data written to this directory will now be transparently stored on the Docker-managed volume on your host.
steps to stop
and remove the container:
Repeat the docker run command with the same arguments. Since the volume named mysql will already exist, the new container will retain the data created by the old one. If you want to destroy the volume, use docker volume rm mysql.
Using container networks
In the examples above, port forwarding was used to expose the MySQL server on its host’s network. If you’re only going to connect to MySQL from another Docker container, such as your API server, a better approach is to create a dedicated Docker network. This improves security by limiting database exposure.
First create a Docker network for your application
specify this network when launching your
Connect another container to the same network:
Your API and MySQL containers now share a network. You can connect to MySQL from your API container by referencing the host name of the MySQL container. This matches the container name by default. Here your application must connect to port 3306 on the mysql host.
The official MySQL image supports several environment variables that you can use to configure the initial state of the container. You’ve seen one, MYSQL_ROOT_PASSWORD. Use the -e flag with docker run to set each of these variables. They are only honored the first time the container is started, when the MySQL data directory is empty.
MYSQL_DATABASE – The name of a database schema that will be created when the container starts.
- MYSQL_USER and MYSQL_PASSWORD – Create a new ordinary user when the container starts
- MYSQL_RANDOM_ROOT_PASSWORD – Set this instead of MYSQL_ROOT_PASSWORD if you want MySQL to generate a strong root password for you. If you enable this setting, the password will be issued to the container logs (accessible via the docker logs command) during the first launch. It will not be possible to recover the password afterwards.
- MYSQL_ALLOW_EMPTY_PASSWORD – By configuring this, the root user will be created with an empty password. Use this option only for disposable DB instances. It is insecure and would allow anyone to connect to MySQL with superuser privileges.
Using these environment variables means that their values will be visible to anyone who can inspect your container. A more secure approach is to use Docker secrets or volumes to inject values as files.
The MySQL image supports an additional variant of each of the above variables. Suffix the name of a variable with _FILE so that its value is interpreted as a path to a file that contains the actual value. This example securely sets the root user’s password in a way that cannot be inspected from outside the container: the password
to a file that is mounted in the container by a Docker volume. MySQL indicates that the password is obtained from that mounted file through the MYSQL_ROOT_PASSWORD_FILE environment variable. Anyone who views the container’s environment variables will see the file path instead of the plaintext password.
a custom image
It can be useful to create your own Docker image if your application requires a custom MySQL configuration. Adding additional layers on top of the official MySQL base image gives you a ready-to-use image where you can skip manual injection of a MySQL configuration file.
Here is an example my.cnf that changes some MySQL configurations
The MySQL image loads configuration files stored in the /etc/mysql/conf.d directory. Files will only be read when the MySQL server starts, which is when you start the Docker container. To get the configuration in the container, use another Docker volume to link the file mount or use a Dockerfile to bake the changes into a new
You can now run your image to start a MySQL instance that automatically uses your configuration file:
Since the custom image is based on the official version of Docker Hub, you can use all the existing environment variables described above
Running MySQL in a Docker container provides consistency and isolation across environments for database deployments. You can use the official MySQL image as-is or create a custom image.
Once you’re ready to go into production, you can reuse your development workflow to activate your database. Automate the process by launching your containers within your CI/CD pipeline, where tools like Earthly can provide repeatable builds and information about any failures. Earthly offers on-demand Docker daemons and high reproducibility to help you automate your builds faster.