mysqlのDB移行ではまったお話。
MySQL4系で動いてたシステムをMySQL5.5のサーバーへ移行する事となりました。その際にいくつかトラブルが発生し、はまってしまったお話です。
移行作業は以下のような形で行いました。
旧サーバーのデータベースのダンプを取得
[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単位での移行であればこの問題はおきないでしょうね。まるごと移行を行うときは注意、というお話でした。
