MySQLのバイナリログを活用しリストア＆リカバリで障害時でもDB完全復旧可能な体制を整える。
- 公開日
- 更新日
- カテゴリ：MySQL
- タグ：MySQL
WEB アプリケーション構築の際に、データベースに MySQL を採用するところは多いと思います。オープンソースで誰でも使う事が出来て高性能な RDBMS（リレーショナルデータベース管理システム）ですが、障害への対策をどれだけ行っているかというのは各所差があるようです。
今回は MySQL のバイナリログを利用して最悪の状況にもデータの全てを復旧可能な体制を整えてみたいと思います。
ちなみに、データ復旧にはよく「リストア」と「リカバリ」という２つの言葉が使われますが、その意味は厳密には違います。
- リストア
- バックアップデータから、その時点までを復元する。
- リカバリ
- データリストア ＋ αの操作によって、データを障害が起きた直前まで復元する。
よって
リストア＋リカバリ＝データ復旧
になります。
Contents
ストーリーと仮説
例えば、小～中規模の WEB サービスの場合を考えてみます。
- DB はクラスタリング（冗長化構成）無しの１台構成で、ストレージエンジンは InnoDB のみ。
- １日に１度 mysqldump でバックアップは取っているけれど、それ以外は特に何もしていない。
こんな感じのケースが多いかなと思います。
この場合に、もし DB に障害が発生した場合は前日までのデータ復旧は行えても、当日分のデータに関してはお手上げです。
そんな時 MySQL では、バイナリログを利用する事でこの部分を補う事が出来ます。
なので今回は、これらの仕組みを使ってサービス停止の直前の状態まで DB を復旧させる事を行います。
ちなみに１日に１度のバックアップは mysqldump（-alll-databases）で、切りよく午前 0 時に取っている事とします。
検証環境
尚、今回の検証環境は以下の通りです。
- CentOS 7
- MySQL 5.7
バイナリログ
バイナリログには、create/drop/insert/update/delete などの、いわゆる作成・削除・挿入・更新に係る操作イベントが格納されます。
これらのクエリが走った際には、バイナリログに記録されていきます。
バイナリログ設定
バイナリログを記録できるように設定を行います。 MySQL の設定ファイルに、以下のように記述します。
[mysqld]
# Binary log
server-id=1
log-bin=/var/log/mysql/bin_log/mysql-bin-log
log_bin_index=/var/log/mysql/bin_log/bin.list
max_binlog_size=256M
expire_logs_days=2
# GTID enable
gtid_mode=ON
enforce-gtid-consistency
# Related settings
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sysdate_is_now
log-bin
ログファイルのパスを記述しています。その際に、例えば今回は末尾を「mysql-bin-log 」と記述していますが、こう書く事で
mysql-bin-log.000001
mysql-bin-log.000002
mysql-bin-log.000003
のようにログが生成されていきます。
ちなみに、log-bin=mysql-bin-log と、ベースネームのみを記述すると /var/lib/mysql/ 配下にログファイルが作成されます。
log_bin_index
バイナリログを管理するためのインデックスファイルのパスを記述しています。
max_binlog_size
ログファイルの最大サイズを指定します。ここで指定したサイズを制限値として、新しいファイルが作成されます。例では 500MB で設定しています。尚、設定しない場合は、デフォルトとして 1G でファイルが切られます。
expire_logs_days
ログファイルを保存しておく期間を日単位で指定します。ここでは、２日間にしています。
gtid_mode
GTID(Global Transaction Identifiers) を有効化しています。 GTID を有効にする事で、トランザクション毎に ID を持たせ管理できます。これを仕込んでおくとリカバリが楽になります。
enforce-gtid-consistency
GTID の一貫性（トランザクションセーフなステートメントのみのログを記録する事）を有効にします。
「enforce-gtid-consistency 」という記述は「enforce-gtid-consistency=true 」と同じです。
これを有効にすると、以下の操作は行えません。
- CREATE TABLE ... SELECT ステートメント
- トランザクション内の CREATE TEMPORARY TABLE ステートメント
- トランザクションおよび非トランザクションテーブルの両方を更新するトランザクションまたはステートメント。
[MySQL 5.6 リファレンスマニュアル]グローバルトランザクション ID のオプションと変数
innodb_flush_log_at_trx_commit
トランザクション単位でログを出力するように指定しています。
sync_binlog
バイナリログへの書き込みの設定ですが、ここを 1 に設定する事でクラッシュの際にもバイナリログ内の最大 1 つの文あるいはトランザクションしか失われないので最も安全な設定になります。
sysdate_is_now
SYSDATE の NOW へのエイリアス指定です。
ディレクトリ作成
設定を記述したら、ログを配置するディレクトリを作成します。
# 必要なディレクトリを作成する
[demo@localhost ~]# mkdir -p /var/log/mysql/bin_log
[demo@localhost ~]# chown -R mysql:mysql /var/log/mysql/bin_log
MySQL を再起動します。
service mysqld restart
これでセットアップは完了です。
バイナリログの確認
バイナリログを確認して、どのようにロギングされているかを確認してみます。
ログの確認には mysqlbinlog コマンドで行います。適当に更新系のクエリを発行してみて、以下のようにコマンドを叩くとバイナリログの中を確認できます。
# mysqlbinlog コマンドでバイナリログを確認する
mysqlbinlog /var/log/mysql/bin_log/mysql-bin-log.000001
mysqldump
GTID を仕込んだバイナリログを設定している場合のバックアップは以下のように取ります。
# mysqldump コマンドでバックアップを取る
mysqldump --single-transaction --all-databases --triggers --routines --events --quick -u username -p > /path/to/backup.sql
もしくは、cron で動かすと考えれば --defaults-extra-file あたりはつけましょうか。
# mysqldump コマンドでバックアップを取る（cnffile 指定）
mysqldump --defaults-extra-file=/path/to/.my.db.cnf --single-transaction --all-databases --triggers --routines --events --quick > /path/to/backup.sql
vim .my.db.cnf
##################################################
[client]
user=username
password="password"
host=192.168.XX.XX
##################################################
バックアップを行ったら、そのファイルは S3 など外部に設置するようにします。
リストア/リカバリ
それでは、データ復旧を行ってみます。
まず、最後にバックアップを取った時点では以下のようなテーブルとデータがあるとします。
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
| 120 |
| 130 |
| 140 |
| 150 |
| 160 |
| 170 |
| 180 |
| 190 |
| 200 |
+-------------+
20 rows in set (0.00 sec)
これらのデータは mysqldump でバックアップがとられています。
現在は、そこからさらに 10 レコード追加されている状況です。
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
| 120 |
| 130 |
| 140 |
| 150 |
| 160 |
| 170 |
| 180 |
| 190 |
| 200 |
| 210 |
| 220 |
| 230 |
| 240 |
| 250 |
| 260 |
| 270 |
| 280 |
| 290 |
| 300 |
+-------------+
30 rows in set (0.00 sec)
この状況から復旧を行ってみます。ここからは、既にデータベースに障害が起きデータを参照できない状態とします。
まずは、バイナリログから、適用させるものをファイルへ出力します。
# バイナリログから適用させるものを出力
mysqlbinlog /var/log/mysql/bin_log/mysql-bin-log.000001 > /path/to/restore_bin.sql
次に、ダンプファイルから前日までのデータをリストアします。
# ダンプファイルからリストアを行う
mysql -u root -p bin_test < /path/to/mysql_dump.sql
データを確認します。
# リストアを行った状態
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
| 120 |
| 130 |
| 140 |
| 150 |
| 160 |
| 170 |
| 180 |
| 190 |
| 200 |
+-------------+
20 rows in set (0.00 sec)
まずバックアップ分まではリストア出来ました。
ちなみに、リストアを行う際に
@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
上記エラーが出た場合は GTID_EXECUTED をリセットしてあげる必要があります。
RESET MASTER でログファイルを削除しポジションを初期化します。
# gtid_executed の状態を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+-------------------------------------------+
| Variable_name | Value |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 448d2ef6-3a1d-11e8-a41a-0800272bd935:1-27 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------------------------------------------+
8 rows in set (0.00 sec)
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin-log.000005
Position: 6693
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 448d2ef6-3a1d-11e8-a41a-0800272bd935:1-27
1 row in set (0.00 sec)
# ログファイルを削除しポジションを初期化する
mysql> RESET MASTER;
Query OK, 0 rows affected (0.22 sec)
# 改めて gtid_executed の状態を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin-log.000001
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ちなみにこれを行うとバイナリログがリセットされログファイルも削除されるので注意してください。ログを退避するとかしてから行わないとバイナリログが消えてリカバリが出来なくなります。
そして最後に、バイナリログからリカバリを行います。
# バイナリログを使ってリカバリを行う
mysql -u username -p < /path/to/restore_bin.sql
データを確認してみます。
# バイナリログからリカバリを行った後
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
| 120 |
| 130 |
| 140 |
| 150 |
| 160 |
| 170 |
| 180 |
| 190 |
| 200 |
| 210 |
| 220 |
| 230 |
| 240 |
| 250 |
| 260 |
| 270 |
| 280 |
| 290 |
| 300 |
+-------------+
30 rows in set (0.00 sec)
問題なくデータ復旧が完了しました。
ちなみにバイナリログから直接充てる事も出来ます。
# バイナリログを指定して直接リカバリ
mysqlbinlog /path/to/mysql-bin-log.000001 | mysql -u username -p
バイナリログを外部サーバで管理する
これでバックアップ分だけでなく、バイナリログを使って障害直前のデータまでを復旧できましたが、この場合１つだけ問題があります。それは、バイナリログが本体サーバにしか無い事です。
この場合、1 台しか無い DB サーバが壊れバイナリログを取り出せなかった場合にリカバリを行う事ができません。
なのでバイナリログを外部にも置く事が必要になりますが、障害発生直前までのデータを保持するという事になれば、外部に置くバイナリログもリアルタイムで更新していくという事になるので、都度外部へ置きに行くという流れは難しくなります。
ただし、敢えてリアルタイム性を諦めて、数分～ 1 時間程度の間隔で cron でバイナリログを S3 など外部ストレージに置きに行く（コピー）というのは要件次第ではありかなとは思います。（ただし、負荷やログファイルの最大サイズなど要検討）
という事で、バイナリログを外部サーバにストリーミングで保存する仕組みを構築します。
まず、DB サーバとは別にもう一台サーバを用意します。スペックは高くなくて OK 。これを、BKUP（バックアップ）サーバと呼ぶことにします。
そして、DB サーバの MySQL に、BKUP サーバからの接続が行えるユーザを作成します。
- DB サーバ 192.168.33.97
- BKUP サーバ 192.168.33.98
# mysql ユーザの作成
mysql> GRANT ALL ON *.* TO 'username'@'192.168.33.98' IDENTIFIED BY 'password';
# ユーザ確認
mysql> SELECT user,host FROM mysql.user;
mysqlbinlog_user | 192.168.33.98
シェルスクリプトファイルを作成し、以下コマンドを登録します。
vim binary_log_sync.sh
#######################
#!/bin/bash
mysqlbinlog --defaults-extra-file=/root/mysqlbinlog/.mylogin.cnf --read-from-remote-server --server-id=1 --stop-never --raw --result-file=/root/mysqlbinlog/ mysql-bin-log.000001 &
#######################
mysqlbinlog コマンド使って DB サーバのバイナリログをこちらでもストリーミングで記録していくコマンドになっています。
- --defaults-extra-file
- バックグラウンドで動かす為に、DB の接続情報を外部ファイルで渡しています
- --read-from-remote-server
- 外部サーバへ接続しそのサーバからバイナリログを取得します。
- --server-id
- DB サーバで server-id を 1 に設定したので、ここでも同じ値を設定します。尚、設定しない場合はデフォルトで 65535 がセットされます。
- --stop-never
- ログの書き込みが完了した後、接続を切断せずに保持し新しいイベントがあった際にさらに読み取りを行います。
- --raw
- バイナリのままで取得する指定。
- --result-file
- バイナリログの出力先をディレクトリで指定しています。
- mysql-bin-log.000001
- 読み取るファイルの先頭を指定します。この場合、000001 を含むそれ以降のバイナリログを読み取ります。
- &
- バックグラウンドで実行させる指定。
[MySQL 5.7 Reference Manual]mysqlbinlog - Utility for Processing Binary Log Files
次に、ログイン用の設定ファイルを作成します。
# ログイン用設定ファイル作成
vim .mylogin.cnf
#######################
[client]
user=username
password=passowrd
host=192.168.33.97
#######################
上記２ファイルが用意できたら、シェルを実行します。
# シェルを実行する
sh binary_log_sync.sh
ps lc -p "PID"
#######################
F UID PID PPID PRI NI VSZ RSS WCHAN STAT TTY TIME COMMAND
0 0 3783 1 20 0 25672 2476 sk_wai S pts/0 0:00 mysqlbinlog
#######################
これでリアルタイムでバイナリログを外に取れるようになりました。
あとはこの動作を継続できる流れ（プロセスが生きているかとか切れた場合の再実行など）を作れば、バイナリログの管理も OK と言ったところでしょうか。
# mysqlbinlog のプロセス管理
vim chk_mysqlbinlog.sh
#######################
while true
do
chk=`ps -ef | grep " mysqlbinlog " | \
grep -v grep | wc -l`
if [ $chk != 1 ]; then
# プロセスが死んでいる場合
sh /root/binary_log_sync.sh
echo "再実行しました"
fi
sleep 3
done
#######################
まとめ
以上で作業は完了になります。
アプリケーションのソースコードって大体 Github にあるし、設定ファイルとかきちんととっておけば Web サーバの復旧ってそんなに手間でもなかったりしますが、データベースは常に新しい情報を蓄積し続けていくもので、それは大事な資産なので、誰もが一度は障害直前までの全てのデータを復旧させるにはどうすれば良いのかと考えると思います。
どういう形を採用していくのかはケースバイケースかもしれませんが、こういった方法をしっかり理解しておく事はとても大切だと思います。
最後に。 PHPerkaigi2018 に登壇されていた@yoku0825 さんの講演 を聞き、自分でもまとめてみようと思いこれを書きました。講演での話、スライドなどとても勉強させていただきました。ありがとうございました。
