MysqlとPostgreSQLのPITRによるバックアップ&リカバリの違い(まとめ)

Pocket

こんにちは、落合です。
この記事は、TECHSCORE Advent Calendar の22日目の記事です。

2014年も終わろうとしていますね。
PostgreSQL8.x系は、今年EOLを迎えました。いままでありがとうございました。
(ですので9.x系で実施した記事内容となっています。)
そして、いまどきはMysqlというよりMariaDBにしたほうが刺さるんですかね?
そもそも、AWSでポチッとバックアップ&リカバリなんてしちゃうので、あんまり興味がなかったりでしょうか?(笑)

さて、、、記事タイトルのことをダラダラと・・・

オンラインバックアップのおさらい

まず、PostgreSQLとMysqlでみなさんが最初に実施したであろうバックアップ、

全データベースのオンラインバックアップをおさらいをしてみようとおもいます。

○PostgreSQL編

簡単ですね。

  • | gzip >
    • サイズが小さいことは何かとお得なので、gzipしておきます。
  • ほかのオプションが無いが・・・?
    • PostgreSQLの場合は、自動的に、読み取り一貫性機能が働きます。
    • バックアップ取得中もデータベースへのアクセス(読み取り/書き込み)は抑制されません。

○Mysql編

ストレージエンジンにInnoDBを利用していることが前提
(MyISAM等は使えないオプションを含んでいます)

  • --all-databases
    • その名の通り、すべてのデータベースをdumpします。
  • --single-transaction
    • PostgreSQLと同じように読み取り一貫性機能が効くようになり、ロックもされません。とにかく付けておいて損はないです。
    • dumpする前にBEGIN (transaction開始) SQLを使ってdumpするので、 ロックされることなくBEGIN 発行時点のdataをdumpできます。
  • --events
    • mysqlバージョン5.1.8からmysqlデータベースにeventsテーブルが追加されたことが原因で、
      --all-databasesをつけると、
      このテーブルだけが除外されるのでwarningがでます。これを抑止するためにつけています。
  • | gzip >
    • サイズ縮小

ポイント・イン・タイム・リカバリ (PITR)

おさらいはいかがだったでしょうか?・・・さて、ここからが本題です。

○基本的なこと

  • そもそもPITRって?
    • 指定した時間に戻すことです。
    • または、障害が起きる直前の状態まで戻すことです。
    • Oracleでいうところの、完全/不完全リカバリ のことです。
  • どうやって戻ってるの?
    • 下記の絵の通り、
      「 ベースバックアップ + 差分バックアップ 」
      この二つのものを利用して戻します。

 

PITRの概念

PITR:ベースバックアップの取得

○PostgreSQL編

  • 開始宣言。これを実行すると以降の更新情報はディスクに反映されなくなります。
    (メモリ上で頑張っています。)
    これでデータベースの静止点が取れます。

  • OSコマンドでベースバックアップを取得します。
    • postgresqlの起動プロセスファイル(postmaster.pid)やWALログ(pg_xlog)は不要です。
    • ここでは、/postgresql_basebackup/配下に保存しています。

  • 終了宣言。 ベースバックアップ取得中に停止されていたデータベースの更新をディスクへ反映して通常の状態に戻ります。

※レプリケーション環境の場合は、専用ツール pg_basebackup がバージョン9.1から利用可能なのでそちらを使ってください。

○Mysql編

  • ベースバックアップ取得方法

  • --flush-logs
    • mysqldumpと同時に、bin-logをローテーションしてくれるので、リカバリがしやすくなります。
  •  --master-data=2
    • msyqldumpで取得後、bin-logのどのファイルの何行目から復旧させればいいかdumpファイルにコメントとして書き込んでくれるすぐれたオプションです。
    • ちなみに、レプリケーション構成を組んでなくても指定可能なので、PITRする人は積極的に利用すべきだと思います。

あれ、これだけ!? オンラインバックアップで使ったdumpと同じ? 

そうです、オプションは少し増えた(例だと二つ)もののMysqlだとこれをベースバックアップとして利用可能なのです。

PITR:差分バックアップの取得

○PostgreSQL編

  • アーカイブログを吐く設定(postgresql.confの編集)

  • %p:アーカイブするファイルのパス名に置換されます。
  • %f:ファイル名部分のみに置換されます。
  • WALを退避します。
    • 最新のトランザクションログなので、完全リカバリを行いたい方は退避しておいてください。
    • 最初から、別ディスクにpg_xlogを吐き出すようにしておくのも有効な手段です。

