DockerでMySQLに接続する時に対応したエラー

#問題１

###エラー1

Mysql2::Error: Host '172.22.0.1' is not allowed to connect to this MySQL server

#対処方法

ユーザーに、外部からのアクセス権を付与する　

###手順

１、ユーザーのhostを確認

select user, host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| user　　            | localhost |
+------------------+-----------+

２、hostをlocalhost→%(ワイルドカード)に変更

CREATE USER 'user' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'user'@'%' WITH GRANT OPTION;

３、再度hostの状態を確認して%になっていたらOK

select user, host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| user             | %         |
+------------------+-----------+

#問題２

###エラー2

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found

#対処方法

caching_sha2_password -> mysql_native_password に変更

###手順

１、認証プラグインを確認

SELECT host, user, plugin FROM mysql.user;

+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | user        　　　　　　　　 | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+

２、pluginをmysql_native_passwordに変更

ALTER USER 'user'@"localhost" IDENTIFIED WITH mysql_native_password BY 'password';

３、再度pluginの状態を確認してmysql_native_passwordになっていたらOK

SELECT host, user, plugin FROM mysql.user;

+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | user 　　　　　　　　        | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+

#問題3

###エラー３

SQLSTATE[HY000] [2002] Connection refused

#対処方法

hostをコンテナ内のIPアドレスにする

同じDocker内の通信なのでlocalhost(127.0.0.1)でいいと思っていたのですが、コンテナでIPアドレスが異なるみたいです

###手順

１、DBコンテナに接続してIPアドレスを確認

hostname -i

root@b3179d7e8745:/# hostname -i
172.19.0.4

２、１で確認したhostを設定する

$db = new PDO('mysql:host=172.19.0.4;dbname=db_name', $user, $pass);