MySQLバックアップのベストプラクティスを求めて #MySQL

2016年に似つかわくないかも知れないけど、RDBのバックアップについて。

しっかりレプリケーションとってるから大丈夫?！

いや、ちょっとまってね。レプリケーションってすぐにマスターからスレーブに反映されちゃうじゃない？DBのトラブルってハードウェア障害じゃなくて、結局開発者が間違って削除しちゃったりすることなのよ。レプリケーションはエンジニアのミスも綺麗に伝播される。

mysqldumpはインフラ担当がやってくれてるから大丈夫？！

「助けて！インフラ担当！」ってお願いすると、「ホイキタァ！」と、mysqldump後のファイルをくれる。ありがてーと思いながら、いざ、gzファイルを展開すると、展開すると、展開すると、、、おわんねー。でかすぎる！

スナップショットとってるから大丈夫？！

今時のクラウドだとスナップショットが簡単にとれる。だからすぐに戻せる。いや、だけど、ごめん全部戻したいんじゃなくて、僕がミスってtrancateした200行のマスタを戻したいだけなんだ。

救って欲しいのは「俺のミス」

間違いなくリカバリたいのはテーブル単位。

ということでこの要件でつくてみよう

・テーブル単位でダンプ&gzip
・テーブル単位で差分があれば、それをS3に持っていく
・履歴管理はS3まかせにしたい

できた!

テーブルの一覧は、infomation_schemaを参照
テーブルの更新状況は、infomation_schemaはあまり正確じゃないので、カラムの、updated_atの最大値と、レコード数をSQL発行して更新の有無をみる
updated_atがないテーブルはレコード数の変化のみで差分をみる
件数が多すぎてもupdated_atの最大値取るのに重くなるので、updated_atをみるのは件数が少ないテーブルのみ
最終更新日とレコード数は、S3のオブジェクトのメタ情報に保存して、次のバックアップの時には、変更があるテーブルだけ対象にする

ソース

データベースの接続情報は適宜書き換えてね。

buckup.rb

require 'active_record'
require 'aws-sdk'
require "tempfile"
require "pp"
require_relative 'env.rb' # AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY AWS_REGION を外部ファイルで設定

ActiveRecord::Base.establish_connection(
  :adapter  => 'mysql2',
  :database => 'information_schema',
  :host     => '127.0.0.1',
  :username => 'root',
  :password => ''
)
ActiveRecord::Base.logger = Logger.new(STDOUT)
class Table < ActiveRecord::Base
end

def get_table_lists
  # システム関連スキーマ以外で実体のあるテーブルを取得（VIEWを除外）
  targets =  Table.where.not(TABLE_SCHEMA: ["sys","PERFORMANCE_SCHEMA","mysql","information_schema"]).where(TABLE_TYPE:"BASE TABLE")
  #
  targets.map {|row|
    begin
     row_count = ActiveRecord::Base.connection.select_value("select count(1) from `#{row.TABLE_SCHEMA}`.`#{row.TABLE_NAME}`")
    rescue
     row_count = 0
    end    
    begin
      # レコード数の少ないテーブルはマスタ系が多いので、そっちは更新日でも差分チェック
      if 0 < row_count && row_count < 10000
        last_updated = ActiveRecord::Base.connection.select_value("select DATE_FORMAT(max(updated_at),'%Y-%m-%d %H:%i:%s') from `#{row.TABLE_SCHEMA}`.`#{row.TABLE_NAME}`")
      else
        last_updated = nil
      end
    rescue
     last_updated = nil
    end
    {
      schema:row.TABLE_SCHEMA,
      name:row.TABLE_NAME,
      last_updated:last_updated.to_s,
      row_count:row_count.to_s
    }
  }
end

def upload_s3(table_lists)
  s3 = Aws::S3::Resource.new
  bucket = s3.bucket('ほげほげバケット')
  hostname = `hostname`.strip

  table_lists.each {|table|
    puts "#{table[:schema]} #{table[:name]} is exporting.."
    o = bucket.object("#{hostname}/#{table[:schema]}/#{table[:name]}.sql.gz")
    if o.exists? && o.metadata["row_count"] == table[:row_count] &&  o.metadata["last_updated"] == table[:last_updated] 
      puts "no change "
      next
    end
    puts "#{table[:schema]} #{table[:name]} is uploading.."
    tf = Tempfile.open(['backup',''])
    system("mysqldump --single-transaction -uroot #{table[:schema]} #{table[:name]} | gzip > #{tf.path}")  
    o.upload_file(tf.path,{metadata:{row_count:table[:row_count], last_updated:table[:last_updated]}})
    tf.close!
  }
end

# main
upload_s3(get_table_lists)

気遣いポイント

バケットの先のディレクトリ名は、マシン名から取得しているから、開発マシンとかでも気軽にアップしちゃえばいいよって仕組みにしてる。
mysqldumpはロックしない優しい設計
ダンプ先のファイルはテンポラリファイルを毎回クリアしているのでそんなにディスクを使わないようにしてる

S3の履歴管理って独特

よくある履歴管理って「７世代でローテーション」みたいなのだけれど、S3の場合は数は無制限で、削除した（上書きした前バージョン）の保存期間が「ライフサイクル」として定義できるのみ。
今回作ったスクリプトでは、差分がない限りS3にもアップしないので、変化のないテーブルは最終的には、最期の１世代だけが残ることになる。（まぁそれでもいいか）

おまけ:S3にアップロードする前にやること

バックアップようにバケットを作ってそこだけがいじれるキーを作りたい。その際の作業ステップがこれ。

バケットを作る
ユーザを作る
ユーザに対して、APIKEY,SECRETをつくる
独自のポリシーを作る
ユーザに対してポリシーをアタッチする
バケットに対してバケットポリシーテンプレートを貼り付ける

すごい面倒。この一連の流れをいっぱつで作って、仲間内でキーを共有すれば、高速安価なクラウドストレージとして使えるのになー。