7

I am using docker-compose to create mysql container. I get host IP 172.21.0.2. But when I connect mysql. I get error:

  1. My docker-compose.yml:

    version: '2'
    services:
    
    ### Mysql container
        mysql:
          image: mysql:latest
          ports:
           - "3306:3306"
          volumes:
           - /var/lib/mysql:/var/lib/mysql
         environment:
           MYSQL_ROOT_PASSWORD: root
           MYSQL_DATABASE: test_db
           MYSQL_USER: test
           MYSQL_PASSWORD: test_pass
    
  2. Get my host IP docker inspect db_mysql_1 | grep IPAddress

    "IPAddress": "172.21.0.2",

  3. Access mysql: mysql -h 172.21.0.2 -P 3306 -u root -proot.

    ERROR 1130 (HY000): Host '172.21.0.1' is not allowed to connect to this MySQL server

How can I connect to mysql container?

11

You can pass an extra environment variable when starting the MySQL container MYSQL_ROOT_HOST=<ip> this will create a root user with permission to login from given IP address. In case where you want to allow login from any IP you can specify MYSQL_ROOT_HOST=%.

This will work only for a newly created containers.

When spinning new container:

docker run --name some-mysql -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

In compose file it would be:

version: '2'
services:

  ### Mysql container
  mysql:
    image: mysql:latest
    ports:
      - "3306:3306"
    volumes:
      - /var/lib/mysql:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: test_db
      MYSQL_USER: test
      MYSQL_PASSWORD: test_pass
      MYSQL_ROOT_HOST: %
| improve this answer | |
  • 2
    Should this still work for Docker version 18.09.1 and "mysql:5.7"? I have just tried it, confirmed that the container has the ENV_VAR correctly set, but I get the same error. – Nigini Jan 15 '19 at 4:49
  • dark ninja magic – satnhak Jan 24 '19 at 20:41
  • 3
    Hi, when I use the enviroment "MYSQL_ROOT_HOST: %", I receive the message: "ERROR: yaml.scanner.ScannerError: while scanning for the next token found character '%' that cannot start any token in "./docker-compose.yml", line 16, column 24", could you help me? – Allan Andrade Dec 3 '19 at 16:21
2

When you connect to a MySQL server, it checks it’s GRANT table (the "user" table in the "mysql| database on the MySQL server) against the IP address of the connecting MySQL client machine. If there are NO MATCHING ENTRIES in the "host" column in the "user" table in the "mysql" database, MySQL will IMMEDIATELY CLOSE THE CONNECTION with ERROR 1130.

Check if the client is able to get to port 3306 (the MySQL port) on the database server:

telnet 172.21.0.2 3306
Trying ::1...
Connected to 172.21.0.2.
Escape character is '^]'.

You should log in to the MySQL server, and run "mysql" to check the grants table:

# mysql mysql

mysql> SELECT host,user FROM user;
+-----------------+-----------+
| host            | user      |
+-----------------+-----------+
| 172.21.0.5      | root      |
| 172.21.0.4      | root      |
| 127.0.0.1       | root      |
| ::1             | root      |
| localhost       | root      |

"root" is authorized to connect from several IP addresses, but not from the client IP 172.21.0.1. So, just add GRANT access from that IP:

mysql> GRANT ALL PRIVILEGES ON root.* TO 'your_db'@'172.21.0.1' IDENTIFIED BY 'Password';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Then, check your connection.

| improve this answer | |
  • 1
    You have an error in your grant: grant all privileges on *.* to 'root'@'172.21.0.1' identified by 'password'; – Alexander Kim Jan 26 '19 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.