Environment

• macOS 10.13.3
• Docker 17.12.0-ce
• Image: mysql:5.7.21
• Example container:

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

Start the container and connect as root

• Start the MySQL container and connect from the host using the MySQL client:

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

Create a least-privileged application user

Create a user restricted to a single database (example database: appdb):

CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPassw0rd!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

Verify the user entry exists and note the Host column:

SELECT User, Host FROM mysql.user;

Example output:

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

Attempt to log in from the host

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

If you see an error like the following, the connection is being matched against the host 172.17.0.1 (the Docker bridge address), not localhost:

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

Option 1: Connect from inside the container

The account 'appuser'@'localhost' matches only clients originating from within the MySQL container. Exec in to the container and connect locally:

docker exec -it 478bbac9137b bash
mysql -u appuser -p

Check visibility and access:

SHOW DATABASES;

Example:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| appdb              |
+--------------------+

Why root works from the host

The root account often has a Host value of '%' (any host), so connections from the Docker bridge address succeed:

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

Option 2: Allow the user to connect from the host

You can change the user’s Host to allow connnections from out side the container.

• Allow from any host (broadest):

RENAME USER 'appuser'@'localhost' TO 'appuser'@'%';
FLUSH PRIVILEGES;

• Or, allow only the Docker bridge address seen by MySQL:

CREATE USER 'appuser'@'172.17.0.1' IDENTIFIED BY 'StrongPassw0rd!';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'172.17.0.1';
FLUSH PRIVILEGES;

Re-test from the host:

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

If using MySQL 8.0+ and native password plugin considerations apply, ensure the client and server authentication plugins are compatible. For MySQL 5.7 in this example, the above statements are sufficient.