Environmental Science

• macOS 10.13.3

• docker 17.12.0-ce

• mysql container information

CONTAINER ID: 478bbac9137b
IMAGE: mysql:5.7.21
PORTS: 0.0.0.0:3306->3306/tcp
NAMES: mysql.5.7.21.plus

Preface

Currently, you use the MySQL container of docker to use mysql. As long as you run the container, you can use the local terminal connection and root login.

docker start 478bbac9137b
mysql -h 127.0.0.1 -P 3306 -u root -p

Create new user

To prevent SQL injection attacks, set up a special user whose permission is limited to operating a database godb

CREATE USER 'schwarzeni'@'localhost' IDENTIFIED BY 'schwarzeni';
GRANT ALL PRIVILEGES ON  godb.* TO 'schwarzeni'@'localhost';
FLUSH PRIVILEGES;

To see if it was created successfully:

select User, Host from mysql.user;

+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| root          | %         |
| schwarzeni    | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

problem

Try to log in locally

mysql -h 127.0.0.1 -P 3306 -u schwarzeni -p

But it's wrong

ERROR 1045 (28000): Access denied for user 'schwarzeni'@'172.17.0.1' (using password: YES)

Solve

localhost can only log in from within the container, that is:

docker exec -it 478bbac9137b bash

Then execute the login command and test

mysql -u schwarzeni -p

SHOW DATABASES;

Success

+--------------------+
| Database           |
+--------------------+
| information_schema |
| godb               |
+--------------------+

So why can root access it on the local terminal? There is such a line when viewing mysql users

+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| root          | %         |
+---------------+-----------+

The% mark of the Host matches the name of any Host, so can schwarzeni users

RENAME USER 'schwarzeni'@'localhost' TO 'schwarzeni'@'%';

In this way, you can access it locally, or create a user for the Host for 172.17.0.1

Reference link

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