mysql移行作業

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を設定する。