0時ちょうどに MySQL の snapshot をとりたい (注: MyISAM 限定)

今日書いてたスクリプトの結果待ちで、ひさしぶりにメモ。

やりたいこと

  • 毎日0時ちょうどに mysql のデータの snapshot を取りたい
  • snapshot はバックアップのためではなく、アクセスできる状態のものにしたい
環境
  • MySQL-5.6.10
    • サービスで使っている master は別にいて、 snapshot 取る用に slave を用意します
    • あとで出てきますが mysqlbinlog の出力フォーマットとかで他のバージョンは未確認
  • Solaris11u1, ZFS
    • 11 以上ならたぶん大丈夫かと

0時ちょうど

「毎日0時ちょうど」という要件を満たすために、0 時よりちょっと前 (23:55とか) に slave の sql_thread を止め、 relaylog が十分溜まってから mysqlbinlog で必要な pos を調べ、そこまで sql_thread を走らせることにします。

たとえば master から 0 時ちょうどを判定できる event が流れてくればいいんですけれど、そうでない場合、 0 時前後の event たちを眺めて「ここまで実行したら 0 時ちょうど」を (sql thread を止めたまま) 確認しないといけません。 master が binlog 書いた時間を調べる方針もあるかもしれませんが、 master には負荷を掛けたくないです。

mysqlbinlog の出力

たとえば以下のような出力があるとします。

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130302  0:00:09 server id 100  end_log_pos 120   Start: binlog v 4, server v 5.6.10-log created 130302  0:00:09
# at 120
#130302  0:00:09 server id 200  end_log_pos 167     Rotate to master.004175  pos: 4
# at 167
#130302 21:03:32 server id 200  end_log_pos 98      Start: binlog v 4, server v 5.5.2-log created 130302 21:03:32
# at 261
#130302 21:03:32 server id 200  end_log_pos 306     Query   thread_id=2718205182    exec_time=0     error_code=0
use `hoge`/*!*/;
SET TIMESTAMP=1362063599/*!*/;
INSERT INTO foo .../*!*/;
# at 469
#130302 21:03:32 server id 200  end_log_pos 555     Query   thread_id=2718205180    exec_time=0     error_code=0
SET TIMESTAMP=1362063600/*!*/;
UPDATE foo ..

ちなみに SHOW RELAYLOG EVENTS で似たようなものを出せますが、 timestamp を知ることはできないようです。 mysqlbinlog が OS のプロセス実行でキモい気もしますが、 snapshot 作成のとこでも必要だし、まあ気にしない。

SET TIMESTAMP 文が 0 時を越えた直前の pos が「0時ちょうど」になるはずです。この例だと pos=469 が 0 時ちょうど、ということになりますね。

範囲を絞る

この出力を地味に理解するのはアホっぽいので

% mysqlbinlog --start-datetime="2013-03-01 00:00:00" --end-datetime="2013-03-01 00:00:01" relaylog.000123

でもうちょい範囲を絞ることにします。こうすると、 pos=469 以降が出力されます。(逆に pos=261~469 が出力されません。)

いらないのを削る

絞った結果の head -n1 が pos=469 だったらいいんですが、最初の pos=4 とか 107 とかは mysqlbinlog の出力にどうしても入ってきてしまいます。しかも 107 なんてバージョンで微妙に変わりそうですよね。

そこで、 # at [pos] の次のコメント行で Start binlog とか Rotate ではなく Query になってるものを出すようにします。

% mysqlbinlog --start-datetime="2013-03-01 00:00:00" --stop-datetime="2013-03-01 00:00:01" relaylog.000123 \
    | sed -n '/^# at [0-9]\+/{N; /#[0-9].*Query/{P; q}}'
# at 469
見つからなかったら?

replication がすごい遅れてると、 0 時ちょうどの event がまだ届いてないかもしれません。その場合は適当に sleep して、存在する relaylog ファイルを順に調べることにします。

23:55 に sql thread 止めるところから始めているので、むしろ最初は見つからないはずです。

その pos まで replication を進める

あとはそのまま。

mysql> START SLAVE SQL_THREAD UNTIL RELAY_LOG_FILE="relaylog.000123", RELAY_LOG_POS=469

snapshot 作成

バックアップだったら mysqld 落として datadir をそのまま tar すればいいですけれど、たとえばデータベース名 hoge だったら hoge_20130301 で 2013/03/01 時点の snapshot にアクセスできると便利ですよね。

そこで、ディレクトリをそのままコピればオッケーな MyISAM の登場です。仮に master が InnoDB でも、この slave だけえいやっと alter table ... engine=myisam しておきましょう。

コピーする
% mysql -e 'FLUSH TABLES WITH READ LOCK; SELECT sleep(3600)' &
% cp -p /srv/mysql/hoge /srv/mysql/hoge_20130301
% kill %1

いや、しかし日数分だけコピーして容量増えるとか無理そうな気がしますね。というか 3600 秒以内にコピーが終わらないとひどいことに...。

zfs clone

そこで zfs の登場です。

zfs snapshot は差分だけしか容量を食わないので、たとえば 100GB のデータベースだが更新されるのは毎日 10MB くらいだとすれば、1日の snapshot に必要な容量は 10MB くらいで済みます。

さらには snapshot, clone はほぼ一瞬なので 3600 秒を超える不安もありません。(たぶん。)

% mysql -e 'FLUSH TABLES WITH READ LOCK; SELECT sleep(3600)' &
% sudo zfs snapshot tank/mysql/hoge@20130301
% sudo zfs clone -o mountpoint=/srv/mysql/hoge_20130301 -o readonly=on tank/mysql/hoge@20130301 \
> tank/mysql/hoge/snap/20130301
% kill %1

ついでに readonly=on もつけときましょうか。まあ rollback できるしいいんですけど。

まとめ

以上をまとたものが https://github.com/ichii386/misc/blob/master/myisam_snapshot.bash これ。

これの前バージョンはどっかの業務ですでに使ってるんですけど、まあ動いてそうだしこれもいけるかな。あ、実際に使うときはもっといろんなことをチェックしないといけないので注意。

なお zfs は以下の様な配置になります。

% zfs list -tall -oname,type,mountpoint -r tank
NAME                       TYPE        MOUNTPOINT
tank                       filesystem  none
tank/origin                filesystem  /srv/mysql
tank/origin@20130301       snapshot    -
tank/origin@20130302       snapshot    -
tank/origin/hoge           filesystem  /srv/mysql/hoge
tank/origin/hoge@20130301  snapshot    -
tank/origin/hoge@20130302  snapshot    -
tank/snap                  filesystem  none
tank/snap/hoge             filesystem  none
tank/snap/hoge/20130301    filesystem  /srv/mysql/hoge_20130301
tank/snap/hoge/20130302    filesystem  /srv/mysql/hoge_20130302

mysql から見るとこんなかんじ。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hoge               |
| hoge_20130301      |
| hoge_20130302      |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)

課題

やっぱ innodb でもやりたいですよね。 tablespace の export/import まだ試してないし。

というか Joyent が Percona Server 使ってこういうのやってるぽいし。調べてないんでわかんないけど同じような目的なのかな?