How to Run MySQL In A Docker Container – CloudSavvy IT

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:

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 initialise 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.

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:

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.

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 initialises 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 initialisation files unless there is no database in the data directory. Wiping the contents of your data directory will trigger a fresh initialisation 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 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.