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

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

転ばぬ先のバックアップ!?~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 オプションを使用)なんて事も
できたりします。
いろんな復旧方法を日頃から試して、データを復旧させる方法を覚えておけば、
何か大事な作業をするときでも、「いざとなればバックアップから復旧できる」という
自信にも繋がりますし、安心して作業ができると思います。

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

それでは、また~~~!

とあるDBAの日常 ~ここが違うよ!DBA~

ブログとしては、ご無沙汰しております。

すがっとです。

 

皆さん、お元気ですか?

お花見の季節になりましたねぇ。

 

さて、今回はタイトルの通り、私がDBAとしてお仕事をしていたとき、

どんな日常を送っていたのか?という内容です。

DBAになる前に自分が抱いていたDBAに対するイメージとのギャップや、

これからDBAを目指したい・興味はあるけどどんな仕事?というのを

身近な目線でお伝えしていけたらと思いますのでお付き合いくださいませ。

まずはじめに

過去に何度か、こんな事を言われたことがあります。

DBA is GOD(DBAは神である)!!

今だから言いましょう。

正直、全然そんな事無いです。

 なんたらマスターみたいな資格が無くてもできますし、

大変なことを積極的にやってるわけでもないです。

障害を積極的に対応してくれてる?いいえ?

仕方なくやってますよ!!だって自分が担当してるんだもん!

みなさんが自分の担当領域に問題があったら必死に対応してるのと

まったく一緒です。

とある一日の流れ

10:00 出社

 のどかに出社をし、監視ツールのモニターを眺め、前日のDBサーバーのリソース状況をチェック。

この時に見ているポイントはこんな感じ:

 ・CPU使用率の変化

 ・ディスクI/Oアクセスの様子

 ・ディスク使用量の変化

 ・メモリ使用状況

 

 今回は↑の事については端折りますが、要は「いつもと変わりないよね?」という確認をしています。

10:30 ~ 13:00くらいまで

 自分の担当しているサーバーの安全点検が終わったら、もうすぐお昼だな、と思いながらプロジェクトの残タスクを確認したり、問い合わせや相談事が来ていないか確認します。

 だいたいこれくらいの時間になると相談が入ってきたりします。

お昼食べて~18:00くらいまで

 案件の仕事を集中的にやりつつ、問い合わせを引きずっていたらその日のうちに解決できるかどうかを判断。

~定時まで

 明日以降の予定を立てつつ、残タスクをチェックしながら後片付け。

 

みたいな感じで一日が終わっていきます。

 

あれ?イメージと違くない?

ええ。僕も最初はそう思いました。ではでは、そのギャップについて解説していきます。

DBの事、めっちゃ詳しいんでしょ?

いいえ?わからないことはググりますよ。

公式のドキュメント見て、わからない単語があればさらにググって。

実際に動かしてみて。開発しているときとまったく変わらないですよ。

夜中でもいつでも対応してくれてるでしょ?

それは皆さん自分が担当してる物に問題があったら責任持って対応するのと同じです。

僕も夜中に対応なんてしたくないですから、そうならないような仕組み作りをするのも仕事の一つです。

毎日忙しそうじゃない?遅くまで仕事してるでしょ?

僕と一緒に仕事をしたことがある人ならわかると思います。

基本的に定時ダッシュです。

残業なんてトラブルが起きた時くらいです。

だって、早く帰りたいもん。

まとめ

最後に僕が言いたいのは、DBAって大変そうなイメージあるけれど、担当している領域が違うだけで

やってることはそんなに皆さんと変わらないです。

データベースって、今後も必要な領域で、もっともっと需要が広がって欲しい。

そんなに難しく構えないで、新しい言語を触ってみる、みたいなノリで触れてみて欲しい。

そうやって、DBAをやってみたい!って思い始める人が増えてくれるといいな、

なんて事を思いつつ、のんびりと毎日を過ごしているすがっとさんです。

 

ではまた!

たまたま見たWAFのログで震えたお話(MS SQLServer)

はじめまして!

いちおう上場企業などでDBAをやっていた すがっと と申します。

はてなデビューしました。

これからはこちらの方で自分がDBAとして経験したことや、

データベースの技術検証した話などを書いていこうと想います。

 

きっかけ

 先日、元同僚とご飯を食べていた時に、「そういや、こんな事があってな」と

この話をしたところ、「それ、LTで話してくださいよー!」と言われまして…。

人前で話をするのが苦手なすがっとさん、とりあえずブログに逃げました。

 

今回は、過去に経験したWAFのログの中でも震えたSQLインジェクションのログから

セキュリティについて考えたお話です。

こんな構成

兎にも角にも構成が見えないとイメージもつかないと思ったので、

当時運用していたサービスの構成をざっくりと絵にしたものがこちらです。

ちなみに、使用しているRDBMSはMS SQLServer 2008R2でした。

 f:id:tetsuwrx:20180825103836p:plain

見つけた!SQLインジェクション

日々の運用ではSQLインジェクションが飛んでくることはそこまで珍しいものではなく、

WAF(Web Application Firewall)で弾かれたものがメールで通知されてくるので何気なく眺めていたところ…

よく見かけるような「' and 1 = 1;'」 みたいなものではなく、

とてつもなく長いSQLが書かれていてメールのログでは途切れてしまう程のものが通知されてきました。

これは何かあってからではマズいということで調べていったところ…

データの抜き取りとはこういうことだったのか

実際の中身は細かく覚えていないのでダイジェストで書くと…

1. xp_cmdshellを使ってWindowsファイヤウォールを停止

2. xp_cmdshellを使って11433ポート(使われていなさそうなポート番号)を開放

3. select文でサーバー名やIPアドレスを取得した結果を、リモートクエリ(openquery)を使って

 外部のDBサーバー(恐らくデータ収集専用DBサーバ)へデータを保存

イメージ的にはこんな感じですね↓

f:id:tetsuwrx:20180825105717p:plain

何が怖かったか

こんな手の凝ったSQLは見たことなかったし、WEBページにデータを表示させたり

DBサーバをクラッシュさせるという目的ではなく、本気でデータを抜きにかかっている事を目的として実行しようとしていたところが怖かったです。

これがもし通っていたら次は何をされるかわからないですからね。

改めて感じたこと

1. xp_cmdshellは危険

SQLServer界隈では有名な話ではあるのですが、SQLの中でOSに対してコマンドを実行できるというものなので、

WEBサービスとして使用しているDBサーバなどでは当然のことながら無効化しておきましょうというものなのですが、

今回の1件で無効化しておいて良かった、と改めて思いました。

2. アプリケーションから接続するユーザーには適切な権限設定を

アプリケーションを動かす上ではinformation_schemaやシステム管理用DBの情報は必要ないはずですので、

見れないようにしておいても実害は無いと思います。

3. openqueryはいろんな意味でやめてほしい

リモートクエリはデータのフローが複雑になるのでできれば使用してほしくないし、

使用しないなら実行できないようにしたいところですね。

 

まとめ

SQLインジェクションって、実際に見てみると思いもよらないクエリが書いてあったりして、

「これを防ぐためには…」というような考えで見ていくと結構勉強になったりする事もあるので、

「なんかいつもと違う!」という第六感を働かせるためにも、その「いつも」がどういうログが飛んでくるか見ておくと、

あなたもニュータイプなDBAになれるかもしれません ( m9`・ω・´)

 

せっかく記事に起したものでもあるので、どこかでお話できたら良いなー、なんて…(ボソボソ

その時は、震えながら話していると思うので温かい目で見守ってあげてください。