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.
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:
docker pull mysql:8.0
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:
docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=secret -v $HOME/mysql-data:/var/lib/mysql -d mysql:8.0
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 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:
version: "3" services: mysql: image: mysql:8.0 environment: - MYSQL_ROOT_PASSWORD volumes: - mysql:/var/lib/mysql volumes: mysql:
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.
docker exec -it my-mysql mysql -p
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:
docker exec -it my-mysql mysql -psecret database_name < path-to-file.sql
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:
ports: - 33060:3306
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_PASSWORD– Create a new initial user account, separately to the
rootuser. If you also set the
MYSQL_DATABASEvariable, 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
rootuser. This replaces the
MYSQL_ROOT_PASSWORDvariable. 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
.sql.gz files in the container’s
Any files found will be executed sequentially. The ordering will always be alphabetical. You can apply filename prefixes (e.g.
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.
docker run --name my-mysql -e MYSQL_ROOT_PASSWORD=secret -d mysql:latest --general-log=on
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
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