Small Sharp Software Tools

Harness the Combinatoric Power of Command-Line Tools and Utilities

../Tutorials

Run a Development Mysql Server Using Docker Compose

tools Docker

Published February 14, 2023 and last verified on March 9, 2024

Running a development database directly on your machine can be messy. You’ll end up with files and services you might not always need. Installation can be tricky, and removing the server once you’re done can be trickier still.

One solution is to run servers like this using containers that map volumes and ports to the host machine.

In this tutorial you’ll run MySQL in a Docker container, using Docker Compose to map the volumes and ports so you can persist data between runs and connect to the server as if it were installed directly on your machine. Then you can remove it all when you’re finished.

What You Need

To complete this tutorial, you’ll need the following things:

  • Docker and Docker Compose installed.
    • On macOS, you can use Colima or Docker Desktop for Mac.
    • On Windows, you’ll use Docker Desktop for Windows,

Create a docker-compose File for MySQL

Docker Compose lets you specify container configurations using YAML. While it’s often used to set up multiple containers at once, it’s also a handy way to manage a single container. Using Docker Compose, you can specify port mappings and volume mappings in addition to environment variables.

Create a docker-compose.yml file in your project with the following settings to create a MySQL container:

version: "3.5"
services:
  mysql:
  image: mysql:latest
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: roo:
      - /tmp/data:/var/lib/mysql

This configuration maps /tmp/data/ to the /var/lib/mysql folder within the container so your data will persist between runs. You need to use a directory that your Docker daemon can access with write permissions, as MySQL will attempt to change ownership over this directory when starting. If you encounter errors with permissions, ensure your Docker daemon allows the directory you’re trying to use. The /tmp directory is usually available to Docker daemons by default.

This also sets the root password to root. You should change this to something more secure.

Once you have the file configured, launch Docker Compose:

docker compose up

The images start downloading:

[+] Running 0/12
 ⠏ mysql Pulling                                                         18.0s
   ⠦ 7d4ed4ca78bc Downloading [====>         ]  4MB/43.46MB              15.7s
   ⠦ 657a7ca448ac Download complete                                      15.7s
   ⠦ 53bd78ce95ca Download complete                                      15.7s
   ⠦ c0e937b70acc Download complete                                      15.7s
   ⠦ c2bf3d14eb5e Download complete                                      15.7s
   ⠦ 4f675b4a4ac0 Download complete                                      15.7s
   ⠦ 53482ccac7fa Downloading [>             ]  539.9kB/55.6MB           15.7s
   ⠦ 828f28210871 Download complete                                      15.7s
   ⠦ 1db57577e20b Downloading [==>           ]  1.904MB/47.17MB          15.7s
   ⠦ 314e3cb90a9a Waiting                                                15.7s
   ⠦ 408e09447dc6 Waiting

Once the downloads complate, the service starts:

[+] Running 2/0
 ⠿ Network yourapp_default    Created                   0.0s
 ⠿ Container yourapp-mysql-1  Created                   0.0s
Attaching to yourapp-mysql-1
yourapp-mysql-1  | 2023-02-13 21:03:43+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.32-1.el8 started.
yourapp-mysql-1  | 2023-02-13 21:03:43+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
yourapp-mysql-1  | 2023-02-13 21:03:43+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.32-1.el8 started.
yourapp-mysql-1  | '/var/lib3mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
yourapp-mysql-1  | 2023-02-13T21:03:43.711913Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
yourapp-mysql-1  | 2023-02-13T21:03:43.712562Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.32) starting as process 1
yourapp-mysql-1  | 2023-02-13T21:03:43.719670Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
yourapp-mysql-1  | 2023-02-13T21:03:43.885533Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
yourapp-mysql-1  | 2023-02-13T21:03:44.012373Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
yourapp-mysql-1  | 2023-02-13T21:03:44.012408Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
yourapp-mysql-1  | 2023-02-13T21:03:44.013492Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
yourapp-mysql-1  | 2023-02-13T21:03:44.025655Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
yourapp-mysql-1  | 2023-02-13T21:03:44.025668Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.32'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.

Now that MySQL is running, You can connect to it from your application code or applications using localhost and port 3306 just as if MySQL was running natively.

Press CTRL+C to stop the service.

Now that you’ve confirmed MySQL starts without errors, start the service in the background with the -d switch:

docker compose up -d
[+] Running 1/1
 ⠿ Container yourapp-mysql-1  Start...

When docker compose is running containers in the background, you won’t see anything in your Terminal, but you can peek at the proceses with docker compose top:

docker compose top

You’ll see the MySQL server running:

yourapp-mysql-1
PID    USER   TIME   COMMAND
9078   999    0:00   mysqld

To stop your service, use docker compose stop.

The server is running, but you’ll also want to use the mysql client to connect to your instance.

Using the MySQL Client Tool

You’ll occasionally want to connect to your MySQL instance to create databases and do other operations. Use docker compose exec for this to avoid installing tools locally.

First, ensure your MySQL service is running under Docker Compose:

docker compose up -d

Now use the following command to connect:

docker compose exec mysql mysql -p

Enter the password you set in the docker-compose.yml file to connect.

Once you enter the password, you see the mysql welcome message and prompt:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Type exit to exit.

After a while, you may want to update your MySQL server to a newer version.

Update MySQL

To update MySQL running in a container, you’ll pull down the new version and restart your service.

The docker-compose.yml file lists the image you’re using. In this tutorial, you’re using the latest tag. If you want to use a specific version, use the tag for that version.

Run the following command to pull down the updated image:

docker compose pull

The new images download:

[+] Running 0/12
 ⠸ mysql Pulling                                                          18.4s
   ⠼ 7d4ed4ca78bc Downloading [=======>         ]  25.22MB/43.46MB        15.5s
   ⠼ 657a7ca448ac Download complete                                       15.5s
   ⠼ 53bd78ce95ca Download complete                                       15.5s
   ⠼ c0e937b70acc Download complete                                       15.5s
   ⠼ c2bf3d14eb5e Download complete                                       15.5s
   ⠼ 4f675b4a4ac0 Download complete                                       15.5s
   ⠼ 53482ccac7fa Downloading [====>            ]  4.844MB/55.6MB         15.5s
   ⠼ 828f28210871 Download complete                                       15.5s
   ⠼ 1db57577e20b Downloading [=======>         ]  14.76MB/47.17MB        15.5s
   ⠼ 314e3cb90a9a Waiting                                                 15.5s
   ⠼ 408e09447dc6 Waiting

Once they’re downloaded, stop your MySQL service if it’s running and remove the container with the following command:

docker compose down

Then restart the MySQL service and remove any unused images:

docker compose up -d --remove-orphans
[+] Running 2/2
 ⠿ Network yourapp_default    Created                                      0.0s
 ⠿ Container yourapp-mysql-1  Started

Repeat this process whenever you need to update your server.

Removing the MySQL Container

If you no longer need MySQL, you can clean everything up.

Use docker compose down with the --rmi flag to stop the service and remove the associated images:

docker compose down --rmi all

The images are removed:

[+] Running 1/1
 ⠿ Image mysql:latest  Removed

You can reinstall everything again with docker compose up.

Conclusion

You now have a method for running MySQL within a container on your local machine so you don’t need to install MySQL Server on your machine.

You can use this approach for Redis, PostreSQL, and other services you want to run but don’t want to configure. Set the volume and ports appropriately for that service.

Related Articles: