Docker-compose check if mysql connection is ready

Question

I am trying to make sure that my app container does not run migrations / start until the db container is started and READY TO accept connections.

So I decided to use the healthcheck and depends on option in docker compose file v2.

In the app, I have the following

app:
    ...
    depends_on:
      db:
      condition: service_healthy

The db on the other hand has the following healthcheck

db:
  ...
  healthcheck:
    test: TEST_GOES_HERE
    timeout: 20s
    retries: 10

I have tried a couple of approaches like :

1. making sure the db DIR is created test: ["CMD", "test -f var/lib/mysql/db"]
2. Getting the mysql version: test: ["CMD", "echo 'SELECT version();'| mysql"]
3. Ping the admin (marks the db container as healthy but does not seem to be a valid test) test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]

Does anyone have a solution to this?

Answer 1

version: "2.1"
services:
    api:
        build: .
        container_name: api
        ports:
            - "8080:8080"
        depends_on:
            db:
                condition: service_healthy
    db:
        container_name: db
        image: mysql
        ports:
            - "3306"
        environment:
            MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
            MYSQL_USER: "user"
            MYSQL_PASSWORD: "password"
            MYSQL_DATABASE: "database"
        healthcheck:
            test: ["CMD", "mysqladmin" ,"ping", "-h", "localhost"]
            timeout: 20s
            retries: 10

The api container will not start until the db container is healthy (basically until mysqladmin is up and accepting connections.)

Answer 2

condition was removed compose spec in versions 3.0 to 3.8 but is now back!

Using version of the compose spec v3.9+ (docker-compose v1.29), you can use condition as an option in long syntax form of depends_on.

Use condition: service_completed_successfully to tell compose that service must be running before dependent service gets started.

services:
  web:
    build: .
    depends_on:
      db:
        condition: service_completed_successfully
      redis:
        condition: service_completed_successfully
  redis:
    image: redis
  db:
    image: postgres

condition option can be:

