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