Go to Qiita Advent Calendar 2024 Top
LoginSignup
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@rettoukan

MySQL8.0でPDO接続できない(dockerコンテナ)

Last updated at Posted at 2019-06-22

TL;DR

  • MySQL8.0ではcaching_sha2_passwordという認証方式に変わったため、PDO接続するユーザの認証方式をmysql_native_passwordに変更する必要があった
  • hostにはdockerのコンテナ名を記入する
  • ユーザを新しく作成して、権限付与し、認証方式を変更する

はじまりのコード

public function connect()
    {
        try {
            $dbh = new PDO('mysql:dbname={db名};host=127.0.0.1', 'root', 'root');
            echo "接続成功\n";
            return $dbh;
        } catch (PDOException $e) {
            echo "接続失敗: " . $e->getMessage() . "\n";
            exit();
        }
    }

{DB名}の部分は自分のデータベース名を入れていました

で、以下エラー文

接続失敗: SQLSTATE[HY000] [2002] Connection refused

理由

hostにはdockerのコンテナ名を入力する必要がある
参考 : Docker Composerではコンテナ間通信はコンテナ名を指定する

hostを変更


$dbh = new PDO('mysql:dbname={db名};host={コンテナ名}', 'root', 'root');

以下エラー（同じ）
接続失敗: SQLSTATE[HY000] [2002] Connection refused

理由

rootの認証方式がchaching_cha2_passwordなので、mysql_native_passwordに設定する必要がある

# mysql接続(mac、もしくはdockerのdbコンテナ上で以下を実行)
❯ mysql -u root -p -h 127.0.0.1

# mysqlのユーザを表示
mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

解決策は以下。2. がおすすめ。

  1. rootのpluginを変更する
  2. 新しくユーザを作って、権限を変更しつつ、pluginを変更する

解決策1「rootのpluginを変更する」

# rootの認証方式をmysql_native_passwordに変更する
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+

これで接続成功した。

解決策2「新しくユーザを作って、権限を変更しつつ、pluginを変更する」

# user名がpoeでpasswordがpoeのユーザを作る。
# hostを%にすることで、外部(dockerコンテナ)から接続できるユーザになる
mysql> CREATE USER 'poe'@'%' IDENTIFIED BY 'poe';
Query OK, 0 rows affected (0.02 sec)

# 確認すると、poeができている
mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| poe              | %         | caching_sha2_password |
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
7 rows in set (0.00 sec)

# poeのpluginを変更する
mysql> ALTER USER 'poe'@'%' IDENTIFIED WITH mysql_native_password BY 'poe';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| poe              | %         | mysql_native_password |
| root             | %         | mysql_native_password |
| test             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
7 rows in set (0.00 sec)

これで接続してみると...エラーになってしまった...


$dbh = new PDO('mysql:dbname={db名};host={コンテナ名}', 'poe', 'poe');

接続失敗: SQLSTATE[HY000] [1044] Access denied for user 'poe'@'%' to database '{db名}'

エラーになった理由

ユーザの権限がないからエラー。このサイト(MySQL8.0ではGRANT構文でユーザを作成できない)を参考にしつつ、以下実行

# 権限確認
mysql> SHOW GRANTS for 'poe'@'%';
+---------------------------------+
| Grants for poe@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `poe`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

# 権限変更
mysql> grant all on *.* to 'poe'@'%' with grant option;
Query OK, 0 rows affected (0.03 sec)

# 権限反映
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

# 権限めっちゃ増えたわ
mysql> SHOW GRANTS for 'poe'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for poe@%                                                                                                                                                                                                                                                                                                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `poe`@`%` WITH GRANT OPTION                                       |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `poe`@`%` WITH GRANT OPTION |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

すると、接続成功しました。

補足 : 新しく作ったユーザの認証方式を全部mysql_native_passwordにさせる

[mysqld]
default_authentication_plugin=mysql_native_password

これをmy.cnfに追加すると、
新規ユーザ作成時、認証方式が自動的にmysql_native_passwordとなるらしいです

そのほかにも出たエラー

接続失敗: sqlstate[hy000] [2002] no such file or directory
多分hostをlocalhostにしているから。dockerだとhostはコンテナ名

SQLSTATE[HY000] [2003] Can't connect to MySQL server on 'localhost' (10061).
mysqlに入ってuserのhostを見て欲しい。localhostになってるでしょ。ここが%じゃないと外部サーバーからの接続はできないんだと思う

5
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?