今回はMySQLのロールフォワードの紹介記事です。
課金や顧客の財産がデータベースに書き込まれていくサービスを運用する場合、ロールフォワードは必須の技術になります。ロールフォワードする場面を想像しただけで、おなかが痛くなりますね･･････＾＾；
ロールフォワード
データベースに障害が発生したときに、記録してあるチェックポイントのデータを再現した上で、ログファイルに残っているチェックポイント後の処理を再現し、障害直前の状態にまで戻すこと。障害直前のデータが再現される。主に、ディスクの損傷などの物理的障害に対して用いられる。
@see e-words
もくじ
事前準備
- 定期的なDBバックアップ、
- バイナリログを出力をさせておく
上記の条件を整えたMySQLの運用を行っておきます。
障害発生。ロールフォワードが必要な事態に！
- 水を飲みます。精神集中。冷静に、冷静に。
- １秒でも早く復旧ではなく、１～２時間以内に着実に復旧する事を考える。
- まずWEBサーバをメンテナンスモードにしてユーザにWEB上で告知するか、止める。とにかく更新がかからないようにする。
- 障害発生時点のDBデータをフルダンプする。
- バイナリログの退避
- 最新のチェックポイントデータからマスターのログファイルとポジションを確認する。
- バイナリログから問題のクエリを確認する。
- バイナリログから問題のクエリを除いた復旧クエリを抽出する×n
- 最新のチェックポイントからリストア ※ロールバック
- 復旧クエリからリストア ※ロールフォワード
- データを確認
まず、必ず予めバイナリ出力をオンにした運用を行います。バイナリログがないと復旧が出来ないからです。
# vi /etc/my.cnf log-bin = mysql-bin binlog_format = mixedbinlog_format = mixed
# service mysqld restart
ロールフォワード記事の為のテーブル作り。
# mysql -u root
mysql> create database mysqltest_db;
mysql> use mysqltest_db;
mysql> create table staff(
-> id int auto_increment not null,
-> name varchar(50),
-> regdate timestamp not null default current_timestamp(),
-> index(id)
-> );
データを入れます。
mysql> insert into staff(name) values('tanaka');
mysql> insert into staff(name) values('suzuki');
mysql> insert into staff(name) values('takashi');
mysql> insert into staff(name) values('yuu');
mysql> insert into staff(name) values('mitani');
mysql> insert into staff(name) values('kato');
mysql> insert into staff(name) values('miyuki');
mysql> insert into staff(name) values('erika');
mysql> SELECT * FROM staff; +----+---------+---------------------+ | id | name | regdate | +----+---------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 4 | yuu | 2016-11-24 14:55:51 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | +----+---------+---------------------+ 8 rows in set (0.00 sec)
mysql> exit Bye
この段階でチェックポイントとなるダンプデータを作成します。
# mysqldump mysqltest_db --single-transaction --master-data=2 --flush-logs > /root/mysqltest_db.`date "+%Y%m%d_%H%M%S"`.sql
作成されていることを確認。
# ls -1 /root/ mysqltest_db.20161124_141357.sql</pre>
# mysql -u root
mysql> use mysqltest_db;
mysql> SELECT * FROM staff; +----+---------+---------------------+ | id | name | regdate | +----+---------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 4 | yuu | 2016-11-24 14:55:51 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | +----+---------+---------------------+ 8 rows in set (0.00 sec)
続けてデータを入れていきます。
mysql> insert into staff(name) values('help_zawa');
mysql> insert into staff(name) values('help_yoko');
mysql> insert into staff(name) values('help_t');
途中経過を確認します。
mysql> SELECT * FROM staff; +----+-----------+---------------------+ | id | name | regdate | +----+-----------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 4 | yuu | 2016-11-24 14:55:51 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | | 9 | help_zawa | 2016-11-24 14:57:19 | | 10 | help_yoko | 2016-11-24 14:57:24 | | 11 | help_t | 2016-11-24 14:57:28 | +----+-----------+---------------------+ 11 rows in set (0.00 sec)
さて、続けてデータを入れていきます。
mysql> DELETE FROM staff WHERE name = "yuu";
mysql> insert into staff(name) values('help_matuzaka');
mysql> SELECT * FROM staff; +----+---------------+---------------------+ | id | name | regdate | +----+---------------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | | 9 | help_zawa | 2016-11-24 14:57:19 | | 10 | help_yoko | 2016-11-24 14:57:24 | | 11 | help_t | 2016-11-24 14:57:28 | | 12 | help_matuzaka | 2016-11-24 14:57:51 | +----+---------------+---------------------+ 11 rows in set (0.00 sec) mysql> exit
mysql> DELETE FROM staff WHERE name = “yuu”;
ここで誤ったクエリによるデータ消失のトラブルに気付く！！
ここでメンテナンスモードにします。
データの更新を防ぐためWEBサーバを止めるか、メンテナンスモードにしてユーザに告知して下さい。
# /etc/init.d/nginx stop
バイナリデータを退避させます。
# cd /var/lib/mysql
# cp -f mysql-bin.* /home/
障害発生時のダンプデータを取得します。
# mysqldump mysqltest_db --single-transaction --master-data=2 --flush-logs > /root/mysqltest_db.rollback_before_`date "+%Y%m%d_%H%M%S"`.sql
# ls -1 /root/ mysqltest_db.20161124_145637.sql mysqltest_db.rollback_before_20161124_145822.sql
最新チェックポイントの中身、マスターログとポジションを確認します。
# head -n 100 /root/mysqltest_db.20161124_145637.sql -- MySQL dump 10.13 Distrib 5.6.34, for Linux (x86_64) -- -- Host: localhost Database: mysqltest_db -- ------------------------------------------------------ -- Server version 5.6.34-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=120; -- -- Table structure for table `staff` -- 略
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000015′, MASTER_LOG_POS=120;
# cd /home # ls -1 mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.000007 mysql-bin.000008 mysql-bin.000009 mysql-bin.000010 mysql-bin.000011 mysql-bin.000012 mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 mysql-bin.000016 mysql-bin.index
dumpの後はmysql-bin.000015から見ればいいことがわかります。mysql-bin.00001～mysql-bin.000014は無視できますね。
(※障害時のdump取得時の–flush-logsオプションを入れているので、mysql-bin.000016も無視できる。)
mysql-bin.000015から問題のクエリを探します。
# mysqlbinlog --verbose --database="mysqltest_db" --start-position=120 /var/lib/mysql/mysql-bin.000015 | grep 'DELETE FROM staff WHERE name = "yuu"' -B 5 -A 5 BEGIN /*!*/; # at 1085 #161124 14:57:45 server id 1 end_log_pos 1211 CRC32 0xad6d8e7d Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1479967065/*!*/; DELETE FROM staff WHERE name = "yuu" /*!*/; # at 1211 #161124 14:57:45 server id 1 end_log_pos 1242 CRC32 0x6ef091e2 Xid = 205 COMMIT/*!*/; # at 1242
ポジション1085と1211の間に問題のクエリを見つけました。
バイナリログより復旧クエリその１を作成します。
# mysqlbinlog --database="mysqltest_db" --start-position=120 --stop-position=1085 /var/lib/mysql/mysql-bin.000015 > /root/recovery_1.sql
更にバイナリログの最後のポジションを確認します。
# mysqlbinlog /var/lib/mysql/mysql-bin.000015 | tail -n 30
#161124 14:57:45 server id 1 end_log_pos 1211 CRC32 0xad6d8e7d Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1479967065/*!*/;
DELETE FROM staff WHERE name = "yuu"
/*!*/;
# at 1211
#161124 14:57:45 server id 1 end_log_pos 1242 CRC32 0x6ef091e2 Xid = 205
COMMIT/*!*/;
# at 1242
#161124 14:57:51 server id 1 end_log_pos 1337 CRC32 0x0b956346 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1479967071/*!*/;
BEGIN
/*!*/;
# at 1337
# at 1369
#161124 14:57:51 server id 1 end_log_pos 1369 CRC32 0x8ae16ff1 Intvar
SET INSERT_ID=12/*!*/;
#161124 14:57:51 server id 1 end_log_pos 1506 CRC32 0x3e0bd555 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1479967071/*!*/;
insert into staff(name) values('help_matuzaka')
/*!*/;
# at 1506
#161124 14:57:51 server id 1 end_log_pos 1537 CRC32 0xd73a611f Xid = 206
COMMIT/*!*/;
# at 1537
#161124 14:58:22 server id 1 end_log_pos 1584 CRC32 0xbd7b065b Rotate to mysql-bin.000016 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
最後のポジションは1537であることがわかります。
バイナリログより復旧クエリその2を作成します。
# mysqlbinlog --database="mysqltest_db" --start-position=1211 --stop-position=1537 /var/lib/mysql/mysql-bin.000015 > /root/recovery_2.sql
最新チェックポイントからのロールバック
ここで最新のチェックポイントを使ったリストアを行います。
# mysql -u root mysqltest_db < /root/mysqltest_db.20161124_145637.sql
# mysql -u root
mysql> SELECT * FROM staff; +----+---------+---------------------+ | id | name | regdate | +----+---------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 4 | yuu | 2016-11-24 14:55:51 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | +----+---------+---------------------+ 8 rows in set (0.00 sec)
復旧クエリによるロールフォワード
復旧クエリ１と２を使い更にリストアします。
# mysql -u root mysqltest_db < recovery_1.sql
# mysql -u root mysqltest_db < recovery_2.sql
確認します。
# mysql -u root
mysql> SELECT * FROM staff; +----+---------------+---------------------+ | id | name | regdate | +----+---------------+---------------------+ | 1 | tanaka | 2016-11-24 14:55:33 | | 2 | suzuki | 2016-11-24 14:55:39 | | 3 | takashi | 2016-11-24 14:55:44 | | 4 | yuu | 2016-11-24 14:55:51 | | 5 | mitani | 2016-11-24 14:55:57 | | 6 | kato | 2016-11-24 14:56:02 | | 7 | miyuki | 2016-11-24 14:56:07 | | 8 | erika | 2016-11-24 14:56:12 | | 9 | help_zawa | 2016-11-24 14:57:19 | | 10 | help_yoko | 2016-11-24 14:57:24 | | 11 | help_t | 2016-11-24 14:57:28 | | 12 | help_matuzaka | 2016-11-24 14:57:51 | +----+---------------+---------------------+ 12 rows in set (0.00 sec)
4番のレコードも消えずに入っています。
これで問題のクエリを避けつつ、データを正常に復旧させることが出来ました。WEBサーバを動かしてサービスを再開させます。
# /etc/init.d/nginx start
ロールフォワードはある程度練習して手順を頭に入れておかないと、障害が発生した緊急時に迅速な対応が出来ないので慣れておく必要があります。
関連記事 - More from my site -
Percona XtraBackup ロールフォワード
ディスク書き込み速度計測
Laravel+Docker+GitHub+CircleCIでユニットテスト
Gmail活用術![[Solved] Let’s Encrypt 更新に失敗する](https://www.yuulinux.tokyo/contents/wp-content/uploads/2021/08/20210809_linux1-150x150.jpg)
[Solved] Let’s Encrypt 更新に失敗する![【Soved】Nginx [crit] 20655#20655: *56 open() “/var/log/nginx/xxxx.access.log” failed (13: Permission denied) while logging request](https://www.yuulinux.tokyo/contents/wp-content/uploads/2020/06/linux_2021_2-150x150.jpg)
【Soved】Nginx [crit] 20655#20655: *56 open() “/var/log/nginx/xxxx.access.log” failed (13: Permission denied) while logging request- Mac PHP7.3から7.4にアップデート
Synology 自動ブロックのアカウントを解除
AWS 1台か複数台か？ スケジュール機能を使ったオートスケール
複利
PHP Tencent Cloud SDKでAPIを叩く
【工事中】Laravel クエリビルダ ->where()まとめ
videoタグで動画サムネイルがSafariで表示されない HTML/CSS
Managerクラス クソコード動画から学ぶ
Amazonおすすめ
iPad 9世代 2021年最新作
iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