Mitzyuki's Blog :: MySQL データベースを Amazon S3 にバックアップするスクリプト

Jul 31, 2013

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

Posted at 00:00 in Computer | コメント/トラックバック(0) | 編集

Edit this entry...

wikieditish message: Ready to edit this entry.

Title:

Body:

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


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


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


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


	<source lang='sh'>
#!/bin/sh

# MySQL 用の設定
database="\<u\>データベース名\</u\>"
user="-u\<u\>ユーザ\</u\> -p\<u\>パスワード\</u\>"

# Amazon S3 用の設定
backet="\<u\>backet 名\</u\>"
PASSPHRASE="\<u\>パスフレーズ\</u\>"
AWS_ACCESS_KEY_ID="\<u\>アクセスキーID\</u\>"
AWS_SECRET_ACCESS_KEY="\<u\>SECRET アクセスキー\</u\>"

# ローカルにバックアップする世代数
max=7
# Amazon S3 に転送する曜日指定 (0: 日曜 … 6: 土曜)
dayofweek=0

# コマンド定義
ls="/bin/ls -1"
wc="/usr/bin/wc -l"
rm="/bin/rm -r"
head="/usr/bin/head -1"
mkdir="/bin/mkdir -p"

# ファイルとディレクトリ定義
logfile="/var/log/`basename ${0}`.log"			# ログファイル名
backupdir="/var/db/backup"						# バックアップ先ディレクトリ
target="`date '+%Y/%m/%d'`"						# Amason S3 でバックアップを格納するディレクトリ名
targetdir="${backupdir}/`date '+%Y%m%d'`"		# ローカルでバックアップを格納するディレクトリ名
backupfile="${targetdir}/all_dump.sql"			# バックアップファイル名
createview="${targetdir}/create_view.sql"
createtable="${targetdir}/create_table.sql"

# MySQL コマンドの定義
mysql="/usr/bin/mysql ${socket} ${user} -B -N -s -r "
mysqldump="/usr/bin/mysqldump ${user}
        -R --hex-blob --single-transaction"
sql="select TABLE_NAME from information_schema.TABLES
        where TABLE_SCHEMA = '${database}' and TABLE_TYPE = 'VIEW';"

# s3cmd の定義
s3cmd="/usr/bin/s3cmd put -rr"
export PASSPHRASE AWS_ACCESS_KEY_ID AWS_SECRET_ACCESS_KEY

# ロギング開始
umask 700
exec > ${logfile} 2>&1
set -x

# ローカルディレクトリの準備
# 最大世代数以上の場合は一番古い世代を削除する
test `${ls} ${backupdir} | ${wc}` -ge ${max} &&
    ${rm} ${backupdir}/`${ls} ${backupdir} | ${head}`
${mkdir} ${targetdir}

echo "DB backup: start: `date`"

# ダンプから除外するために VIEW を取得し create view 文をダンプする
for i in `${mysql} ${database} -e "${sql}"`
do
    view="${view} --ignore-table=${database}.${i}"
    ${mysql} ${database} -e "show create view ${i}"
done > ${createview}

# 全データダンプ
${mysqldump} ${view} ${database} > ${backupfile}

# create table 文のダンプ
${mysqldump} --no-data ${view} ${database} > ${createtable}

# 指定された曜日の場合は Amazon S3 に転送する
# 以下の 1行をコメントとすると 毎日 Amazon S3 に転送する
test `date '+%w'` -eq ${dayofweek} &&
    ${s3cmd} ${targetdir}/ s3://${backet}/${target}/

echo "DB backup: end: `date`"
	</source>

Excerpt:

自宅サーバで運用している 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`"

Ping URLs:

Password:

A quick preview will be rendered here when you click "Preview" button.