21

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?

Improve this question

6 Answers 6

Reset to default
40

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: '%' # needs to be enclosed with quotes
Improve this answer
6
  • 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. Commented Jan 15, 2019 at 4:49
  • dark ninja magic Commented Jan 24, 2019 at 20:41
  • 5
    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? Commented Dec 3, 2019 at 16:21
  • 2
    MYSQL_ROOT_HOST: '%' - The % needs be string enclosed Commented Dec 14, 2020 at 10:28
  • If you have a "broken" or misconfigured grant tables in the host /var/lib/mysql location, setting MYSQL_ROOT_HOST=% is not going to work because that "broken" grant tables is already exists on host. Create another mysql container using docker-compose that mounted to the host data location, enter the container terminal, start mysql with mysqld_safe --skip-grant-tables, and fix the grant tables using root (without password). In my case, I need to drop the problematic users, re-create and re-grant the privileges. This happened to me when I want to change the authentication method of a user. Commented Mar 26, 2022 at 23:28
3

This way it worked for me:

MYSQL_ROOT_HOST: '%'
Improve this answer
3

In my case. I deleted the volumes configuration and it worked.

Improve this answer
0
3

This is what worked for me:

docker exec -it mysql1 mysql -u root -p update mysql.user set host='%' where user='root' and host = 'localhost'; flush privileges;

change mysql1 to your container name.

P.S: I'm not using docker-compose.

Improve this answer
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
  • 1
    You have an error in your grant: grant all privileges on *.* to 'root'@'172.21.0.1' identified by 'password'; Commented Jan 26, 2019 at 5:39
0

When i tried MYSQL_ROOT_HOST= '%' i got this error message after docker-compose up

ERROR 1064 (42000) at line 8: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'' IDENTIFIED BY 'root'' at line 1

However, using double-quotes MYSQL_ROOT_HOST= "%" resolved the issues surprisingly.

This is final docker-compose.yml I used, to spin up a container for mysql db.

version: '3.3' services: db: image: mysql:8-oracle container_name: mysqldb2 environment: - MYSQL_ROOT_PASSWORD=root - MYSQL_DATABASE=social-media-database - MYSQL_USER=test - MYSQL_PASSWORD=pass - MYSQL_ROOT_HOST= "%" ports: - "3306:3306" restart: always
Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.