Help us understand the problem. What is going on with this article?

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

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

追記: 2020.04.12

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

https://www.php.net/manual/ja/ref.pdo-mysql.php

環境

  • 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に対応する予定らしいです!

代替案

関連記事

ucan-lab
Backend Developer at ROLO. I love PHP and I'm focusing on Laravel, Docker, GraphQL.
https://u-can.pro
miraito-inc
システムデザインを中心に置いた開発により高品質で使いやすいシステムを提供いたします。業務システム構築、アプリ開発、コンサルティングまで幅広く手がけています。
https://miraito-inc.co.jp/
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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした