おっちゃんのエンジニア日記

今までエンジニアとして経験してきたことを書いていくブログ

転ばぬ先のバックアップ!?~MariaDB / MySQL のロールフォーワードリカバリ~

久しぶりに会う人には必ず、「久しぶり!」よりも前に「太った?」が挨拶代わりに言われます。

すがっとです。

 

さてさて、今回はタイトルの通り、DBAのお仕事として忘れてはいけない、

DBのバックアップとリカバリのお話を、MariaDB 10.2を使って説明していきます。

ではでは、いっきまっすよ~~~

 

転ばぬ先のバックアップ!!

もう、もはやネット上では都市伝説になっているのではないか?と思いたいような、

こんな出来事をご存知でしょうか?

 

 いわゆる、「where句無しでupdate / deleteしちゃった」ってお話ですね。

 私もやったことがあります。

 

  • 「それでも僕はやってない」

 開発環境だと思ってテーブル削除したら、実は本番でした、みたいなやつです。

 これは、セキュリティに厳しい昨今の環境であればあまり起きづらいかな?!

 

こんなレアケースと思いたいようなことでも、DBAとしてお仕事していく以上は対応しなければなりません。

そこで必要になってくるのが、「データベースのバックアップ」という事になります。

 

DBのバックアップ

DBのバックアップには大きく分けて2種類あります。

フルバックアップ」と呼ばれるものと、「トランザクションログバックアップ」と呼ばれるものです。

フルバックアップとは

フルバックアップとは、その名の通り、データベースサーバ全体のデータのバックアップを断面的に取得したファイルの集合体です。

用途としては、別サーバーに本番環境を再現させたりとか、あるいはDBサーバが起動できなくなった際に環境を復旧させたりするのに用いたりします。

トランザクションログバックアップとは

トランザクションログバックアップとは、「トランザクション」つまり、日々のデータのやり取りで起こった変更情報を保存しておくもので、時系列にデータを保存してあるものです。

用途は、フルバックアップと組み合わせて特定の時点までデータを戻したりするのに使います。

 

日々の運用として

リストアの方法のお話をする前に、日々のバックアップの運用について軽くお話をします。

先程の2種類のバックアップを日々取得していくのですが、それぞれ、バックアップの周期が違います。

注意!)RDBMSの種類によってはトランザクションログのバックアップの周期が違ったりしますので、これはあくまで一例と思ってください。

種類 周期 データ量
フルバックアップ 日次 多い
トランザクションログバックアップ 一定の時間おき
例)3時間に1回とか
少ない

 

 

トラブル発生!~データを救え~

さて、これらのデータをもとに復旧をしていくのですが、できる限りトラブルが発生した直前までのデータに復旧したいですよね?そこで活躍するのが、ロールフォーワードリカバリです。PostgreSQLとかではPITR(Point In Time Recovery)と呼ばれています。

 

大まかな作業の流れ

大まかに作業を分けると、以下のような手順で復旧していきます。

ちょっと実際にトラブルが起きた時を想定したときの手順です。

  1. フルバックアップ(定期的なバックアップ)
  2. 通常運用
  3. トラブル発生!
  4. 別サーバフルバックアップを用いてデータベースをリストア(復元)する
  5. 4.のDBへバイナリログを用いてデータをリカバリ(復旧)する

おそらく、トラブルが発生した際というのは、データベース全体ではなく、一部のテーブルのデータのみであることが多いと思います。

ですので、トラブルが発生したサーバ上で復元・復旧させるのではなく、別サーバへDBを復旧させ、その中から対象のテーブルだけコピー・転送していく運用が安全だと思います。

 

いざ、やってみよう!

 

検証環境には、pitr_sampleというデータベースがあって、

test_tableというテーブルがあります。

MariaDB [pitr_sample]> desc test_table;
+------------+----------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------------------+----------------+
| col1 | int(11) | NO | PRI | NULL | auto_increment |
| created_at | datetime | YES | | current_timestamp() | |
+------------+----------+------+-----+---------------------+----------------+
2 rows in set (0.00 sec)

データの中身はこんな感じです。

