MySQLブログ | システム監視・サーバ監視・障害監視・ネットワーク監視・システム運用・MySQL・Web地図を提供

バックアップからのリカバリが必要になるケースは、冗長化された本番環境では中々お目にかからない事もあるかと思います。
リカバリが必要な事態に遭遇し、いざリカバリしてみたら「これリカバリできないじゃないか…」みたいなケースもあったりで。
そういった事にならない為にもリカバリ手順はしっかりと確認しておきましょう。
前回取得したオンラインバックアップを実際に戻した際の手順をリストアップしておきます。

①MySQLサーバが停止していない場合は停止
[mysql@SERVER ~]$ sudo /sbin/service mysql stop
Shutting down MySQL.. [ OK ]

※Serviceコマンドによる起動停止は事前に要設定

➁ 未バックアップのバイナリログファイルをバックアップ
[mysql@SERVER ~]$ cp –p /data/mysql/blog/mysql-bin.* /backup/mysql/blog/
前回バックアップ以降に更新されたデータのロールフォワードリカバリ用

③バックアップファイルを対象ディレクトリにリストア
[mysql@SERVER ~]$ tar -C /data/mysql/ -xvf Hotbk20090428.tar

④初期化パラメータファイルのバイナリログ出力設定を停止し、リモート接続禁止状態でMySQLサーバを起動
[mysql@SERVER ~]$ vi /data/mysql/cnf/my.cnf
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=/data/mysql/blog/mysql-bin
[mysql@SERVER ~]$ sudo /sbin/service mysql start –skip-networking
Password:
Starting MySQL [ OK ]
ロールフォワードリカバリ中のバイナリログ出力を避け、リカバリ中のクライアント接続を防ぎます。

⑤前回バックアップ以降に出力されたバイナリログから、ロールフォワードリカバリ用のSQLを作成
[mysql@SERVER ~]$ mysqlbinlog --disable-log-bin mysql-bin.000002 mysql-bin.000003 > \
recover20090429_2.sql
前回バックアップ以降に出力されたバイナリログが「mysql-bin.000002」、「mysql-bin.000003」の前提です。
「–disable-log-bin」はロールフォワードリカバリ中のバイナリログ出力を抑止します。リカバリ用SQLは「recover20090429_2.sql」に出力されます。

なお上記は障害直前まで復旧するSQLを出力しますが、ポイントタイムリカバリを行う場合は以下のように指定します。

[mysql@SERVER ~]$ mysqlbinlog --disable-log-bin --stop-datetime="2009-04-29 17:38:15" \
mysql-bin.000002 mysql-bin.000003 > recover20090429.sql
上記例では2009年4月29日の17時38分15秒までに更新されたデータがリカバリ用SQLファイルに出力されます。

⑥ロールフォワードリカバリ用SQLをMySQLクライアントから適用
[mysql@SERVER ~]$ mysql -u root -prootpass --default-character-set=cp932 < \
recover20090429_2.sql
⑦初期化パラメータファイルのバイナリログ出力設定を戻し、MySQLサーバを再起動
[mysql@SERVER ~]$ vi /data/mysql/cnf/my.cnf
# Replication Master Server (default)
# binary logging is required for replication
log-bin=/data/mysql/blog/mysql-bin
[mysql@SERVER ~]$ sudo /sbin/service mysql restart
Password:
Shutting down MySQL.. [ OK ]
Starting MySQL.. [ OK ]

以上がLINUX環境でローカルディスクで運用している場合のバックアップリカバリ手順となります。
共有ストレージ製品が利用可能な場合は、LVM Snapshotではなく、ストレージ製品のスナップショット機能や遠隔ミラー機能のsync-split等で同等のオペレーションが可能です。

今回はMySQLのオンラインバックアップ手順について書いてみます。
オンラインバックアップ環境の概要は MySQLバックアップリカバリ　推奨手順 （LINUX環境） を参照ください。

この手順では、MyISAMとInnoDB混合環境を想定し、システム全体で一貫性のあるバックアップを前提としています。
①MySQLサーバに接続し、データベースの全テーブルに対する共有ロックを取得
[mysql@SERVER ~]$mysql -u root –prootpass
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.79-enterprise-gpl-log MySQL Enterprise Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>flush tables with read lock;

flush tables with read lock文は、バッファキャッシュ上のデータが全てファイルにフラッシュし、MySQLサーバの全テーブルに共有ロックをかけます。

②LVM Snapshotの作成
[mysql@SERVER ~]$ sudo /usr/sbin/lvcreate --snapshot -L4G -n snapLV02 \
/dev/VolGroup00/LogVol02
Logical volume “snapLV02″ created

※sudoの実行には事前にvisudo でsudoersを設定が必要

/dev/VolGroup00/LogVol02 は、MySQLのバックアップ対象ファイルが保存されたボリュームです。
複数ボリュームに対象ファイルが存在する場合は、複数スナップショットを作成してください。

③バイナリログのスイッチとポジションの確認
mysql>FLUSH LOGS;
mysql>SHOW MASTER STATUS;
+————–+————+————+———–+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————–+————+————+———–+
| mysql-bin.000002 | 98 | | |
+————–+————+————+———–+
FLUSH LOGSを実行すると、バイナリログ（バイナリログについては3-5で説明します）がスイッチされます。リカバリ後のロールフォワードは、ここでスイッチされたものから適用することになります。この際、SHOW MASTER　STATUSで、スイッチされた後のバイナリログファイル名とポジションを確認することをお勧めします。

