MySQL

MySQLロールフォワード

| by 優

 

今回はMySQLのロールフォワードの紹介記事です。

 

課金や顧客の財産がデータベースに書き込まれていくサービスを運用する場合、ロールフォワードは必須の技術になります。ロールフォワードする場面を想像しただけで、おなかが痛くなりますね･･････＾＾；

ロールフォワード

 

データベースに障害が発生したときに、記録してあるチェックポイントのデータを再現した上で、ログファイルに残っているチェックポイント後の処理を再現し、障害直前の状態にまで戻すこと。障害直前のデータが再現される。主に、ディスクの損傷などの物理的障害に対して用いられる。

 

@see e-words

 

事前準備

  1. 定期的なDBバックアップ、
  2. バイナリログを出力をさせておく

上記の条件を整えたMySQLの運用を行っておきます。

 

障害発生。ロールフォワードが必要な事態に！

  1. 水を飲みます。精神集中。冷静に、冷静に。
  2. １秒でも早く復旧ではなく、１～２時間以内に着実に復旧する事を考える。
  3. まずWEBサーバをメンテナンスモードにしてユーザにWEB上で告知するか、止める。とにかく更新がかからないようにする。
  4. 障害発生時点のDBデータをフルダンプする。
  5. バイナリログの退避
  6. 最新のチェックポイントデータからマスターのログファイルとポジションを確認する。
  7. バイナリログから問題のクエリを確認する。
  8. バイナリログから問題のクエリを除いた復旧クエリを抽出する×n
  9. 最新のチェックポイントからリストア　※ロールバック
  10. 復旧クエリからリストア　※ロールフォワード
  11. データを確認

 

 

 

まず、必ず予めバイナリ出力をオンにした運用を行います。バイナリログがないと復旧が出来ないからです。

# 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 -

Amazonおすすめ

iPad 9世代 2021年最新作

iPad 9世代出たから買い替え。安いぞ！🐱 初めてならiPad。Kindleを外で見るならiPad mini。ほとんどの人には通常のiPadをおすすめします＞＜

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。（スパム対策）