mysqlのサーバ移設作業ではまったことについて書きます。
1. OS及びDBサーバのバージョン
[現行サーバ]
OS: Redhat Linux: 2.4.21-40 , DB: Mysql: 4.1.18
[新サーバ]
OS: Redhat Linux: 2.6.9-42, DB: Mysql: 4.1.22
2. ダンプ&レストア
まずは現行サーバで
% mysqldump –add-drop-table -u[ユーザ名] -p[パスワード] [db名] > [db名].dump
で対象のDBのダンプファイルを作成。その後、ダンプファイルを新サーバへコピーし、
% mysql -u[ユーザ名] -p[パスワード] < [db名].dump
でレストアしたが、
Error: 1064 SQLSTATE: 42000 (ER_PARSE_ERROR)
とかでエラーが発生し、止まってしまった。
そこで、レストア方法を変更しようと思い、ダンプファイルをデータファイル形式（タブ区切り）で再度作成し、mysqlimportを使ってレストアにトライした。
[現行サーバ]
% mysqldump –add-drop-table –tab=. –fields-enclosed-by='”‘ -u[ユーザ名] -p[パスワード] [db名]
※上記方式ではダンプファイルはテーブルごとにsqlファイル、データファイルに出力される。~/dump/などのディレクトリを作成し、その下で実行すると良い。
↓rsyncで対象ファイルを全て新サーバへコピー（rsync -av)
[新サーバ]
% mysql -u[ユーザ名] -p[パスワード] < [sqlファイル]
% mysqlimport -v -u[ユーザ名] -p[パスワード] [DB名] ./dump/[データファイル]
※テーブル数が多いと大変なので、バッチ処理する。
インポート作業は何とか完了したものの、移行データの中身を見ると”?????”のオンパレード。
これはおかしいと思い、現行のDBサーバと新サーバのDB文字コードを
mysql > SHOW VARIABLES LIKE ‘char%’ ;
で確認すると、現行サーバは
+————————–+—————————————-+
| Variable_name | Value |
+————————–+—————————————-+
| character_set_client | sjis |
| character_set_connection | sjis |
| character_set_database | sjis |
| character_set_results | sjis |
| character_set_server | sjis |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+————————–+—————————————-+
であるのに対し、新サーバではsjisがlatin1となっていた。。。
そりゃ化けるは。。。と気づいたところで、新サーバでmysqlを再構築。下記のようにconfigした。
./configure \
–prefix=/usr/local/mysql \
–with-extra-charsets=all \
–with-charset=sjis \
–with-mysqld-user=mysql \
–enable-local-infile
あとは通常のインストールで完了。上記方法にて文字コード確認後、再度最初の方法でダンプ＆レストアを行ったところ、無事移行完了！
3. WebサーバからDBサーバへの接続確認
まず、webサーバから、
% mysql -h[新DBサーバip] -u[ユーザ名] -p[パスワード]
でDB接続を確認。
webサーバから現行DBサーバへはphpで接続を行っているため、プログラム中のDBホストを新サーバに設定し、接続を確認。下記エラーが発生。
PHP Warning: mysql_connect(): Client does not support authentication protocol requested by server; consider upgrading MySQL client in
ネットで調べたところ、新旧バージョンuserパスワード中の相違により、認証エラーが発生しているとのこと。新サーバで
mysql > UPDATE user SET Password = OLD_PASSWORD(‘[ユーザパスワード]’) where User='[ユーザ名]’;
mysql > FLUSH PRIVILEGES;
を実行。webサーバから再度接続したところ、ようやくOKとなった。
4. まとめ
(1) 新旧mysqlサーバのインストール文字コードを必ず統一する。
(2) 接続エラーの際は、ユーザアカウントのOLDPASSWORDを設定する。