MySQL 5.7 でレプリケーションされているDBのリストア方法について

###前提・実現したいこと
現在、Webサーバが使用するDBが、2台で準同期でレプリケーションされるように設定されています。

レプリケーション環境の構築とDBリストアの経験が無く、調べながらやっているのですが、質問は3点あるのですが、リストア時の注意点等御存じの方がいらっしゃいましたら、ご教授頂きたいです。

【1】DB1台であれば、DBに何か障害があってデータを戻す際には、バックアップのdumpファイルからリストアで良いと考えていますが、レプリケーション環境の場合、注意すべき点はありますでしょうか。
レプリケーション環境であれば、両方のDBサーバが故障した時か、DBに間違った更新を掛けたなどでDBを戻したい時だと考えています。

【2】Master/Slave両方動いている状態で、DBをバックアップから戻す場合、Master側で1日毎に以下のコマンドでdumpを作成したものをMaster側でリストアするだけで良いのでしょうか。
・リストア
/usr/bin/mysql -u root -p < dumpall_yyyyMMdd.sql

※実際はbashスクリプトで実行してdumpを作成した後、別環境にscpで送っています。
/usr/bin/mysqldump --defaults-extra-file=/root/db_backup/.conf -A > dumpall_yyyyMMdd.sql

【3】DBの障害時には以下のパターンと対処を考えていますが、他に考慮すべき点等ありますでしょうか。

■想定障害パターンと対処
DB1：Master
DB2：Slave

パターン1.DB2側の故障（Webサーバ・DB1側の影響はなし）
=>DB2環境を再構築し、準同期レプリケーション設定までを行うことで、レプリケーションされる。

パターン2.DB1側の故障（Webサーバ・DB2側の影響はある）
=>DB2をMasterに昇格し、Webサーバが向いているDBをDB2に変更する。
DB1 mysqlサービス停止
# systemctl stop mysqld

DB2 Slave→Master昇格
mysql> stop slave;
mysql> reset master;
mysql> exit

DB2 設定ファイル変更
# vim /etc/my.cnf
rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_slave_enabled = 1

=>DB1が復旧したら、レプリケーションを再構築するため、

[1]DB2側でDBをロック
mysql> FLUSH TABLES WITH READ LOCK;

[2]show master status;でmaster statusの値を確認
[3]MySQLのスナップショットを取得
# cd /var/lib
# tar cvzf /tmp/mysql-snapshot.tar.gz ./mysql

[4]ロックを解除し、取得したスナップショットをDB1へ転送。
mysql> UNLOCK TABLES;
# scp -i /root/key.pem /tmp/mysql-snapshot.tar.gz root@10.0.1.1:/tmp/

[5]DB1のmysqlを停止し、MySQLのデータファイルを削除
# systemctl stop mysqld
# rm -rf /var/lib/mysql

[6]DB2から転送したスナップショットを解凍
# cd /tmp
# tar xvzf mysql-snapshot.tar.gz
# mv mysql /var/lib/

[7]uuidが記載されているファイルを削除
# rm -f /var/lib/mysql/auto.cnf

[8]DB1の設定ファイル変更（rpl_semi_sync_slave_enabled = 1にする）、MySQL起動
[9]DB1のSlave設定として[2]で確認したmaster statusの値を設定
※この時点でDB1：Slave・DB2：Masterとなる

###該当のソースコード

■Master側：
# vim /etc/my.cnf
##追記
character-set-server = utf8
## Binary Log
log-bin = mysql-bin
# Replication
server-id = 1
slave_compressed_protocol = 1
relay-log = mysql-relay-bin
rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_slave_enabled = 1
log-error=/var/log/mysql/mysqld.log

■Slave側：
# vim /etc/my.cnf
##追記
character-set-server = utf8
# Binary Log
log-bin = mysql-bin
# Replication
server-id = 2
slave_compressed_protocol = 1
relay-log = mysql-relay-bin
#rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 1
log-error=/var/log/mysql/mysqld.log

■非同期レプリケーション設定
Master側：
# mysql -u root -p

レプリケーション用ユーザ作成
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.1.1' IDENTIFIED BY 'pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.1.2' IDENTIFIED BY 'pass';

Slave側：
# mysql -u root -p

レプリケーション用ユーザ作成
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.1.1' IDENTIFIED BY 'pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.1.2' IDENTIFIED BY 'pass';

■準同期レプリケーション設定
Master側：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

Slave側：
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled = 1;

###試したこと
非同期レプリケーション設定後に、レプリケーション動作確認として以下のことを実施、動作を確認しています。

1 テスト用database作成
 Master側で作成
 create database test01;
 create table test01.personal(id int, name varchar(20));
 insert into test01.personal(id, name) values(1, 'testuser1');
2 Slave側で確認
 select * from test01.personal;

3 Master側でdatabase削除
 mysql> drop database test01;

4 Slave側でdatabase確認 test01が無いことを確認
 mysql> show databases;

Slave→Master昇格(■想定障害パターンと対処のパターン2と同様の操作を実施)

###補足情報(言語/FW/ツール等のバージョンなど)
CentOS7.3
MySQL 5.7

行動規範の内容に同意します

回答1件

ベストアンサー