MariaDB [pitr_sample]> select * from test_table;
+------+---------------------+
| col1 | created_at          |
+------+---------------------+
|    1 | 2019-06-29 10:05:05 |
+------+---------------------+
1 row in set (0.00 sec)

この状態で、一度フルバックアップを取得します。

[root@mariadb-spider01 backup]# mysqldump -u root -p --single-transaction --master-data --flush-logs pitr_sample > pitr_sample_full.sql

ここで、いくつかデータを編集していきます。

MariaDB [pitr_sample]> truncate table test_table;
Query OK, 0 rows affected (0.01 sec)

MariaDB [pitr_sample]> select * from test_table;
Empty set (0.00 sec)

MariaDB [pitr_sample]> insert into test_table(col1, created_at) values (2, current_timestamp() );
Query OK, 1 row affected (0.00 sec)

MariaDB [pitr_sample]> insert into test_table(col1, created_at) values (3, current_timestamp() );
Query OK, 1 row affected (0.01 sec)

MariaDB [pitr_sample]> select * from test_table;
+------+---------------------+
| col1 | created_at          |
+------+---------------------+
|    2 | 2019-06-29 10:29:43 |
|    3 | 2019-06-29 10:29:58 |
+------+---------------------+
2 rows in set (0.00 sec)

!!ここからがリストアの開始です。!!

まず、フルバックアップのファイルから、ロールフォーワードリカバリするためのバイナリログファイル・ポイントを確認します。

[root@host-name backup]# cat pitr_sample_full.sql | grep CHANGE
CHANGE MASTER TO MASTER_LOG_FILE='pitr-sample-bin.000003', MASTER_LOG_POS=397;

この例では、バイナリログファイル'pitr-sample-bin.000003'のポイント"397"より前まではフルバックアップでリストアできている、ということになります。

そうしたら、ダンプファイルのリストアを行います。

[root@host-name backup]# mysql -uroot -p pitr_sample < pitr_sample_full.sql
Enter password:

ここで、insertでcol1の値が2まで入った状態まで戻してみたいと思います。
まずは、mysqlbinlogコマンドを使って、insertが行われたログを探します。

[root@host-name backup]# mysqlbinlog --no-defaults --database=pitr_sample /export/mariadb/pitr-sample-bin.000003 | grep -B 5 insert
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
--
/*!*/;
<b># at 580</b>
#190629 10:29:43 server id 130  end_log_pos 739 CRC32 0xf6968d0f        Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1561771783.544877/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
insert into test_table(col1, created_at) values (2, current_timestamp() )
--
BEGIN
/*!*/;
<b># at 812</b>
#190629 10:29:58 server id 130  end_log_pos 971 CRC32 0x32ab1b3f        Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1561771798.160378/*!*/;
insert into test_table(col1, created_at) values (3, current_timestamp() )

ちょっと見づらいですが、上記の「# at 580」というのが、col1に2を入れたinsert文のバイナリログポイントの番号になります。
なので、復旧ポイントはその次の812ということになります。

[root@host-name backup]# mysqlbinlog --database=pitr_sample --disable-log-bin --stop-position=812 /export/mariadb/pitr-sample-bin.000003 | mysql -uroot -p
MariaDB [pitr_sample]> select * from test_table;
+------+---------------------+
| col1 | created_at          |
+------+---------------------+
|    2 | 2019-06-29 10:29:43 |
+------+---------------------+
1 row in set (0.00 sec)

これで、col1に2が入った状態まで復元ができました!

最後に

今回は、ログポイントを使用しての復旧を行いましたが、
時間で復旧する(--start-datatime, --stop-datetime オプションを使用)なんて事も
できたりします。
いろんな復旧方法を日頃から試して、データを復旧させる方法を覚えておけば、
何か大事な作業をするときでも、「いざとなればバックアップから復旧できる」という
自信にも繋がりますし、安心して作業ができると思います。

とはいえ、こんなトラブルに見舞われると大変な思いをすることになるので、
日々の運用からそういったトラブルを未然に防ぐ為の仕組みづくりを心がけましょう!

それでは、また~~~!