がじぇ

お金と家電とプログラミングのブログ

DB(Mysql)のデータをバックアップしてS3に保存するスクリプト

テクノロジー

こんにちわ

がじぇったー (@hackmylife7) | Twitter


です。


仕事でDBのバックアップの仕組を作る機会がありましたので、
使ったスクリプトを記載いたします。
DBごとにdumpファイルを作成し、それをS3に自動でアップロードするようなスクリプトです。




事前準備(AWS)

AWSでs3バケットを事前に作成します。

Userを作成し、A/S Keyを控えます。S3のバケットにPushできる権限(Policy)をつけます。


私の場合はPolicyを以下の通り作成しました。大きすぎる権限を割り当てるとセキュリティリスクがあるので、権限はList,Put,Getに絞っています。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:PutObject",
                "s3:GetObject"
            ],
            "Resource": [
                "arn:aws:s3:::hoge-hoge-bucket-name/*"
            ]
        }
    ]
}

その後、IAMでシステムユーザを作成します。「プログラムによるアクセス」のみチェックし、マネジメントコンソールへのログイン権限は与える必要はありません。
作成したポリシーをUserに割り当てます。

作成したらAccess KeyとSecret Keyを控えます。

バックアップ対象サーバでの準備

~/.aws/credentialsにAccess KeyとSecret Keyが記載されたファイルを配置します。

# mkdir ~/.aws
# touch ~/.aws/credentials
# chmod 400 ~/.aws/credentials
# vi ~/.aws/credentials

# paste bellow

[default]
aws_access_key_id     = "*******************"
aws_secret_access_key = "*******************"
region                = ap-northeast-1

aws-cliのインストール

以下を参照してcliをインストールしてください。
S3にdumpファイルを保存する際に使います。
AWS CLI のインストール - AWS Command Line Interface

スクリプトの配置

rootのホームディレクトリに下記のスクリプトを配置します。

#!/bin/sh

# 他のユーザからバックアップを読み込めないようにする(所有者のみファイルにアクセス可)
umask 077

# バックアップファイルの保存期間（日）
period=3
# バックアップファイルを保存するディレクトリ
BACKUPDIR='/root/backup/mysql'
PASSWORD='DB_PASSWORD'
# ファイル名を定義(日付)
FILENAME=`date '+%Y%m%d'`

BUCKET_NAME='dumpファイルを保存するバケット名'

# 他のユーザからバックアップを読み込めないようにする(所有者のみファイルにアクセス可)
umask 077

# バックアップファイルの保存期間（日）
period=3
# バックアップファイルを保存するディレクトリ
BACKUPDIR='/root/backup/mysql'
PASSWORD=$(openssl rsautl -decrypt -inkey ~/.ssh/id_rsa -in pass.rsa)
# ファイル名を定義(日付)
FILENAME=`date '+%Y%m%d'`

BUCKET_NAME='stg-data-dbbackup-edu'

DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# BACKUPDIRがなければ作成する
if [ ! -e "$BACKUPDIR" ]
    then
    mkdir -p "$BACKUPDIR"
fi

for dbname in $DBLIST
do
  [ $dbname = "performance_schema" ] && continue
  table_count=`mysql -u root -p$PASSWORD -B -e "show tables" $dbname | wc -l`
  [ $table_count -ne 0 ] && mysqldump -u root --password=$PASSWORD --add-drop-table $dbname > $BACKUPDIR'/'$dbname'_'$FILENAME'.sql'
done

# backup/mysql/配下のsqlファイルをgzで固める
find $BACKUPDIR/*$FILENAME* | xargs tar zcvf $BACKUPDIR/$FILENAME.tar.gz

# s3にuploadする
aws s3 cp $BACKUPDIR/$FILENAME.tar.gz s3://$BUCKET_NAME/$FILENAME.tar.gz
'

DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# BACKUPDIRがなければ作成する
if [ ! -e "$BACKUPDIR" ]
    then
    mkdir -p "$BACKUPDIR"
fi

for dbname in $DBLIST
do
  [ $dbname = "performance_schema" ] && continue
  table_count=`mysql -u root -p$PASSWORD -B -e "show tables" $dbname | wc -l`
  [ $table_count -ne 0 ] && mysqldump -u root --password=$PASSWORD --add-drop-table $dbname > $BACKUPDIR'/'$dbname'_'$FILENAME'.sql'
done

# backup/mysql/配下のsqlファイルをgzで固める
find $BACKUPDIR/*$FILENAME* | xargs tar zcvf $BACKUPDIR/$FILENAME.tar.gz

# s3にuploadする
aws s3 cp $BACKUPDIR/$FILENAME.tar.gz s3://$BUCKET_NAME/$FILENAME.tar.gz

実行して見ましょう。
以下のように処理が成功すればOKです。

# chmod 700 ./mysql_dump.sh
# ./mysql_dump.sh
tar: Removing leading `/' from member names
/root/backup/mysql/mysql_20191013.sql
/root/backup/mysql/DB_NAME_20191013.sql
upload: backup/mysql/20191013.tar.gz to s3://BUKET_NAME/20191013.tar.gz


パスワードをスクリプトに直書きしたくない場合は、下記を参照してパスワードを暗号化しましょう。
auewe.hatenablog.com

# ssh-keygen
# echo 'DB_PASSWORD' | openssl rsautl -encrypt -inkey ~/.ssh/id_rsa > pass.rsa
# vi mysql_dump.sh

以下スクリプトを貼り付けます

#!/bin/sh

# 他のユーザからバックアップを読み込めないようにする(所有者のみファイルにアクセス可)
umask 077

# バックアップファイルの保存期間（日）
period=3
# バックアップファイルを保存するディレクトリ
BACKUPDIR='/root/backup/mysql'
PASSWORD=$(openssl rsautl -decrypt -inkey ~/.ssh/id_rsa -in pass.rsa)
# ファイル名を定義(日付)
FILENAME=`date '+%Y%m%d'`

BUCKET_NAME='dumpファイルを保存するバケット名'

DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /`

# BACKUPDIRがなければ作成する
if [ ! -e "$BACKUPDIR" ]
    then
    mkdir -p "$BACKUPDIR"
fi

for dbname in $DBLIST
do
  [ $dbname = "performance_schema" ] && continue
  table_count=`mysql -u root -p$PASSWORD -B -e "show tables" $dbname | wc -l`
  [ $table_count -ne 0 ] && mysqldump -u root --password=$PASSWORD --add-drop-table $dbname > $BACKUPDIR'/'$dbname'_'$FILENAME'.sql'
done

# backup/mysql/配下のsqlファイルをgzで固める
find $BACKUPDIR/*$FILENAME* | xargs tar zcvf $BACKUPDIR/$FILENAME.tar.gz

# s3にuploadする
aws s3 cp $BACKUPDIR/$FILENAME.tar.gz s3://$BUCKET_NAME/$FILENAME.tar.gz

Cronの設定

Crontabを編集して定期実行されるようにしましょう
下記の例だと毎朝3時に実行されます

# crontab -e
0 3 * * * ./mysql_dump.sh