WindowsのPythonでMySQL80を触ろうとしたら躓いた話

本題

MySQLが8.0になった際、認証周りで変更があったため、Pythonから触ろうとしたらいろいろ躓いたのでそれらをまとめた。

なお環境は下記のとおりである。

OS:Windows 10
Mysql 8.0
Driver: mysql-connector-python-rf

発生したエラー

Traceback (most recent call last):
  File "****.py", line 46, in <module>
    charset = 'utf8')
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\__init__.py", line 179, in connect
    return MySQLConnection(*args, **kwargs)
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\connection.py", line 95, in __init__
    self.connect(**kwargs)
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\abstracts.py", line 716, in connect
    self._open_connection()
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\connection.py", line 210, in _open_connection
    self._ssl)
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\connection.py", line 142, in _do_auth
    auth_plugin=self._auth_plugin)
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\protocol.py", line 102, in make_auth
    auth_data, ssl_enabled)
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\protocol.py", line 58, in _auth_response
    auth = get_auth_plugin(auth_plugin)(
  File "C:\Users\***\Local\Programs\Python\Python35\lib\site-packages\mysql\connector\authentication.py", line 191, in get_auth_plugin
    "Authentication plugin '{0}' is not supported".format(plugin_name))
mysql.connector.errors.NotSupportedError: Authentication plugin 'caching_sha2_password' is not supported

発生した理由はMySQL8.0で追加されたchaching_sha2_password機能のせい

簡単な理由説明

MySQL8.0になってからユーザ認証方式として従来のmysql_native_passwordとは別にcaching_sha2_passwordが追加された。しかしプログラミング言語からMySQLにつなぐためのドライバの多くがcaching_sha2_passwordに対応していないために認証時にエラーが発生する。

やること

検索すると下記二つのどちらかでいいという記事が出てくるが、私は両方やらないと解決しなかった。

  • MySQLの特定のユーザの認証方式を変える。
  • MySQLのパスワードの認証方式を変える。

MySQLの特定のユーザの認証方式を変える。

MySQLにrootでログインし、変更したいユーザのパスワードを変更する。usernameとpasswordは各々の設定したいユーザ名と認証するときに利用するパスワードを入れてください。

mysql> alter user 'username'@'localhost' identified WITH mysql_native_password by 'password';

設定が反映されたことの確認

mysqlのプロンプトからSELECT user, host, plugin FROM mysql.user;と打ち込んでください。

設定したユーザのpluginがmysql_native_passwordになっていればよいようです。

この段階でうまくいく人もいるらしいです。わたしはうまくいかなかったのでmysql自体の設定をいじりに行きます。

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| ******           | localhost | 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 |
| ****             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+

MySQLの認証方式を変える

MySQLの設定ファイルであるmy.iniを変更しに行きます。

my.iniC:\Program Data\MySQL\MySQL Server 8.0\にあります。

このProgram Dataというディレクトリは通常隠しファイル扱いになるのでエクスプローラの表示から隠しファイルのチェックボックスを入れてください。

f:id:kataware8136:20180530213537p:plain

my.iniの変更ですが、default_authentication_plugin=mysql_native_passwordとしてください。

[mysqld]

---(snip)

#default_authentication_plugin=caching_sha2_password
default_authentication_plugin= mysql_native_password

設定ファイルを変更したらMySQLのデーモンを再起動します

コマンドプロンプトから再起動

管理者用のコマンドプロンプトで実行してください

> net stop MySQL80
> net start MySQL80

サービス管理画面から再起動

win + rここに入力して検索のところでservices.mscと打ち込むとサービス管理画面を起動できます。

ここからMySQLのサービスを選んで右クリック、再起動してください。

設定が反映されたことの確認

mysqlのプロンプトからshow variables like 'default_authentication_plugin';を打ち込み、valuemysql_native_passwordとなっていれば大丈夫です。

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

さいごに

pythonのドライバだけではなくphpのドライバでも同様の問題は起こっているみたいです。直にドライバも対応していくでしょうし、ドライバのほうがcaching_sha2_passwordに対応したら今回の設定内容を直すのが無難です。

というのもmysql_native_passwordよりも安全な認証方式になっているようです。なので今回の方法は一時しのぎといった感じでしょうが役に立てば幸いです。

参考文献

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

caching_sha2_passwordをサポートされているコネクタ一覧