RitoLabo

MySQLのバイナリログを活用しリストア&リカバリで障害時でもDB完全復旧可能な体制を整える。

  • 公開:
  • 更新:
  • カテゴリ: Database RDBMS MySQL
  • タグ: MySQL,Database,5.6,5.7

WEBアプリケーション構築の際に、データベースにMySQLを採用するところは多いと思います。オープンソースで誰でも使う事が出来て高性能なRDBMS(リレーショナルデータベース管理システム)ですが、障害への対策をどれだけ行っているかというのは各所差があるようです。

今回はMySQLのバイナリログを利用して最悪の状況にもデータの全てを復旧可能な体制を整えてみたいと思います。

ちなみに、データ復旧にはよく「リストア」と「リカバリ」という2つの言葉が使われますが、その意味は厳密には違います。

リストア
バックアップデータから、その時点までを復元する。
リカバリ
データリストア+αの操作によって、データを障害が起きた直前まで復元する。

よって
リストア+リカバリ=データ復旧
になります。

アジェンダ
  1. ストーリーと仮説
  2. 検証環境
  3. バイナリログ
  4. バイナリログ設定
  5. バイナリログの確認
  6. mysqldump
  7. リストア/リカバリ
  8. バイナリログを外部サーバで管理する

ストーリーと仮説

例えば、小~中規模のWEBサービスの場合を考えてみます。

  • DBはクラスタリング(冗長化構成)無しの1台構成で、ストレージエンジンはInnoDBのみ。
  • 1日に1度mysqldumpでバックアップは取っているけれど、それ以外は特に何もしていない。

こんな感じのケースが多いかなと思います。

この場合に、もしDBに障害が発生した場合は前日までのデータ復旧は行えても、当日分のデータに関してはお手上げです。

そんな時MySQLでは、バイナリログを利用する事でこの部分を補う事が出来ます。

なので今回は、これらの仕組みを使ってサービス停止の直前の状態までDBを復旧させる事を行います。

ちなみに1日に1度のバックアップは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
ログファイルを保存しておく期間を日単位で指定します。ここでは、2日間にしています。
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つだけ問題があります。それは、バイナリログが本体サーバにしか無い事です。

この場合、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
#######################

上記2ファイルが用意できたら、シェルを実行します。

# シェルを実行する
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さんの講演を聞き、自分でもまとめてみようと思いこれを書きました。講演での話、スライドなどとても勉強させていただきました。ありがとうございました。