MySQL のユーザのパスワード変更の方法です。ちょっとハマったので記事にしました。

MySQL８、ユーザのパスワードが変更できない？！

なぜか、パスワードがうまく変更できない・・

mySQL８で、ユーザのパスワードを変更しようとするとなぜかエラーが発生して変更できません。下記は、ぐぐるとたくさんでてくる、MYSQL５.7まで有効なコマンドで、MySQL8では利用できません。

//失敗例１
mysql > update mysql.user set password=password('hogehoge') where user = 'root';

//失敗例２
mysql > SET PASSWORD FOR root@localhost=PASSWORD('hoge');

//結果
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD('root')' at line 1

PASSWORD() 関数は、MySQL version 5.67 までで廃止されている！

なんか文法エラーかと思ったら、MySQL 8 においては、そもそも関数が存在しないようですね・・。

MySQL８での、ユーザのパスワード変更（リセット）の方法

もう少し詳しく調べていくと、MySQL8 では、下記のコマンドでパスワードを変更することになっているようです。

mysql > USE mysql;
mysql > ALTER USER 'root'@'localhost' identified BY 'hoge';

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

無事パスワードが変更できました！

【補足】root ユーザのパスワード忘れて、ログイン出来ないとき

もし「パスワードがわからなくなてログインできない！という場合は、いちどセーフモード（mysqld_safe）で起動し、パスワードなしでログインします。

// 一度終了
$ mysql.server stop

// セーフモードでの起動、
$ mysqld_safe --skip-grant-tables &

// パスワードなしでログインできる
$ mysql -u root

一度パスワードを空にします。

// root のパスワードを空にする
mysql > UPDATE mysql.user SET authentication_string=null WHERE User='root';
mysql > exit;

その後、MYSQLを再起動。空のパスワードでログインして、上記のALTERコマンドを実行します。

// MySQL再起動（非セーフモード）
$ mysql.server restart

// 空のパスワードでログイン
$ mysql -u root -p 

// 
mysql > USE mysql;
mysql > ALTER USER 'root'@'localhost' identified BY 'hoge';

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 

【補足】MySQL８のパスワード認証プラグインについて

MySQLでは、パスワードの認証時に、実行される、「authentication plugin」という仕組みのがあります。

例えば上記のコマンドで設定したパスワードについて、下記のコマンドで、確認できます。

mysql> select User, Plugin from mysql.user where User = 'root';
+------+-----------------------+
| User | Plugin                |
+------+-----------------------+
| root | caching_sha2_password |
+------+-----------------------+

下のコマンドで確かめられるように、MySQL8　からは、「caching_sha2_password」がデフォルトとなったようなのですが・・、

mysql > use mysql;
mysql > show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+

mysqlを利用するクライアントソフトウェア（例えば、PhpMyAdminなど）では、この認証方法をサポートしていないため、エラーとなりログイン出来ないことがあるようです。

これは、パスワードを下記のコマンドで設定すると、回避できます。

mysql> ALTER USER 'root'@'localhost' identified WITH mysql_native_password BY 'root';
Query OK, 0 rows affected (0.01 sec)

「mysql_native_password」は後方互換性のある認証のようです。

同様に「my.cnf」にも書き込んでおきます。これでデフォルトの認証プラグインを変更できましたので、これ以降の新規ユーザ作成時は、デフォルトで、mysql_native_password　が使用されます。

[mysqld]

# これがないと、デフォルトは、caching_sha2_password
default_authentication_plugin=mysql_native_password

MySQL 8のパスワード変更についての参考サイト

安心のStackoverflowにも、同じ質問がありました。

• Stackoverflow: phpMyAdmin on MySQL 8.0
• Stackoverflow: How to reset the root password in MySQL 8.0.11?

こちらは公式ドキュメント。言及があるようです：

• MySQL: Encryption and Compression Functions

こちらは、日本語で詳しくまとめてくださっているページです。

• MySQL8.0新機能 (caching_sha2_password 認証プラグイン)

以上です。