mysqlのDB移行ではまったお話。
MySQL4系で動いてたシステムをMySQL5.5のサーバーへ移行する事となりました。その際にいくつかトラブルが発生し、はまってしまったお話です。
mysqlの移行作業
移行作業は以下のような形で行いました。 旧サーバーのデータベースのダンプを取得 [code] /usr/local/mysql/bin/mysqldump -pXXXX -u root -x -A > /tmp/my_dump.db [/code]
取ったdumpを新サーバーへ転送 [code] scp /tmp/my_dump.db hoge@xxx.xxx.xxx.xxx:/tmp/ [/code] 新サーバーでリストア [code] mysql -u root < /tmp/my_dump.db [/code] FLUSHを実行 [code] mysql -u root
FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) [/code] 新サーバー上からmysqlへの接続を確認して作業完了したのでした。
rootユーザーがなんかおかしい
問題はユーザー作成を行ったときに発覚しました。 rootでログインしてユーザー作成を行おうとするも、権限がないというエラーが発生してしまう。 [code] mysql> GRANT ALL PRIVILEGES ON . TO hoge@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION; ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES) [/code] 権限の確認の為に以下のコマンドで確認したのですが [code] mysql -u root mysql> select * from mysql.user where user='root'; [/code] 表示されるすべての項目は「Y」となっていました。
rootユーザーを再作成してみる
どうしても原因がわからなかったのでrootユーザーが変になったのだと決めつけた。 検証環境でrootユーザーを削除して再作成を実行してみました [code] mysql> DELETE FROM mysql.user WHERE user='root'; Query OK, 1 row affected (0.00 sec) mysql> exit [/code] mysqlを権限フリーモードで起動 [code] /path/to/mysqld_safe --skip-grant-tables & [/code] mysqlにログイン。rootユーザーのパスなしでログインできるようになっている。 [code] mysql -u root [/code] パスワードの設定 [code] mysql> update user set password=PASSWORD('パスワード') where user='root'; mysql> use mysql; [/code] このままでは権限は何ももたない一般ユーザーと同じなのでrootユーザーへ様々な権限を付与します。 この内容は正常に動作しているmysql5.5サーバーのrootユーザーの設定を確認しました。
別のmysqlサーバーにて以下のようにして権限の種類を確認
[code]
mysql -u root
mysql> select * from mysql.user where user='root';
+------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+------------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y
[/code]
この権限の通りに設定を行います。結果以下のコマンドになりました。 [code] mysql> update user setSelect_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y', Create_priv='Y', Drop_priv='Y', Grant_priv='Y', Alter_priv='Y', Shutdown_priv='Y', Process_priv='Y', File_priv='Y', Grant_priv='Y', References_priv='Y', Index_priv='Y', Alter_priv='Y', Reload_priv='Y', Show_db_priv='Y', Super_priv='Y', Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y', Repl_slave_priv='Y', Repl_client_priv='Y', Create_user_priv='Y', ssl_cipher='', x509_issuer='', x509_subject='', max_questions='', max_updates='', max_connections='' where User='root'; [/code] しかしこのコマンドでエラー発生しました。Create_user_priv といった項目など存在ない、というのです。 mysql4のドキュメントを確認した所、たしかに「Create_user_priv」はない。 mysql5.1や5.5のドキュメントでは「Create_user_priv」が存在します。
・権限システムはどのように機能するか（mysql4.1） - http://dev.mysql.com/doc/refman/4.1/ja/privileges.html ・MySQL 提供の権限（mysql5.1） - http://dev.mysql.com/doc/refman/5.1/ja/privileges-provided.html ・Privileges Provided by MySQL（mysql5.5） - http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html
つまり、バージョン違いによるprivilegesカラムの変化が問題だったんだよ！
なんだってー！ Ω ΩΩ
なんとなくバージョン違いなのが問題なんだろうなー、とわかってはきたのですがどうしたら解決できるの・・・と困っていたのですが、この件は会社の何人かに相談していて、nakagawa氏から以下のようなページを教えてもらったのでした。
4.5.4. mysql_upgrade — MySQL アップグレードのテーブル チェック http://dev.mysql.com/doc/refman/5.1/ja/mysql-upgrade.html
上記のページを確認してただ以下のコマンドを実行 [code] mysql_upgrade [/code] このコマンドにより [code] mysql> select * from mysql.user where user='root'; [/code] で確認した所 「Create_user_priv」の項目ができている事を確認。
この上で上記の権限を付与さしてあげました。 [code] mysql> update user setSelect_priv='Y', Insert_priv='Y', Update_priv='Y', Delete_priv='Y', Create_priv='Y', Drop_priv='Y', Grant_priv='Y', Alter_priv='Y', Shutdown_priv='Y', Process_priv='Y', File_priv='Y', Grant_priv='Y', References_priv='Y', Index_priv='Y', Alter_priv='Y', Reload_priv='Y', Show_db_priv='Y', Super_priv='Y', Create_tmp_table_priv='Y', Lock_tables_priv='Y', Execute_priv='Y', Repl_slave_priv='Y', Repl_client_priv='Y', Create_user_priv='Y', ssl_cipher='', x509_issuer='', x509_subject='', max_questions='', max_updates='', max_connections='' where User='root'; [/code] rootの設定が完了したら今は権限スルーモードで立ち上げているいるのでいったん終了して普通モードで起動します [code] /path/to/mysqld_safe & [/code] これでログインしてユーザーが作成できるか確認します [code] mysql -u root mysql> GRANT ALL PRIVILEGES ON . TO hoge@localhost IDENTIFIED BY 'パスワード' WITH GRANT OPTION; [/code] 成功しました！
まとめ
古いバージョンのmysql dumpを最近のmysqlに流し込んだ時は mysql_upgradeを実行し、mysqlテーブルを最新仕様にしておく。また、その後rootなどの管理ユーザーに適切な権限を付与する必要があるという事を学びました。
考えたらDB単位での移行であればこの問題はおきないでしょうね。まるごと移行を行うときは注意、というお話でした。
