Docker containers let you quickly spin up new applications without polluting your system with dependencies. A MySQL database is a common requirement for web-based software. Here’s how to get a new MySQL instance running in Docker in only a few minutes.
Getting Started
The official MySQL image on Docker Hub provides everything needed to get started. Images are available for MySQL versions 5.6, 5.7 and 8.0. Specify the one you want to use as the image tag:
Before deploying, you’ll need to setup a Docker volume or bind mount to persist your database in. Otherwise, your data will be lost when the container restarts. The mount should be made to /var/lib/mysql within the container.
You also need to set a password for the MySQL root user. Use the MYSQL_ROOT_PASSWORD environment variable. MySQL will refuse to initialize if this variable isn’t set. Try to choose a secure password.
Here’s how to run MySQL while satisfying these prerequisites:
The MySQL first-run routine will take a few seconds to complete. You can check if the database is up by running docker logs my-mysql. Check for a line that contains ready for connections.
RELATED: How to Use Docker to Containerize PHP and Apache
Docker Compose
The docker run command can quickly become lengthy, especially if you have complex configuration requirements. Writing a docker-compose.yml file is more maintainable. Here’s an example:
You can start MySQL using MYSQL_ROOT_PASSWORD=secure docker-compose up -d.
RELATED: How to Install Docker and Docker Compose on Linux
Connecting to MySQL
You can gain an interactive MySQL shell by attaching to the container and running the mysql command. This provides the full MySQL command-line interface, so you can use all the familiar commands and flags.
This command opens a shell as the root user and prompts for a password.
To import a SQL file from your filesystem, you can pipe it into the command:
You must setup a port binding if you want to access MySQL from your host. Bind a host port to the container’s port 3306. Add the following to your docker-compose.yml file, within the mysql service definition:
This will bind port 33060 on your host machine to the container’s port 3306. If you’re not using Docker Compose, pass -p 33060:3306 to docker run when you start your container. You’ll now be able to connect to localhost:33060 using MySQL clients running on your host.
If you need to connect from another Docker container, it’s best to use Docker Compose. Define another service for the new container. Both containers will exist in the same Docker network. You’ll be able to connect using port 3306 on the MySQL container’s hostname (this matches the service name defined in your Compose file).
Automatically Creating A Database and User
The MySQL Docker images support optional environment variables that let you manage the first-run database setup. Set these variables to automatically provision a new empty database and user account:
MYSQL_DATABASE – The name of a database schema to create when the server initializes for the first time. MYSQL_USER and MYSQL_PASSWORD – Create a new initial user account, separately to the root user. If you also set the MYSQL_DATABASE variable, your new user account will automatically be granted all privileges on the newly created database. MYSQL_RANDOM_ROOT_PASSWORD – Set this variable to have MySQL automatically generate a new password for the root user. This replaces the MYSQL_ROOT_PASSWORD variable. The generated password will be emitted to the standard output stream on first run. Because of this, you should take care when using this variable in CI environments – the password may be visible to anyone who has access to the job logs!
First-Run Seed Files
You’ll often have tables and data you want to seed into the database as soon as it’s created. The MySQL images are configured to look for .sh, .sql and .sql.gz files in the container’s /docker-entrypoint-initdb.d directory.
Any files found will be executed sequentially. The ordering will always be alphabetical. You can apply filename prefixes (e.g. 1-seed_tables.sql / 2-seed_data.sql) to make sure operations complete in the intended order.
This mechanism lets you easily seed your new database instance with initial data. You can write your own Dockerfile, using MySQL as the base image, and add your seed files into /docker-entrypoint-initdb.d. Alternatively, bind mount a directory on your host into the container directory.
MySQL will ignore initialization files unless there is no database in the data directory. Wiping the contents of your data directory will trigger a fresh initialization when you restart the container.
Tweaking Server Settings
MySQL server configuration files will be loaded from /etc/mysql/conf.d. Add your own configuration files to this directory, using a Dockerfile override or a bind mount, to adjust server settings. You can get the list of available options from the MySQL Server manual.
Using an options file makes it easy to view and edit your configuration in the future. You check your file into version control alongside your project. However, command-line flags are also available for one-off tweaks and experimentation.
This variant of docker run will start MySQL with the general query log enabled. The mysql binary is the image’s entrypoint, so flags passed to docker run after the image name will be forwarded to mysql.
Conclusion
Running MySQL within Docker is much quicker and easier than installing MySQL Server “bare-metal.” You can run multiple MySQL instances without the risk of conflict.
The official Docker images come with useful configuration options and built-in support for first-run database seeds. This lets you get off the ground quickly, whether you’re starting from scratch or using an existing schema.
RELATED: How to Quickly Deploy WordPress as a Docker Container
