Mitzyuki's Blog

MySQL データベースを Amazon S3 にバックアップするスクリプト

自宅サーバで運用している MySQL データベースのデータを Amazon S3 にバックアップするためのスクリプトを作成したので公開。

今回作成したスクリプトでは、 ローカルディレクトリに YYYYMMDD 型式で 7 世代分のデータをフルダンプし、 毎週日曜日の深夜に Amason S3 に当日分のバックアップを転送している。
mysqldump で取得したバックアップデータに VIEW 情報が含まれていると データベースのレストア時にエラーが発生する可能性がある様なので、 mysqldump 時に VIEW 定義は除外してダンプを取得し VIEW 情報のみを別途ダンプする様にしている。

Amason S3 への転送は S3 tools が公開している s3cmd を利用しているので、 s3cmd と python のインストールは必須。
当然 Amazon S3 も利用できる様にしておく必要がある。

以下のスクリプトは MySQL の接続用パスワードや Amazon S3 のアクセスキーなどを平文で保存しているので、 ファイルのアクセス権限には十分に注意が必要である。
バックアップは root 権限で実行する様にし ファイルの権限は 500 に設定しておく事が望ましい。

  1#!/bin/sh
  2
  3# MySQL 用の設定
  4database="データベース名"
  5user="-uユーザ -pパスワード"
  6
  7# Amazon S3 用の設定
  8backet="backet 名"
  9PASSPHRASE="パスフレーズ"
 10AWS_ACCESS_KEY_ID="アクセスキーID"
 11AWS_SECRET_ACCESS_KEY="SECRET アクセスキー"
 12
 13# ローカルにバックアップする世代数
 14max=7
 15# Amazon S3 に転送する曜日指定 (0: 日曜 … 6: 土曜)
 16dayofweek=0
 17
 18# コマンド定義
 19ls="/bin/ls -1"
 20wc="/usr/bin/wc -l"
 21rm="/bin/rm -r"
 22head="/usr/bin/head -1"
 23mkdir="/bin/mkdir -p"
 24
 25# ファイルとディレクトリ定義
 26logfile="/var/log/`basename ${0}`.log"          # ログファイル名
 27backupdir="/var/db/backup"                      # バックアップ先ディレクトリ
 28target="`date '+%Y/%m/%d'`"                     # Amason S3 でバックアップを格納するディレクトリ名
 29targetdir="${backupdir}/`date '+%Y%m%d'`"       # ローカルでバックアップを格納するディレクトリ名
 30backupfile="${targetdir}/all_dump.sql"          # バックアップファイル名
 31createview="${targetdir}/create_view.sql"
 32createtable="${targetdir}/create_table.sql"
 33
 34# MySQL コマンドの定義
 35mysql="/usr/bin/mysql ${socket} ${user} -B -N -s -r "
 36mysqldump="/usr/bin/mysqldump ${user}
 37        -R --hex-blob --single-transaction"
 38sql="select TABLE_NAME from information_schema.TABLES
 39        where TABLE_SCHEMA = '${database}' and TABLE_TYPE = 'VIEW';"
 40
 41# s3cmd の定義
 42s3cmd="/usr/bin/s3cmd put -rr"
 43export PASSPHRASE AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY
 44
 45# ロギング開始
 46umask 700
 47exec > ${logfile} 2>&1
 48set -x
 49
 50# ローカルディレクトリの準備
 51# 最大世代数以上の場合は一番古い世代を削除する
 52test `${ls} ${backupdir} | ${wc}` -ge ${max} &&
 53    ${rm} ${backupdir}/`${ls} ${backupdir} | ${head}`
 54${mkdir} ${targetdir}
 55
 56echo "DB backup: start: `date`"
 57
 58# ダンプから除外するために VIEW を取得し create view 文をダンプする
 59for i in `${mysql} ${database} -e "${sql}"`
 60do
 61    view="${view} --ignore-table=${database}.${i}"
 62    ${mysql} ${database} -e "show create view ${i}"
 63done > ${createview}
 64
 65# 全データダンプ
 66${mysqldump} ${view} ${database} > ${backupfile}
 67
 68# create table 文のダンプ
 69${mysqldump} --no-data ${view} ${database} > ${createtable}
 70
 71# 指定された曜日の場合は Amazon S3 に転送する
 72# 以下の 1行をコメントとすると 毎日 Amazon S3 に転送する
 73test `date '+%w'` -eq ${dayofweek} &&
 74    ${s3cmd} ${targetdir}/ s3://${backet}/${target}/
 75
 76echo "DB backup: end: `date`"
    

