How to check if MySQL in Docker is ready before interacting with it?

UPDATE See the end of the post, for an update.

While working on MySQL adapter for stash-it, I stumbled upon a problem of having a MySQL instance not being ready on time to run the tests against it. Well... it was a right time to to put the DevOps hat on and do some hacking.

In this blog post I will present all the findings and how I got to solve my problem, step by step.

Setting up dockerized MySQL

First of all, I wanted to have MySQL running inside a docker container. For that I prepared the docker-compose.yaml file:

services:
  mysql:
    image: mysql
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: dbname
    ports:
      - '3306:3306'

Nothing fancy. A quick run:

docker compose up

and after a few moments, the DB was up and running. So far, so good.

Running the tests

As the project is Node-based, I thought that this will work (part of package.json contents):

{
  "scripts": {
    "test": "docker compose up -d && vitest run && docker compose kill && docker compose rm -f"
  }
}

A quick explanation:

docker compose up -d starts MySQL container in detached mode
vitest run runs my tests (that connect to the DB, perform tasks, etc.)
docker compose kill immediately stops all running containers (in the context of where the script is executed, in this case, it's what was defined in docker-compose.yaml file)
docker compose rm -f forcefully removes stopped containers.

Basically the 2 last steps are a cleanup after the tests run.

Going back to running the tests. When I executed the script:

pnpm test

I got a nasty error when my code wanted to connect to the DB, but the connection was not possible.

What was going on? Simple - the DB was not yet ready to be interacted with.

Waiting for the DB to be up and running

In order to know that the DB is ready, I had to come up with a method that:

Starts the container
Checks if it is ready
Runs the tests
Cleans up after everything's done

So, I created this script (with a bit of help from ChatGPT). I added some echo statements to visualize the progress.

#!/bin/bash

echo "Starting MySQL using docker-compose..."
docker-compose up -d mysql

echo "Waiting for MySQL to be ready..."
MYSQL_HOST="localhost"       # I knew I would run this only locally
MYSQL_PORT="3306"            # Using the port from docker-compose.yaml
RETRIES=30                   # Maximum retries before giving up
SLEEP_TIME=2                 # Time to wait between retries

for ((i=1; i<=RETRIES; i++)); do
    if nc -z "$MYSQL_HOST" "$MYSQL_PORT"; then
        echo "MySQL is ready!"

        echo "Running tests..."
        vitest run

        echo "Cleaning up Docker containers..."
        docker-compose kill
        docker-compose rm -f
        exit 0
    else
        echo "MySQL is not ready yet. Retrying in $SLEEP_TIME seconds... ($i/$RETRIES)"
        sleep $SLEEP_TIME
    fi
done

# If we reach here, the database never became ready
echo "MySQL did not become ready in time. Cleaning up Docker and exiting."
docker-compose kill
docker-compose rm -f
exit 1

The new thing here is nc -z "$MYSQL_HOST" "$MYSQL_PORT".

The nc (or netcat) utility is used for just about anything under the sun involving TCP, UDP, or UNIX-domain sockets. In other words, I wanted to check if, for given host and port, my DB is ready.

Nice... right? (well, no, but later about that)

I saved the file, made it executable chmod +x run-tests-with-cleanup.sh, attached it to the test script

"test": "./run-tests-with-cleanup.sh"

and run:

pnpm test

And I got the same error 😂

What was it this time? The same thing. Only this time we reached the point when MySQL server was listening, but the DB was still not ready yet.

Wait, hodl it!

A naive approach was to wait some arbitrary time, so I did this:

if nc -z "$MYSQL_HOST" "$MYSQL_PORT"; then
  # Give it a few more seconds to be ready
  sleep 3

But it turned out not to be deterministic. One time it was enough, the other time it was not. I had to come up with a better check in the if clause.

DB, are you there?

The simplest solution was to connect to the DB directly and perform a query on it. If it responded with a non error response, then all's good.

I updated the script, adding:

MYSQL_PASSWORD="rootpassword"

and changing the if clasuse to this:

if docker exec -it mysql_container mysql -u "$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1"; then

and also updated docker-compose.yaml file, adding the container name:

container_name: mysql_container

How that new if works? It allocates a pseudo-terminal in an interactive mode on a running container (identified by its name, mysql_container in this case), and executes command

mysql -u "$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1.

And ... success! Everything worked as expected. MySQL got up, the DB was ready, tests run and passed, and eventually docker cleaned up everything.

But I noticed this (expected) error message, which was printed upon each "is the DB ready" iteration. It was a bit noisy and not informative (as I did not care that the DB is not ready yet).

Waiting for MySQL to be ready...
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

So I decided to send it into the void (/dev/null), resulting in:

if docker exec -it mysql_container mysql -u "$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1" > /dev/null; then

And now, when I executed pnpm test everything was nice and clean:

Loading environment variables from .env file...
Starting MySQL using docker-compose...
[+] Running 1/1
 ✔ Container mysql_container  Started                                                                        0.1s
Waiting for MySQL to be ready...
MySQL is not ready yet. Retrying in 2 seconds... (1/30)
MySQL is not ready yet. Retrying in 2 seconds... (2/30)
MySQL is not ready yet. Retrying in 2 seconds... (3/30)
MySQL is ready!
Running tests...

(removed logs from runnig tests as it does not matter here)

Cleaning up Docker containers...
[+] Killing 1/1
 ✔ Container mysql_container  Killed                                                                         0.2s
Going to remove mysql_container
[+] Removing 1/0
 ✔ Container mysql_container  Removed

Final touches

I also noticed, that I need to repeat the values I use for username, password, host etc.

Environment variables to the rescue. That way, if I decided, or were forced, to use different values, I would be able to do so, with a change in one place only.

Summarizing, this is the final state of the files I created:

.env

MYSQL_CONTAINER_NAME=mysql_container
MYSQL_DATABASE=database_name
MYSQL_USER=root
MYSQL_ROOT_PASSWORD=rootpassword
MYSQL_PORT=3306

docker-compose.yaml

services:
  mysql:
    image: mysql
    container_name: ${MYSQL_CONTAINER_NAME}
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
    ports:
      - '${MYSQL_PORT}:3306'

run-tests-with-cleanup.sh

#!/bin/bash

if [ -f .env ]; then
    echo "Loading environment variables from .env file..."
    source .env
else
    echo ".env file not found. Exiting."
    exit 1
fi

echo "Starting MySQL using docker-compose..."
docker-compose up -d mysql

echo "Waiting for MySQL to be ready..."
RETRIES=30                   # Maximum retries before giving up
SLEEP_TIME=2                 # Time to wait between retries

for ((i=1; i<=RETRIES; i++)); do
    if docker exec -it "$MYSQL_CONTAINER_NAME" mysql -u "$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1" > /dev/null; then
        echo "MySQL is ready!"

        echo "Running tests..."
        vitest run

        echo "Cleaning up Docker containers..."
        docker-compose kill
        docker-compose rm -f
        exit 0
    else
        echo "MySQL is not ready yet. Retrying in $SLEEP_TIME seconds... ($i/$RETRIES)"
        sleep $SLEEP_TIME
    fi
done

# If we reach here, the database never became ready
echo "MySQL did not become ready in time. Cleaning up Docker and exiting."
docker-compose kill
docker-compose rm -f
exit 1

Remember to make it executable, if you didn't already: chmod +x ./run-tests-with-cleanup.sh.

package.json (just the test script)

"test": "./run-tests-with-cleanup.sh"

And that's all. Enjoy!

An update

I realized I can make this script be even more versatile, by running "a command" when MySQL is ready, not just unit tests. Why is that good? Well, because the above implementation does not allow for e.g. running tests in watch mode.

That is why I changed the script to this:

#!/bin/bash

if [ -f .env ]; then
    echo "Loading environment variables from .env file..."
    source .env
else
    echo ".env file not found. Exiting."
    exit 1
fi

echo "Starting MySQL using docker-compose..."
docker-compose up -d mysql

echo "Waiting for MySQL to be ready..."
RETRIES=30                   # Maximum retries before giving up
SLEEP_TIME=2                 # Time to wait between retries

for ((i=1; i<=RETRIES; i++)); do
    if docker exec -it "$MYSQL_CONTAINER_NAME" mysql -u "$MYSQL_USER" -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1" > /dev/null; then
        echo "MySQL is ready!"

        if [ $# -gt 0 ]; then
            echo "Running: $@"

            "$@"
        fi

        echo "Cleaning up Docker containers..."
        docker-compose kill
        docker-compose rm -f
        exit 0
    else
        echo "MySQL is not ready yet. Retrying in $SLEEP_TIME seconds... ($i/$RETRIES)"
        sleep $SLEEP_TIME
    fi
done

# If we reach here, the database never became ready
echo "MySQL did not become ready in time. Cleaning up Docker and exiting."
docker-compose kill
docker-compose rm -f
exit 1

Where if [ $# -gt 0 ]; then means: if there are any arguments added to the script, do something. And that's done here: "$@".

Also, I renamed the script to: when-mysql-is-ready-run.sh. And now I can do this in package.json:

"test": "./when-mysql-is-ready-run.sh vitest run",
"test:watch": "./when-mysql-is-ready-run.sh vitest --watch"