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

[sougiovn]

I'm trying to connect to the MySQL by the DBeaver db manager.

But I get this error: java.sql.SQLException: null, message from server: "Host '172.18.0.1' is not allowed to connect to this MySQL server"

I using docker-compose, here is my docker-compose.yml:

version: '2'
services:
  db:
    image: 'mysql:5.7'
    volumes:
       - '~/dev/dbs-data/mysql:/var/lib/mysql'
    restart: 'always'
    expose:
      - '3306'
    ports:
      - '3306:3306'
    environment:
       MYSQL_ROOT_PASSWORD: 'pass'
       MYSQL_DATABASE: 'db'
       MYSQL_USER: 'user'
       MYSQL_PASSWORD: 'pass'

The problem is that, in my Mac it works, but in my Linux I get the error above.

Should I do some other configuration for my linux?

Same here. The problem suddenly appears today on a new container 5.7.17. all previous containers with the same image are fine (sounds impossible, I know).

Still investigating how it changed from working to not working from one day to the next.

[sougiovn]

I resolved removing the volumes configuration.

[ltangvald]

Can you check the user,host columns in your mysql.user table to make sure the user isn't limited in some way.
And also run from the container (or with exec)
mysqld --verbose --help | grep bind-address
To check that the server will allow external connections

Thanks for the tipp @ltangvald the user was completely missing and so was the database.
The problem was in my docker-compose calls that launched two containers simultanously which shared a data volume.

[s1dekick223]

Hey @ltangvald, i've the same problem but dont really understand how to do this can you help me and telling me where i find my mysql stuff?

[lucile-sticky]

@s1dekick223

Check if the database user exists and can connect

In MySQL, each database user is defined with IP address in it, so you can have for example a root user allowed to connect from localhost (127.0.0.1) but not from other IP addresses. With a container, you never access to the database from 127.0.0.1, it could explain the problem.

To check it, you can do the following:

1* From a terminal, connect you to your MySQL running container

docker exec -it your_container_name_or_id bash

2* In your container, connect you to the MySQL database

mysql -u your_user -p

It will ask you your password, you have to write it and press enter.

3* In your MySQL database, execute this SQL script to list all existing database users

SELECT host, user FROM mysql.user;

It will display a table, for example like this:

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

It has to contain a line with your database user and '%' to works (% means "every IP addresses are allowed"). Example:

+------------+------------------+
| host       | user             |
+------------+------------------+
| %          | root             |
+------------+------------------+

My root user can connect itself from any IP addresses.

Are external connections allowed?

After that, like @ltangvald said, it could be a problem of allowing external connections to the container.

To check it, you can do the following:

1* From a terminal, connect you to your MySQL running container

docker exec -it your_container_name_or_id bash

2* In your container, run this command

mysqld --verbose --help | grep bind-address

It will display address to bind to, for example like this:

  --bind-address=name IP address to bind to.
bind-address                                                 0.0.0.0

The bind address have to be 0.0.0.0 (which means "every IP addresses") to work.

Also, a note:

Using docker-compose, if you link a volume, the parameters

    environment:
       MYSQL_ROOT_PASSWORD: 'pass'
       MYSQL_DATABASE: 'db'
       MYSQL_USER: 'user'
       MYSQL_PASSWORD: 'pass'

in your docker-compose.yml will not be used, so default user will not be created: you have to create it manually or remove the volume declaration.

[yosifkit]

Using docker-compose, if you link a volume, the parameters

   environment:
      MYSQL_ROOT_PASSWORD: 'pass'
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'user'
      MYSQL_PASSWORD: 'pass'

in your docker-compose.yml will not be used, so default user will not be created: you have to create it manually or remove the volume declaration.

This is not quite accurate. They will be used if the database files do not exist in the folder. If it is a folder from a previous run of mysql, either from another container or an install from the host machine (ensure neither is running), then the initialization step will be skipped and these environment variables have no effect.

If there is no important data in the files, you can always docker-compose rm -fv; sudo rm -rf ~/dev/dbs-data/mysql/* and then docker-compose up -d.

[thedrint]

I have today this problem, but it was my mistake.

I copying docker-compose.yml and related volumes directories (where logs and database settings stored) from one old project to another new project. And run docker-compose up -d

So i have, in result, mysql's service image builted from cache, with database settings from original project. No environments were applied, @yosifkit make exact definition of situation.

Simply delete from volume's directory all old mysql data, rebuild image - and all works fine.

[LordRahl90]

please @lucile-sticky my bind-address is '*' how do i edit it to '0.0.0.0'??? Thanks

[lucile-sticky]

@LordRahl90 to change the bind-address option, you have to:

• find your mysql configuration file (maybe you already customize it for your container)
• under the section [mysqld], add bind-address=0.0.0.0

Example:
In my case, I create my own mysqld.cnf file:

[mysqld]
pid-file	            = /var/run/mysqld/mysqld.pid
socket		            = /var/run/mysqld/mysqld.sock
# Where the database files are stored inside the container
datadir		            = /var/lib/mysql

# My application special configuration
max_allowed_packet          = 32M
sql-mode                    = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

# Accept connections from any IP address
bind-address	            = 0.0.0.0

Then in my Dockerfile, I tell to Docker to copy it inside the container:

FROM mysql:5.6
MAINTAINER lucile-sticky "aaa@bbb.ccc"

# Setup the custom configuration
ADD mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf

So when MySQL starts, it uses my special configuration instead of the default one.

[littlee]

@lucile-sticky I'm having the same issue as which @LordRahl90 has, but I want to ask, why mysql does not set bind-address to 0.0.0.0 when it start? Do I have to set it manually?

[lucile-sticky]

@littlee I don't know what is the default value of bind-address for MySQL, that's why I specify it, to be sure to have the wanted one.

[geerlingguy]

For me, I hit the same issue once I switched the volume to a path local on my computer, then went through a docker-compose down and docker-compose up -d cycle. After clearing out the files in that directory, the next docker-compose up -d created things fresh, and it worked fine.

I didn't have this problem when using a data volume (volumes: ['db_data:/var/lib/mysql']).

[Danisan]

You can also copy the my.cnf file
docker cp mysql_mysql_1:/etc/my.cnf conf/

And then include a volume mapping exclusively to your file.

volumes:
    - './conf/my.cnf:/etc/my.cnf'

So you can add bind-address=0.0.0.0 to your my.cnf file.

Anyway, I've done this, but still cannot connect from outside.

[lucasdidur]

I fixed executing this command on docker container:

# mysql_secure_installation