awk による正規表現にマッチした部分の抜き出し

入力行に "running"、"stopping"、 "stopped"、"pendding" のいずれかの単語が含まれる場合に その単語を抽出したい場合。
awk (1) の sub()、gsub() 組み込み関数では 後方参照ができないので マッチしたパターンに応じた変換を一度に行う事ができず、 それぞれのパターンにマッチングさせて処理をするしかない。

    awk '
        /^INSTANCE.*running/{
            print running;
        }
        /^INSTANCE.*stopping/{
            print stopping;
        }
        /^INSTANCE.*stopped/{
            print stopped;
        }
        /^INSTANCE.*pending/{
            print pending;
        }
    '
    

しかしこれではあまりにも悲しいのでマニュアルをじっくりと調べた所、 組み込み関数 match() で正規表現にマッチ処理を行うと 組み込み変数 RSTART にマッチした文字の位置、 RLENGTH にマッチした文字列の長さが設定されるので 組み込み関数 substr() と併用する事で マッチした部分を抽出する事ができる事に気がついた。

    awk '
        /^INSTANCE/{
            if(match($0, /running|stopping|stopped|pending/))
                print substr($0, RSTART, RLENGTH);
            if(match($0, /ec2.*\.amazonaws\.com/))
                print substr($0, RSTART, RLENGTH);
        }
    '
    

awk (1) で正規表現によるパターン抽出を行う場合に便利だと思います。

文字列はあくまでも例であって特別な意味を持っていません。
万が一類似する文字列があった場合でもそれは偶然の一致です。 ぐ、偶然ですからねっ(汗

PATH の扱い

unix 系 OS では ${PATH} 環境変数を参照して コマンドの検索パスを指定する事ができる。
通常はログイン時にシステムデフォルトのコマンド検索パスが ${PATH} に自動で設定されているが、 ユーザ毎のシェルのログインファイル (.profile 等）で 自分用のコマンド検索パスを追加設定する事が多い。
ここで気をつけるべきは ${PATH} 中のヌルパス ("::") は カレントディレクトリ (".") と同等に扱われてしまう事だ。

例えば

PATH="${PATH}:${mypath}:${HOME}/bin"

    

と設定した時に ${mypath} が設定されていない場合

PATH="${PATH}::${HOME}/bin"

    

と展開されてしまうので、 結果としてカレントディレクトリが ${PATH} に含まれてしまう。
この挙動は POSIX (IEEE Std 1003.1, 2004 Edition) の 8.3 Other Environment Variables) にも明記されている。

The prefixes shall be separated by a colon ( ':' ). When a non-zero-length prefix is applied to this filename, a slash shall be inserted between the prefix and the filename. A zero-length prefix is a legacy feature that indicates the current working directory.

過去互換性の様だが非常に迷惑な仕様である。

カレントディレクトリをコマンド検索パスに含める事は どんな場合でもセキュリティ上のリスクを伴うので避けるべきでなので、 PATH を設定する場合は注意が必要だ。

例えば .profile で ${PATH} を設定する場合、 以下の様にする事は防衛手段として悪く無い考えだと思う。

PATH="`echo ${PATH}:${mypath}:${HOME}/bin | sed 's/::*/:/g'`"

    

バーベキュー

金曜日の夕方過ぎに自宅から連絡があり、 唐突に土曜日は朝から道志川界隈にバーベキューに行くことに。
実はご近所さんにご一緒にと誘って頂いたのだが、 娘もとても仲良くしているお友達なので勿論行ってきました。
しかも場所の選定、バーベキューの道具（グリルやらテーブルやら）から 食材に至るまで完パケで用意して頂けたので感謝感激です。

仕事に行くよりも断然早く、朝 6 時に起きて 7 時出発。 車 2 台で向かったのは道志川沿いの「道志の森キャンプ場」。 ちゃんとしたキャンプ場ですがバーベキューなどのデイキャンプも可の様で、 しっかりテントを貼ったキャンパー風の一行から、 我々の様なバーベキューを楽しみに来た家族連れまで賑わっていました。
川の水はとっても冷たかったのですが、 娘達と一緒に脚まで水に浸かって目一杯楽しんだ後は 美味しいバーベキューに舌鼓の乱れ打ち。

ちゃんと火おこしや調理など頑張って働きましたよ!

河原でのバーベキューなんて 10 年以上ぶりの事なので、 娘よりも親の方がはしゃいでしまったけど、 暑すぎず寒くもなくとても楽しい一日でした。

お誘い頂いて本当にありがとございました♪