④テーブルの共有ロックを解除
mysql>unlock tables;
Unlock tables文を発行することで、①で取得した全テーブルへの共有ロックを解除します。
※①～③の操作中にクライアント接続を切断するとロックが解除されてしまうため、スクリプト等で自動化する場合は、以下のように実行します。
[mysql@SERVER ~]$ mysql -u root -prootpass -e ‘flush tables with read lock;system \
sh LVsnap.sh ; flush logs; show master status ; unlock tables;’

⑤Snapshotボリュームを読取専用でマウント
[mysql@SERVER ~]$ sudo /bin/mount -o ro /dev/VolGroup00/snapLV02 /snapLV02

⑥バックアップ対象ファイルをアーカイブ
[mysql@SERVER ~]$ cd /snapLV02/mysql/;tar cvf /backup/mysql/Hotbk20090428.tar ./*
この例では、/snapLV2/mysql/ 配下に必要な対象ファイルが全て存在する事とします。また、バックアップ先ボリュームは /backup/mysql となります。

⑦snapshotボリュームをアンマウントし、snapshotを削除
[mysql@SERVER ~]$ sudo /bin/umount /dev/VolGroup00/snapLV02
[mysql@SERVER ~]$ sudo /usr/sbin/lvremove -f /dev/VolGroup00/snapLV02
Logical volume “snapLV02″ successfully removed

⑧バックアップ済みのバイナリログを削除
mysql>PURGE MASTER LOGS TO mysql-bin.000002
③で確認したバイナリログファイル以前のファイルがバックアップ済みである前提です。 mysql-bin.000002より以前のバイナリログファイルは全て削除されます。

パフォーマンス検証結果を踏まえ、弊社なりにポイントを整理してみました。
■InnoDBの主キー検索は非常に高速。検索重視のMyISAMにも劣らない。
InnoDBの特徴であるクラスターIndexは、主キー(B-tree)のリーフページにデータが直接格納されています。従って、主キーと行データの紐付けにかかるI/Oコストが少ない。

■バッチ処理等による大量データのInsert時、またMyISAM利用時はバルクロードが高速
注意点として、ロード対象がレプリケーションのMasterで、ステートメントベースレプリケーションの場合、Slave側にもロード用のデータが必要となる。（行ベースレプリケーションでは必要ないが、代わりにログ出力のオーバーヘッドがある。）

■アプリケーションによるInsert処理は、InnoDBでは可能な限りマルチプルInsertが望ましい
※MyISAMではマルチプルInsertでも劇的には速くはならない。

■MySQL5.1系と比較して、MySQL5.0系が若干高速
InnoDB、MyISAM共にMySQL5.0系が若干パフォーマンスが出ており、安定版として枯れてきている事が想定される。

なおMySQL5.1系の導入を検討する場合、注意が必要です。

MySQL5.1系は昨年末リリースされたばかりで、パーティショニング機能や、行ベースレプリケーション機能等にバグが残っているため、導入前に入念な検証が必要となる。

【参考】公開中のバグ
※弊社といたしましては、安定版であるMySQL5.0を推奨します。

前回まではSelect系のパフォーマンスを紹介しました。
今回はInsertのパフォーマンスについてふれてみます。
Insert方式は用途に応じて各種選択が可能です。
大量データの一括ロード時は、SQL文のパースが必要ないバルクロードを利用すると効果的です。
アプリケーション仕様上、登録行セットをまとめることが可能であれば、MySQL独自構文であるマルチプルInsert文（１回のInsert文発行で複数行セットをInsertする）を利用すると効果的です。

• バルクロード ：可変長データ（CSV等）、固定長データからのデータロード
  mysql> LOAD DATA INFILE “import_data.csv” INTO TABLE tableA FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’;　
• マルチプルInsert（MySQL独自構文）：Insert文１発行で複数行セットを挿入するInsert文
  mysql> INSERT INTO emp (empid,name,country) VALUES(1,’taro’,’jpn’),(2,’jiro’,’usa’),(3,’saburo’,’ita’),(4,’shiro’,’chu’);
  ※１Insert文のサイズは、初期化パラメータ　max_allowed_packet以内となることが条件
• 通常Insert：Insert文１発行で１行を挿入するInsert文
  mysql> INSERT INTO emp (empid,name,country) VALUES(1,’taro’,’jpn’);
  mysql> INSERT INTO emp (empid,name,country) VALUES(2,’jiro’,’usa’);

■処理時間比較
Row長約２００bytes、100万件（約200Mbytes）のデータを投入した場合の比較 （単位：秒）
（サーバ環境とMySQLパラメータは前項目と同一。NO RAID、元データは同じローカルDISKよりロード）

※1000件分を計測し×1000で算出

【参考】1,000万件ロード時

最後にフルスキャンも試してみました。
READ FTS　　　　　 ：Indexのないカラムの最小値を取得

非Indexカラムの最小値取得はテーブルのフルスキャンとなり低速です。但し、MySQL機能であるクエリキャッシュを有効（query_cache_type=ON)にする事で再発行時の検索コストを抑える事は可能です。