• service_started is equivalent to short syntax form
• service_healthy is waiting for the service to be healthy. Define healthy with healthcheck option
• service_completed_successfully specifies that a dependency is expected to run to successful completion before starting a dependent service (Added to docker-compose with PR#8122).

It is sadly pretty badly documented. I found references to it on Docker forums, Docker doc issues, Docker compose issue, in Docker Compose e2e fixtures. Not sure if it's supported by Docker Compose v2.

Answer 3

This should be enough

version: '3.4'
services:
  mysql:
    image: mysql
    ports: ['3306:3306']
    environment:
      MYSQL_USER: myuser
      MYSQL_PASSWORD: mypassword
    healthcheck:
      test: mysqladmin ping -h 127.0.0.1 -u $$MYSQL_USER --password=$$MYSQL_PASSWORD
      start_period: 5s
      interval: 5s
      timeout: 5s
      retries: 55

Answer 4

Hi for a simple healthcheck using docker-compose v2.1, I used:

/usr/bin/mysql --user=root --password=rootpasswd --execute \"SHOW DATABASES;\"

Basically it runs a simple mysql command SHOW DATABASES; using as an example the user root with the password rootpasswd in the database.

If the command succeed the db is up and ready so the healthcheck path. You can use interval so it tests at interval.

Removing the other field for visibility, here is what it would look like in your docker-compose.yaml.

version: '2.1'

  services:
    db:
      ... # Other db configuration (image, port, volumes, ...)
      healthcheck:
        test: "/usr/bin/mysql --user=root --password=rootpasswd --execute \"SHOW DATABASES;\""
        interval: 2s
        timeout: 20s
        retries: 10

     app:
       ... # Other app configuration
       depends_on:
         db:
         condition: service_healthy

Answer 5

Adding an updated solution for the healthcheck approach. Simple snippet:

healthcheck:
  test: out=$$(mysqladmin ping -h localhost -P 3306 -u foo --password=bar 2>&1); echo $$out | grep 'mysqld is alive' || { echo $$out; exit 1; }

Explanation: Since mysqladmin ping returns false positives (especially for wrong password), I'm saving the output to a temporary variable, then using grep to find the expected output (mysqld is alive). If found it will return the 0 error code. In case it's not found, I'm printing the whole message, and returning the 1 error code.

Extended snippet:

version: "3.8"
services:
  db:
    image: linuxserver/mariadb
    environment:
      - FILE__MYSQL_ROOT_PASSWORD=/run/secrets/mysql_root_password
      - FILE__MYSQL_PASSWORD=/run/secrets/mysql_password
    secrets:
      - mysql_root_password
      - mysql_password
    healthcheck:
      test: out=$$(mysqladmin ping -h localhost -P 3306 -u root --password=$$(cat $${FILE__MYSQL_ROOT_PASSWORD}) 2>&1); echo $$out | grep 'mysqld is alive' || { echo $$out; exit 1; }

secrets:
  mysql_root_password:
    file: ${SECRETSDIR}/mysql_root_password
  mysql_password:
    file: ${SECRETSDIR}/mysql_password

Explanation: I'm using docker secrets instead of env variables (but this can be achieved with regular env vars as well). The use of $$ is for literal $ sign which is stripped when passed to the container.

Output from docker inspect --format "{{json .State.Health }}" db | jq on various occasions:

Everything alright:

{
  "Status": "healthy",
  "FailingStreak": 0,
  "Log": [
    {
    {
      "Start": "2020-07-20T01:03:02.326287492+03:00",
      "End": "2020-07-20T01:03:02.915911035+03:00",
      "ExitCode": 0,
      "Output": "mysqld is alive\n"
    }
  ]
}

DB is not up (yet):

{
  "Status": "starting",
  "FailingStreak": 1,
  "Log": [
    {
      "Start": "2020-07-20T01:02:58.816483336+03:00",
      "End": "2020-07-20T01:02:59.401765146+03:00",
      "ExitCode": 1,
      "Output": "\u0007mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 \"No such file or directory\")' Check that mysqld is running and that the socket: '/var/run/mysqld/mysqld.sock' exists!\n"
    }
  ]
}

Wrong password:

{
  "Status": "unhealthy",
  "FailingStreak": 13,
  "Log": [
    {
      "Start": "2020-07-20T00:56:34.303714097+03:00",
      "End": "2020-07-20T00:56:34.845972979+03:00",
      "ExitCode": 1,
      "Output": "\u0007mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)'\n"
    }
  ]
}

Answer 6

If you can change the container to wait for mysql to be ready do it.

If you don't have the control of the container that you want to connect the database to, you can try to wait for the specific port.

For that purpose, I'm using a small script to wait for a specific port exposed by another container.

In this example, myserver will wait for port 3306 of mydb container to be reachable.

# Your database
mydb:
  image: mysql
  ports:
    - "3306:3306"
  volumes:
    - yourDataDir:/var/lib/mysql

# Your server
myserver:
  image: myserver
  ports:
    - "....:...."
  entrypoint: ./wait-for-it.sh mydb:3306 -- ./yourEntryPoint.sh

You can find the script wait-for-it documentation here

Answer 7

RESTART ON-FAILURE

Since v3 condition: service_healthy is no longer available. The idea is that the developer should implement mechanism for crash recovery within the app itself. However for simple use cases a simple way to resolve this issue is to use restart option.

If mysql service status causes your application to exited with code 1 you can use one of restart policy options available. eg, on-failure

version: "3"

services:

    app:
      ...
      depends_on:
        - db:
      restart: on-failure

Answer 8

I had the same problem, I created an external bash script for this purpose (It is inspired by Maxim answer). Replace mysql-container-name by the name of your MySQL container and also password/user is needed:

bin/wait-for-mysql.sh:

#!/bin/sh
until docker container exec -it mysql-container-name mysqladmin ping -P 3306 -proot | grep "mysqld is alive" ; do
  >&2 echo "MySQL is unavailable - waiting for it... "
  sleep 1
done

In my MakeFile, I call this script just after my docker-compose up call:

wait-for-mysql: ## Wait for MySQL to be ready
    bin/wait-for-mysql.sh

run: up wait-for-mysql reload serve ## Start everything...

Then I can call other commands without having the error:

An exception occurred in driver: SQLSTATE[HY000] [2006] MySQL server has gone away

Output example:

docker-compose -f docker-compose.yaml up -d
Creating network "strangebuzzcom_default" with the default driver
Creating sb-elasticsearch ... done
Creating sb-redis              ... done
Creating sb-db                 ... done
Creating sb-app                ... done
Creating sb-kibana             ... done
Creating sb-elasticsearch-head ... done
Creating sb-adminer            ... done
bin/wait-for-mysql.sh
MySQL is unavailable - waiting for it... 
MySQL is unavailable - waiting for it... 
MySQL is unavailable - waiting for it... 
MySQL is unavailable - waiting for it... 
mysqld is alive
php bin/console doctrine:schema:drop --force
Dropping database schema...
[OK] Database schema dropped successfully!

Answer 9

I modified the docker-compose.yml as per the following example and it worked.

  mysql:
    image: mysql:5.6
    ports:
      - "3306:3306"
    volumes:       
      # Preload files for data
      - ../schemaAndSeedData:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: rootPass
      MYSQL_DATABASE: DefaultDB
      MYSQL_USER: usr
      MYSQL_PASSWORD: usr
    healthcheck:
      test:  mysql --user=root --password=rootPass -e 'Design your own check script ' LastSchema

In my case ../schemaAndSeedData contains multiple schema and data seeding sql files. Design your own check script can be similar to following select * from LastSchema.LastDBInsert.

While web dependent container code was

depends_on:
  mysql:
    condition: service_healthy

Answer 10

condition is added back, so now you could use it again. There is no need for wait-for scripts. If you are using scratch to build images, you cannot run those scripts anyways.

For API service

api:
    build:
      context: .
      dockerfile: Dockerfile
    restart: always
    depends_on:
      content-db:
        condition: service_healthy
    ...

For db block

content-db:
    image: mysql:5.6
    restart: on-failure
    command: --default-authentication-plugin=mysql_native_password
    volumes:
      - "./internal/db/content/sql:/docker-entrypoint-initdb.d"
    environment:
      MYSQL_DATABASE: content
      MYSQL_TCP_PORT: 5306
      MYSQL_ROOT_PASSWORD: $MYSQL_ROOT_PASSWORD
    healthcheck:
      test: "mysql -uroot -p$MYSQL_ROOT_PASSWORD content -e 'select 1'"
      interval: 1s
      retries: 120

Answer 11

You can try this docker-compose.yml:

version: "3"

services:

  mysql:
    container_name: mysql
    image: mysql:8.0.26
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_db
      MYSQL_USER: test_user
      MYSQL_PASSWORD: 1234
    ports:
      - "3306:3306"
    volumes:
      - mysql-data:/var/lib/mysql
    healthcheck:
      test: "mysql $$MYSQL_DATABASE -u$$MYSQL_USER -p$$MYSQL_PASSWORD -e 'SELECT 1;'"
      interval: 20s
      timeout: 10s
      retries: 5

volumes:
  mysql-data:

Answer 12

Although using healthcheck together with service_healthyis a good solution, I wanted a different solution that doesn't rely on the health check itself.

My solution utilizes the atkrad/wait4x image. Wait4X allows you to wait for a port or a service to enter the requested state, with a customizable timeout and interval time.

Example:

services:
  app:
    build: .
    depends_on:
      wait-for-db:
        condition: service_completed_successfully
        
  db:
    image: mysql
    environment:
      - MYSQL_ROOT_PASSWORD=test
      - MYSQL_DATABASE=test

  wait-for-db:
    image: atkrad/wait4x
    depends_on:
      - db
    command: tcp db:3306 -t 30s -i 250ms

Explanation

The example docker-compose file includes the services:

• app - this is the app that connects to the database once the database instance is ready
  • depends_on waits for wait-for-db service to complete successfully. (Exit with 0 exit code)
• db - this is the MySQL service
• wait-for-db - this services waits for the database to open its port
  • command: tcp db:3306 -t 30s -i 250ms - wait on the TCP protocol for 3306 port, with a timeout of 30 seconds, check the port every 250 milliseconds

Answer 13

After going through other solutions, mysqladmin ping does not work for me. This is because mysqladmin will return a success error code (i.e 0) even if MySQL server has started but not accepting a connection on port 3306. For the initial start, MySQL server will start the server on port 0 to setup the root user and initial databases. This is why there is a false positive test.

Here is my healthcheck test:

test: ["CMD-SHELL", "exit | mysql -h localhost -P 3306 -u root -p$$MYSQL_ROOT_PASSWORD" ]

The exit | closes MySQL input prompt during a successful connection.

My Complete Docker Compose file:

version: '3.*'

services:
  mysql:
    image: mysql:8
    hostname: mysql
    ports:
      - "3306:3306"
    environment:
      - MYSQL_DATABASE=mydb
      - MYSQL_ALLOW_EMPTY_PASSWORD=1
      - MYSQL_ROOT_PASSWORD=mypass
    healthcheck:
      test: ["CMD-SHELL", "exit | mysql -h localhost -P 3306 -u root -p$$MYSQL_ROOT_PASSWORD" ]
      interval: 5s
      timeout: 20s
      retries: 30
  web:
    build: .
    ports:
      - '8000:8000'
    depends_on:
      mysql:
        condition: service_healthy

Answer 14

I'd like to provide one more solution for this, which was mentioned in one of the comments but not really explained:
There's a tool called wait-for-it, which is mentioned on
https://docs.docker.com/compose/startup-order/
How it works? You just specify the host and the port that script needs to check periodically if it's ready. If it is, it will execute the program that you provide to it. You can also specify for how long it should check whether the host:port is ready. As for me this is the cleanest solution that actually works.
Here's the snippet from my docker-compose.yml file.

version : '3'

services:

database: 
    build: DatabaseScripts
    ports:
        - "3306:3306"
    container_name: "database-container"
    restart: always

backend:
    build: backend
    ports: 
        - "3000:3000"
    container_name: back-container
    restart: always
    links:
        - database
    command : ["./wait-for-it.sh", "-t", "40", "database:3306", "--", "node", "app.js"]
    # above line does the following:
        # check periodically for 40 seconds if (host:port) = database:3306 is ready
        # if it is, run 'node app.js'
        # app.js is the file that is connecting with the db

frontend: 
    build: quiz-app
    ports:
        - "4200:4200"
    container_name: front-container
    restart: always

default waiting time is 20 seconds. More details about it can be found on
https://github.com/vishnubob/wait-for-it.

I tried it on 2.X and 3.X versions - it works fine everywhere.
Of course you need to provide the wait-for-it.sh to your container - otherwise it won't work.
To do so use the following code :

COPY wait-for-it.sh <DESTINATION PATH HERE>

I added it in /backend/Dockerfile, so it looks something like this :

FROM node
RUN mkdir -p /usr/src/app
WORKDIR /usr/src/app
COPY package.json /usr/src/app
COPY wait-for-it.sh /usr/src/app
RUN npm install
COPY . /usr/src/app
EXPOSE 3000
CMD ["npm", "start"]

To check everything is working correctly, run docker-compose logs. After some time somewhere in the logs you should see the output similar to that :

<container_name> | wait-for-it.sh: waiting 40 seconds for database:3306
<container_name> | wait-for-it.sh: database:3306 is available after 12 seconds

NOTE : This solution was provided by BartoszK in previous comments.

Answer 15

No one answer worked for me:

• Docker version 20.10.6
• docker-compose version 1.29.2
• docker-compose yml version: version: '3.7'
• mysql 5.7
  • run script at container start : docker-entrypoint-initdb.d

Solution

Check for some word in last lines of mysql log which indicates me something like "Im ready".

This is my compose file:

version: '3.7'

services:
  mysql:
    image: mysql:5.7
    command: mysqld --general-log=1 --general-log-file=/var/log/mysql/general-log.log
    container_name: mysql
    ports:
     - "3306:3306"
    volumes:
     - ./install_dump:/docker-entrypoint-initdb.d
    environment:
      MYSQL_ROOT_PASSWORD: changeme
      MYSQL_USER: jane
      MYSQL_PASSWORD: changeme
      MYSQL_DATABASE: blindspot
    healthcheck:
          test: "cat /var/log/mysql/general-log.log | grep \"root@localhost on  using Socket\""
          interval: 1s
          retries: 120

  some_web:
    image: some_web
    container_name: some_web
    ports:
     - "80:80"
    depends_on:
        mysql:
            condition: service_healthy

Explanation

After several checks I was able to get the entire mysql log of the container.

docker logs mysql could be enough but I was not able to access to the docker log inside of healthcheck, so I had to dump the query log of mysql into a file with:

command: mysqld --general-log=1 --general-log-file=/var/log/mysql/general-log.log

After that I ran several times my mysql container to determine if log is the same. I found that last lines were always the same:

2021-08-30T01:07:06.040848Z    10 Connect   root@localhost on  using Socket
2021-08-30T01:07:06.041239Z    10 Query SELECT @@datadir, @@pid_file
2021-08-30T01:07:06.041671Z    10 Query shutdown
2021-08-30T01:07:06.041705Z    10 Query 
mysqld, Version: 5.7.31-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument

Finally, after some attempts, this grep return just one match which corresponds to the end of mysql log after the execution of dumps in /docker-entrypoint-initdb.d:

cat /var/log/mysql/general-log.log | grep \"root@localhost on  using Socket\"

Words like started with or Tcp port: returned several matches (start, middle and at the end of log) so are not options to detect the end of starting mysql success log.

healthcheck

Happily, when grep found at least one match, it returns a success exist code (0). So use it in healthcheck was easy:

healthcheck:
  test: "cat /var/log/mysql/general-log.log | grep \"root@localhost on  using Socket\""
  interval: 1s
  retries: 120

Improvements

• If someone knows how to get the docker logs mysql inside of healthchek it will be better than enable the query log
• Handle when sql scripts returns an error.

Answer 16

This worked for me:

version: '3'

services:

  john:
    build:
      context: .
      dockerfile: containers/cowboys/john/Dockerfile
      args:
        - SERVICE_NAME_JOHN
        - CONTAINER_PORT_JOHN
    ports:
      - "8081:8081" # Forward the exposed port on the container to port on the host machine
    restart: unless-stopped
    networks:
      - fullstack
    depends_on:
      db:
        condition: service_healthy
    links:
      - db

 db:
    build:
      context: containers/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_USER: docker_user
      MYSQL_PASSWORD: docker_pass
      MYSQL_DATABASE: cowboys
    container_name: golang_db
    restart: on-failure
    networks:
      - fullstack
    ports:
      - "3306:3306"
    healthcheck:
      test: mysqladmin ping -h 127.0.0.1 -u $$MYSQL_USER --password=$$MYSQL_PASSWORD

networks:
  fullstack:
    driver: bridge

// containers/mysql/Dockerfile

FROM mysql
COPY cowboys.sql /docker-entrypoint-initdb.d/cowboys.sql

Answer 17

Most of the answers here are correct at half.

I used mysqladmin ping --silent command and it was mostly good, but even if container becomes healthy it wasn't able to handle external requests. So I decided to switch to more complicated command and use container's external ip address to be sure that healthcheck is the same as real request will be:

services:
  my-mariadb:
    container_name: my-mariadb
    image: ${DB_IMAGE}
    environment:
      MARIADB_ROOT_PASSWORD: root_password
      MARIADB_USER: user
      MARIADB_PASSWORD: user_password
      MARIADB_DATABASE: db_name
    volumes:
      - ./db/dump.sql:/docker-entrypoint-initdb.d/dump.sql
    ports:
      - 3306:3306
    healthcheck:
      test: mysql -u"$${MARIADB_USER}" -p"$${MARIADB_PASSWORD}" -hmariadb "$${MARIADB_DATABASE}" -e 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES LIMIT 1;'
      interval: 20s
      timeout: 5s
      retries: 5
      start_period: 30s
    networks:
      my_network:
        aliases:
          - mariadb

Here is mySQL query is running via external hostname (mariadb)

Also test may be looks like

mysql -u"$${MARIADB_USER}" -p"$${MARIADB_PASSWORD}" -h"$$(ip route get 1.2.3.4 | awk '{print $7}' | awk /./)" "$${MARIADB_DATABASE}" -e 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES LIMIT 1;'

If you want to use IP address instead of hostname

When I used mysqladmin ping command, time while status changed to healthy was about 21 seconds, and after I switched to new command it raised to 41 seconds. That means that database needs extra 20 seconds to be finally configured and able to handle external requests.

Answer 18

For me it was both:

The MySQL image version and the enviroment variable SPRING_DATASOURCE_URL. If I remove the SPRING_DATASOURCE_URL it doesn´t work. Neither if I use MySQL:8.0 or above.

version: "3.9"

services:
  api:
    image: api
    build:
      context: ./api
    depends_on:
      - db
    environment:
      SPRING_DATASOURCE_URL: jdbc:mysql://db:3306/api?autoReconnect=true&useSSL=false
    networks:
      - private
    ports:
      - 8080:8080
  db:
    image: mysql:5.7
    environment:
      MYSQL_DATABASE: "api"
      MYSQL_ROOT_PASSWORD: "root"
    networks:
      - private
    ports:
      - 3306:3306

networks:
  private: