SEEDS Creator's Blog

読者です 読者をやめる 読者になる 読者になる

MySQLでDB移行する時はmysql_upgradeを行おう

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.htmlMySQL 提供の権限(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単位での移行であればこの問題はおきないでしょうね。まるごと移行を行うときは注意、というお話でした。