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

#問題１

###エラー1

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

#対処方法

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

###手順

１、ユーザーの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
```

#対処方法

:::note info
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
```

#対処方法

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

:::note warn
同じ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);
```