転ばぬ先のバックアップ!?~MariaDB / MySQL のロールフォーワードリカバリ~
久しぶりに会う人には必ず、「久しぶり!」よりも前に「太った?」が挨拶代わりに言われます。
すがっとです。
さてさて、今回はタイトルの通り、DBAのお仕事として忘れてはいけない、
DBのバックアップとリカバリのお話を、MariaDB 10.2を使って説明していきます。
ではでは、いっきまっすよ~~~
転ばぬ先のバックアップ!!
もう、もはやネット上では都市伝説になっているのではないか?と思いたいような、
こんな出来事をご存知でしょうか?
- 漢SQL
いわゆる、「where句無しでupdate / deleteしちゃった」ってお話ですね。
私もやったことがあります。
- 「それでも僕はやってない」
開発環境だと思ってテーブル削除したら、実は本番でした、みたいなやつです。
これは、セキュリティに厳しい昨今の環境であればあまり起きづらいかな?!
こんなレアケースと思いたいようなことでも、DBAとしてお仕事していく以上は対応しなければなりません。
そこで必要になってくるのが、「データベースのバックアップ」という事になります。
DBのバックアップ
DBのバックアップには大きく分けて2種類あります。
日々の運用として
リストアの方法のお話をする前に、日々のバックアップの運用について軽くお話をします。
先程の2種類のバックアップを日々取得していくのですが、それぞれ、バックアップの周期が違います。
注意!)RDBMSの種類によってはトランザクションログのバックアップの周期が違ったりしますので、これはあくまで一例と思ってください。
種類 | 周期 | データ量 |
フルバックアップ | 日次 | 多い |
トランザクションログバックアップ | 一定の時間おき 例)3時間に1回とか |
少ない |
トラブル発生!~データを救え~
さて、これらのデータをもとに復旧をしていくのですが、できる限りトラブルが発生した直前までのデータに復旧したいですよね?そこで活躍するのが、ロールフォーワードリカバリです。PostgreSQLとかではPITR(Point In Time Recovery)と呼ばれています。
大まかな作業の流れ
大まかに作業を分けると、以下のような手順で復旧していきます。
ちょっと実際にトラブルが起きた時を想定したときの手順です。
おそらく、トラブルが発生した際というのは、データベース全体ではなく、一部のテーブルのデータのみであることが多いと思います。
ですので、トラブルが発生したサーバ上で復元・復旧させるのではなく、別サーバへ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 オプションを使用)なんて事も
できたりします。
いろんな復旧方法を日頃から試して、データを復旧させる方法を覚えておけば、
何か大事な作業をするときでも、「いざとなればバックアップから復旧できる」という
自信にも繋がりますし、安心して作業ができると思います。
とはいえ、こんなトラブルに見舞われると大変な思いをすることになるので、
日々の運用からそういったトラブルを未然に防ぐ為の仕組みづくりを心がけましょう!
それでは、また~~~!