MySQL 5.6 old_passwords=1 環境へのユーザー情報移行が難しい
Windows Server 2003 で稼働させている MySQL 5.0 を Windows Server 2012 / MySQL 5.6 にアップグレードしようとしています。 古い MySQL クライアントライブラリを使用しているプログラムがある関係で、MySQL 5.0 では old_passwords オプションを指定して運用してきました。
MySQL 5.6 でもこの状態(old_passwords)は維持したいと考えています。まずはユーザーおよびその権限データを新サーバーに移してみようとしたところ、なかなかうまく行きませんでした。以下いろいろ試してみた結果です。
1. mysql データベース dump & restore
5.0 サーバーの mysql データベースを mysqldump コマンドで dump して、5.6 サーバーにインポートしてみました。この方法だと、mysql データベースの各テーブルの構造が違うためか MySQL サービスが起動しなくなってしまうようで、あきらめました。
2. show grants
5.0 サーバーで
show grants for 'ユーザー名'
として、表示された SQL 文を 5.6 サーバーで実行してみました。表示された(=実行した) SQL 文は例えば
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '5d2e19393cc5ef67';
ですが、
ERROR 1827 (HY000): The password hash doesn't have the expected format. Check if
the correct password algorithm is being used with the PASSWORD() function.
というエラーが出て実行できませんでした。 試しに、
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
と、生のパスワードを指定してみましたが、同じエラーが出て実行できませんでした。old_passwords = 1 の環境ではパスワード付きのユーザーを作成できないということなんですかね??
3. mysql.user テーブル直接書き換え
仕方がないので、まずはパスワード無しのユーザーを作成し、その後 show grants の結果から 'IDENTIFIED BY..." 以降を削除した SQL 文を実行します。
create user 'user'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%';
さらに、mysql.user テーブルの password フィールドに直接パスワードをセットします。
update mysql.user set password='5d2e19393cc5ef67' where user='user' and host='%';
flush privileges;
これで、Version 5.6 の MySQL クライアント(mysql.exe)で接続してみます。
>mysql -u user -ppassword
以下のエラーが出て接続できませんでした。
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password:
YES)
さらに、mysql.user テーブルの plugin フィールドを空にしてみます。デフォルトではここに 'mysql_native_password' がセットされています。空をセットすることでパスワードハッシュの値に応じて 'mysql_native_password' か 'mysql_old_password' プラグインのいずれかを呼び出してくれるようです。MySQL 5.6 マニュアル
update mysql.user set plugin='' where user='user' and host='%';
flush privileges;
もう一度 mysql.exe で接続してみます。
>mysql -u user -ppassword
またエラーですが、今度はエラーメッセージが変わりました。
ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol ref
used (client option 'secure_auth' enabled)
どうやら、5.6 付属の mysql.exe ではデフォルトで secure_auth オプションが有効になっているようです。明示的に無効にしてみます。
>mysql -u user -ppassword --skip-secure-auth
やっと接続出来ました。
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.15-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
という訳で何とかなりましたが、何かもっと簡単な方法があるような気もします...。