MySQL のバックアップ手法いろいろ

mysqldump

mysqldump コマンドを使うだけなのだが、バックアップ専用のアカウントをつくるたびに、どの権限が必要だったろうかと迷う。

1. サーバ側で dump 用ユーザを作成する。ここではユーザ名を backup とする。SELECT, SHOW VIEW, LOCK TABLES, RELOAD 権限が必要。ローカルサーバで mysqldump する場合は、さらに FILE 権限も必要。

mysql> grant select, show view, lock tables, reload on *.* to backup@192.168.0.111 identified by '<パスワード>';
 
SELECT
 バックアップ対象テーブルを読み取るのに必要。
SHOW VIEW
  VIEWをダンプするのに必要。
LOCK TABLES
 整合性を持った状態でダンプするのに必要。mysqldump 実行時に --skip-lock-tables を付ける場合は不要。
RELOAD
 mysqldump が FLUSH TABLES を実行するために必要。

ネットワーク接続を TCP wrapper や iptables で制限している場合はそちらの解除も忘れずに。

$ cat /etc/hosts.allow
...
mysqld: 192.168.0.111

2. クライアント（リモート）側で mysqldump を実行する。

mysqldump --host=192.168.0.100 --user=backup --password=<パスワード> --lock-all-tables <DB名> > DB名.sql

ダンプ中の insert などで整合性が保てなくなっても良い場合は、--skip-lock-tables をつける。この場合は LOCK TABLES 権限は不要だし、ダンプ中に更新が止まることもない。

mysqlhotcopy

テーブルをロックして、データベースディレクトリをそのままコピーする。mysqldump よりもコストが低いが、同じホスト内でしか使えない。また、MyISAM と ARCHIVE テーブルでのみ利用可能。

ファイルシステムのスナップショット

MySQL 上で FLUSH TABLES WITH READ LOCK した上で、ファイルシステムレベルでスナップショットを取る。同じホスト内でしか使えないが、更新停止時間は mysqldump や mysqlhotcopy よりも格段に短い。スナップショットを取るのは一瞬ということになっているが、Amazon EBS の場合はコマンドが Java で書かれているため、実際には数秒止まる。経験上10秒を超えたことはない。ファイルシステムに XFS を用いて、こんなスクリプトでバックアップしている。

#!/bin/sh
 
EC2_HOME=/usr/local/ec2/ec2-api-tools-1.3-41620
EC2_PRIVATE_KEY=/mnt/pk-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx.pem
EC2_CERT=/mnt/cert-XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.pem
 
MYSQL_USER=<ユーザ名>
MYSQL_PW=<パスワード>
 
MOUNT_POINT=/mnt/vol1
VOLUME=vol-xxxxxxxx
 
MYSQL_CRED="-u$MYSQL_USER -p$MYSQL_PW"
 
mysql $MYSQL_CRED -e "flush tables with read lock;"
mysql $MYSQL_CRED -Nse "show master status" > $MOUNT_POINT/mysql/master_snapshot_position
mysql $MYSQL_CRED -Nse "show slave status" > $MOUNT_POINT/mysql/slave_snapshot_position
 
xfs_freeze -f $MOUNT_POINT
ec2-create-snapshot $VOLUME
xfs_freeze -u $MOUNT_POINT
 
mysql $MYSQL_CRED -e "unlock tables;"

レプリケーションしてスレーブ側でバックアップする

レプリケーション構成で、バックアップ専用のスレーブを設ける。そのスレーブでどんなに長時間ロックしようとも、システムには影響が出ない。

レプリケーション

上記の他の手法とは意味合いが異なるが、レプリケーションもバックアップのひとつだ。リアルタイムに別サーバにバックアップされるので、ハードウェア障害に強く、最新のデータまで守れる。ただし間違って DROP TABLE したなどの操作ミスもリアルタイムに伝播するので、人為的ミスやアプリケーションバグには弱い。

RAID, DRBD, ...

デバイスレベルでリアルタイムバックアップという意味で、MySQL のレプリケーションと同じ強みと弱みを持つ。

バックアップ手法は、リアルタイム系（フロー）とスナップショット系（ストック）に大きく分かれる。データを守るという観点からは、リアルタイムバックアップ（レプリケーション、RAID）でハードウェア障害に備えつつ、スナップショットで操作ミスに備えるという構成でシステムを組むことになる。