○Mysql編

  • バイナリログを吐く設定(my.cnfの編集)
    Mysqlではアーカイブログと呼ぶものではなく、バイナリログがその役割を担います。

PITR:リカバリ

さていよいよリカバリです。

○PostgreSQL編

ゴミデータがあっては困るので、再インストールしたばかりのPostgreSQLがあって起動していない状態等を作っておいてください。

  • 何はともあれ、まずは、ベースバックアップの戻し

  • アーカイブ(WAL)ログを充てるための準備
    • recovery.confにアーカイブ(WAL)ログが存在している場所を指定します。

※ recovery_target_time の行を無くせば、WALが持っている最新の時刻まで戻ります。

  • WALを充てるための準備

  • PostgreSQLの起動
    • これで差分バックアップのデータ(アーカイブログおよび、WAL)まで適用されていきます。

○Mysql編

ゴミデータがあっては困るので、再インストールしたばかりのMysqlがあって起動した状態等を作っておいてください。

  • 何はともあれ、まずは、ベースバックアップの戻し

  • ダンプ取得以降~復旧したい時間までに書き出されたバイナリログを確認

  • ダンプ開始時点のバイナリログ名と位置を確認

  •  上記で確認したバイナリログ名と位置を指定して、ダンプ取得以降~復旧したい時間までのデータを書き出す
  • ダンプ取得時に --flush-logs オプションを指定しているので開始位置は、先頭になっているので、
    ずばり position で指定しています。

  • 例)ダンプ取得以降~復旧したい時間までに書き出されたバイナリログが複数の場合

※ --stop-datetime の指定がなければ、指定したバイナリログファイルが持っている最新の時刻までが対象になります。

    • 差分バックアップデータの適用

まとめ の まとめ

いかがだったでしょうか? PostgreSQLとMysqlとでPITRの比較ができましたでしょうか?

そして今回の記事のまとめです。

手順 PostgreSQL Mysql
オンラインバックアップコマンド pg_dumpall(または、pg_dump) mysqldump
PITRのためのベースバックアップ取得方法 1.psql -c "SELECT pg_start_backup(now()::text)"
2.OSコマンドによる該当領域バックアップ
3.psql -c "select pg_stop_backup()"
mysqldump
PITRで戻すために必要な差分バックアップファイルの種類 1.アーカイブ(WAL)ログ
2.WALログ

※Oracleでいう不完全リカバリの場合は不要な場合がある。
(つまり、復旧させる時刻次第で使われなかったりする)

バイナリログのみ
PITRのための差分バックアップ取得方法 1.アーカイブログを吐く設定(postgresql.conf)
2.OSコマンドによるWALログの退避
バイナリログを吐く設定(my.cnf)
PITRで戻すためのアプリケーションの状態 停止 起動
PITRで戻すためのベースバックアップ適用方法 OSコマンドによる退避済みベースバックアップの戻し作業 gunzip --stdout [mysqldumpで取得したファイル] | mysql -u root
(または、mysql -u root < [mysqldumpで取得したファイル])
PITRで戻すための差分バックアップ適用方法 1.退避済みアーカイブログの場所指定(recovery.conf)
2.退避済みWALログのOSコマンドによる戻し作業
3.PostgreSQLの起動
1.mysqlbinlogで、バイナリログをテキスト化
2.mysql -u root < 上記テキスト化したファイル

ベースバックアップ+差分バックアップの仕組みを理解できれば、応用が利くようになります。
たとえば、Master/Slaveのレプリケーション構成で、HDD丸ごとクラッシュした場合、壊れたほうのDBはいずれ修復しないといけないですよね。
そんなとき、ベースバックアップを手動で戻してあげて、差分バックアップ部分については、レプリケーションで追いつかせる。
なんてことがスムーズに実行できるかとおもいます。

Oraclerな私でしたが、
Mysqlにはストレージエンジンだったり、この記事のようにオンラインバックアップがそのままベースバックアップとして使えたり驚きがありますね。
PostgreSQLはDDLにトランザクション張れる(ロールバックできる!)とか、SQLがTAB補完できたりとか驚かせられますね。

みなさんも、いろいろ比較してみてはいかがでしょうか?

Pocket

Comments are closed, but you can leave a trackback: Trackback URL.