ちょっと長くなってしまいましたが、ご容赦ください。

準同期レプリケーションはMySQL 5.5から導入された機能です。
MySQLの場合ですと、通常は非同期レプリケーションになります。
＃SemisyncはSemisynchronous Replicationの略です。

準同期レプリケーションは、I/Oスレッドを同期し、SQLスレッドは非同期で実行されます。
またSLAVEへのバイナリログ転送が保証されています。

MASTERはトランザクションコミットを実行するスレッドを立てると
自分へのcommitを実行後にブロック状態にします。
ブロック状態でSLAVEに通知を行いSLAVEからの通知
（そのトランザクションの全てのイベントを受取完了 or タイムアウト発生を通知）
を受け取るまで待機します。

MASTERのブロック中は、トランザクションを実行したセッションに戻りません。
ブロックが終了すると、MASTERはそのセッションに戻り、次のステートメント実行に移ります。

SLAVEからのトランザクションの受取通知がない状態でタイムアウトが発生した場合、
MASTERは非同期レプリケーションになります。
＃SLAVEから受取完了の通知が来たら、準同期レプリケーションに戻ります。

したがって、巨大なトランザクションを実行したり、SLAVEの負荷が高くなってしまうとDB遅延が発生します。
五月雨式にトランザクションを流しても同様に、徐々にSLAVEの更新が徐々に遅くなっていくことになります。
つまり、SLAVE側の障害がMASTERに影響してしまう可能性があるということです。

これはテストしてもわからないようなことですので、
実運用し始めてパフォーマンスチューニングをしていくことになります。

質問の回答をしていきますと

【1】

DB1台であれば、DBに何か障害があってデータを戻す際には、バックアップのdumpファイルからリストアで良いと考えていますが、レプリケーション環境の場合、注意すべき点はありますでしょうか。
レプリケーション環境であれば、両方のDBサーバが故障した時か、DBに間違った更新を掛けたなどでDBを戻したい時だと考えています。

想定タイミングは色々あると思います。
特にフルダンプリストアすると先程述べたようにSLAVEに負荷をかけてしまうので、なるべく回避すべきです。
テーブルダンプでいいならテーブルリストアで直す方法もあります。
障害時に限らず、本番データでないと発生しないような事象を調査する場合にも、
検証用環境に対して本番データのリストアかけることも必要になってきます。

【2】

Master/Slave両方動いている状態で、DBをバックアップから戻す場合、
Master側で1日毎に以下のコマンドでdumpを作成したものをMaster側でリストアするだけで良いのでしょうか。

どの復元ポイントに戻すかは、必ず決めておくべきですが、
場合によって柔軟に対応せざるを得ない場合があります。

例えばゲームでせっかくレアアイテム出たのに
データが１日前（レアアイテム出る前）まで巻き戻ってしまう場合、
どう思いますか？ということです。

【3】

DBの障害時には以下のパターンと対処を考えていますが、他に考慮すべき点等ありますでしょうか。

まず障害時に何を復旧の必要要件とするのかを事前に決めておかなければなりません。
・可用性なのか
・データの保全性なのか

どんな構成でも、システムの完全な無停止はできません。
必ず復帰するまでに数秒～かかってしまいます。

それを踏まえた上で、どのようなパターンの時にどういう対処が
適確であるのかを話し合いで決めておきます。

それによりフォールトトレラントシステムを用いるのか、
フェイルオーバーさせるのか、スイッチオーバーにするのか
手段を決めていきます。

以下は、MASTERは更新、SLAVEは参照として構築すると思われるので
その想定として記載します。

パターン1. DB2側の故障（Webサーバ・DB1側の影響はなし）

即座にやらないといけないことは、全てのDBの向き先をMASTERに向けないといけません。
でないとアプリケーションがデータ参照ができなくなってしまいます。
その後、SLAVEの構築をします。
その際、ログポジションを誤らないように注意が必要です。

パターン2. DB1側の故障（Webサーバ・DB2側の影響はある）

MASTERが壊れた際には、障害程度によりさらに細分化されます。

１．MASTERがブロック中に障害が発生し、OSログインすら出来ない場合
２．MASTERがアイドル中に障害が発生し、OSログインすら出来ない場合
３．MASTERは生きているがブロック中に障害が発生し、DBのみ死んでしまった場合
４．MASTERは生きているがアイドル中に障害が発生し、DBのみ死んでしまった場合

どれもSLAVEを信用するしかない状態であると言えますが、
【１or３】と【２or４】で復元ポイントが違いますし
【１or２】と【３or４】で作業は異なります。

またSLAVEをMASTERに昇格した場合も、そのままMASTERとして利用し続けることも
視野に入れておかねばなりません。

またテクニックとして、MASTER-SLAVE構成を作った場合、
設定値に関しては、MASTER昇格のことを考え、MASTERもSLAVEも同じ設定ファイルをコピーし、
コメントアウトあるなしで設定した方が良いかと思います。
＃MASTERの設定値とSLAVEの設定値を固めてグループにしておく

MASTER用ファイル、SLAVE用ファイルを置いておき、リネームして有効無効にする方法もあります。

投稿2017/05/05 13:47

編集2017/05/05 13:48