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`"