How Use Mysql with docker and docker-compose

31/03/2024

Docker-compose is a powerful tool for managing multi-container Docker applications. In this article, we’ll explore how to set up a MySQL database using Docker-compose and tackle common issues that may arise during setup. We’ll cover troubleshooting steps for errors such as « not allowed to connect to this MySQL server » and dealing with authentication plugin loading problems. By following these steps, you can ensure smooth deployment and management of your MySQL containers.

Docker-compose

version: '3.8'

services:
  mysqldb:
    image: mysql:latest
    container_name: mysql-db
    restart: unless-stopped
    env_file: ./.env
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
      - MYSQL_ROOT_PASSWORD=$MYSQLDB_ROOT_PASSWORD
      - MYSQL_DATABASE=$MYSQLDB_DATABASE
    ports:
      - $MYSQLDB_LOCAL_PORT:$MYSQLDB_DOCKER_PORT
    volumes:
      - /home/antoine/databases/mysql/data:/var/lib/mysql

Set .env

.env is at the root of my project

MYSQLDB_USER=root
MYSQLDB_ROOT_PASSWORD=qqddqsqddqqdqdsqds
MYSQLDB_LOCAL_PORT=3306
MYSQLDB_DOCKER_PORT=3306

Troubleshooting

not allowed to connect to this MySQL server

Host '172.18.0.1' is not allowed to connect to this MySQL server

If you get this error when trying to connect to your fresh MySQL container

Enter on the container :

docker exec -it mysql-db bash

mysql -u root -p

Query the myql.user database

SELECT host, user FROM mysql.user;

You will get this

+------------+------------------+
| host       | user             |
+------------+------------------+
| %          | root             |
| 127.0.0.1  | root             |
| ::1        | root             |
| localhost  | mysql.sys        |
| localhost  | root             |
| localhost  | sonar            |
+------------+------------------+

Create a new user

CREATE USER 'antoine'@'%' IDENTIFIED WITH mysql_native_password BY '<thePassword>';
grant all on *.* to 'antoine'@'%';

Alternatively :

ALTER USER 'antoine'@'%' IDENTIFIED WITH mysql_native_password BY '<thePassword>';

mysql docker uthentication plugin ‘caching_sha2_password’ cannot be loaded:

If you get an erorr which says :

mysql docker uthentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.s

Add this command to the docker-compose

    command: mysqld --default-authentication-plugin=mysql_native_password

Issue with Sequel Pro

If you encounter issues when trying to connect to your database with Sequel Pro, it’s likely a bug from sequel pro with the latest MySQL version.

Instead uses Sequel Ace

How Use Mysql with docker and docker-compose / Tips and tricks