Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
205
Help us understand the problem. What is going on with this article?
@ucan-lab

MySQL8.0 認証方式を変更する(Laravel5)

MySQL8.0 を初めてインストールして、
Laravelでマイグレーションを実行したときにエラーがでて罠にハマりました。

追記: 2020.04.12

PHP 7.1.16, 7.2.4 以降のバージョンから caching_sha2_password に対応するようになりましたので、この記事の設定は不要となります。

環境

  • MySQL8.0.x
  • PHP < 7.2.4

エラー内容

mysql コマンドで直接データベースへログインできたのですが、Laravel マイグレーションを実行するとエラーとなりました。

$ php artisan migrate
   Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknownto the client (SQL: select * from information_schema.tables where table_schema = blog and table_name = migrations)

  at /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668|

  Exception trace:

  1   PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")
      /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=blog", "vagrant", "P@assw0rd", [])
      /home/vagrant/blog/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:68

原因: MySQL8.0.4 デフォルトの認証方式変更

  • MySQL8.0.4以降 のログイン認証方式は caching_sha2_password がデフォルト
  • PHPのMySQL接続ライブラリが caching_sha2_password に未対応のため接続不可
  • 解決策としては認証方式を mysql_native_password に戻す

6.5.1.3 Caching SHA-2 Pluggable Authentication

既存ユーザーの認証方式を確認

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

対処法1: 既存ユーザーの認証方式を変更

> ALTER USER 'vagrant'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'vagrant'@'192.168.%' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'vagrant'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';
> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'P@ssw0rd';

対処法2: 新規ユーザー作成時の認証方式を変更

/etc/my.cnf に追記

[mysqld]
default_authentication_plugin=mysql_native_password

最初にこれを設定しておくべきでした。。

Sequel Proで接続できない問題

Sequel ProがMySQL8.0に対応していません!
執筆時のバージョンは1.1.2で1.2以降からMySQL8.0に対応する予定らしいです!

代替案

関連記事

205
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ucan-lab
Backend Developer at ROLO. I love PHP and I'm focusing on Laravel, Docker, GraphQL.
yyphp
PHPerが毎週集まり、ざっくばらんに情報交換する雑談コミュニティ

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
205
Help us understand the problem. What is going on with this article?