3流プログラマのメモ書き

元開発職→社内SE→派遣で営業支援→開発戻り浦島太郎状態の三流プログラマのIT技術メモ書き。 このメモが忘れっぽい自分とググってきた技術者の役に立ってくれれば幸いです。

(Linux)MySQLのバックアップをシェルスクリプトで自動的に行う

MySQLのバックアップを行うには、大きく mysqlhotcopy を使う方法と mysqldump を使う方法があります。

mysqlhotcopy はテーブルをロックしてデータベースのファイルがあるディレクトリをそのままコピーする方法です。

ただし、MyISAM と ARCHIVE テーブルでのみ利用可能なのでInnoDBを使っているテーブルはバックアップ不可能です。

mysqldump はデータベースの内容をSQLファイルに出力する方法です。

mysqldumpによるバックアップはコストも高く、復元にも時間がかかるようなので、InnoDB以外を使っている場合で同じサーバで復元する場合はmysqlhotcopyを使ったほうがいいかもしれません。

ただ、バックアップファイルの扱いやすさはmysqldumpに利があるかと思います。(テキストエディタで中身確認できますしね。)

さて、今回 mysqlhotcopy と mysqldump を両方走らせて cron で自動実行するシェルスクリプトを作ってみました。

7日間の世代管理を行うようにしています。

バックアップはデータベース毎に取ります。

#!/bin/sh

#アーカイブ前の一時ディレクト

BACKDIRTMP=/var/backup/tmp/mysql

# バックアップ先ディレクト

BKDIR=/var/backup/mysql/day

MYPATH=$(cd $(dirname $0);pwd)

#世代管理(何日分残すか)

DAYAGO="7"

# MySQLrootパスワード

ROOTPASS=MySQLのルートユーザのパスワード

#日付・時刻取得し変数に代入

TIMESET=`date +%Y%m%d-%H%I%M`

#バックアップ先ファイル名を設定 設定ファイルにあるファイル名+日付.tar.gzする

BKFILE=${BKDIR}/mysql.$TIMESET.tar.gz

# データベース名取得

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

#一時退避先掃除

rm -rf ${BACKDIRTMP}/*

# データベースごとに一時退避先にバックアップ

for DBNAME in $DBLIST

do

#mysqlhotcopyによるバックアップ

mysqlhotcopy $DBNAME -u root -p $ROOTPASS $BACKDIRTMP

if [ $? = 0 ]; then

logger -t mysqlhotcopy "Backup OK mysqlhotcopy:${DBNAME}"

echo "Backup OK mysqlhotcopy:${DBNAME}"

else

#失敗したらその旨を表示かつ、ログに保存、メール送信

logger -t mysqlhotcopy "Backup NG mysqlhotcopy:${DBNAME}"

echo "Backup NG mysqlhotcopy:${DBNAME}"

${MYPATH}/mailsend.sh "バックアップ失敗通知" "実行バックアップスクリプト: ${0}

バックアップ対象: mysqlhotcopy mysqlDB名:${DBNAME}

バックアップに失敗しました。"

fi

#mysqldumpによるバックアップ

mysqldump -u root -p${ROOTPASS} $DBNAME --opt > ${BACKDIRTMP}/${DBNAME}_dump.sql | logger -t mysqldump

if [ $? = 0 ]; then

logger -t mysqldump "Backup OK mysqldump:${DBNAME}"

echo "Backup OK mysqldump:${DBNAME}"

else

#失敗したらその旨を表示かつ、ログに保存、メール送信

logger -t mysqldump "Backup NG mysqldump:${DBNAME}"

echo "Backup NG mysqldump:${DBNAME}"

${MYPATH}/mailsend.sh "バックアップ失敗通知" "実行バックアップスクリプト: ${0}

バックアップ対象: mysqldump mysqlDB名:${DBNAME}

バックアップに失敗しました。"

fi

done

# 退避先のバックアップを処理日名をつけてアーカイブ圧縮(失敗時はメール送信)

cd $(dirname ${BACKDIRTMP})

tar vzcf $BKFILE $(basename ${BACKDIRTMP}) ||

${MYPATH}/mailsend.sh "バックアップ失敗通知" "実行バックアップスクリプト: ${0}

バックアップ対象: mysql一時ファイルのアーカイブ処理に失敗しました。"

#-------yyyymmdd形式のファイルから古い物を削除-------

# 削除しきい日付(yyyymmdd)を設定。(DAYAGO日前の日付求める)

tday=`date -d "${DAYAGO} day ago" +%Y%m%d`

# 対象Dir配下のファイル名をwhileに渡し「$list」となる

ls $BKDIR|while read list

do

#ファイル名に「数字x8」が含まれている場合、

if [[ $list =~ [0-9]{8} ]] ; then

# $listから、「yyyymmdd」を取り出しepoch時間($fday)にする

fday=`expr $list : "mysql\.\([0-9]\{8\}\).*.tar.gz"`

#ファイル名から取得した「yyyymmdd」が本日の「yyyymmdd」よりも小さい場合

if [ -n "$fday" ] && [ $fday -lt $tday ]; then

# echo する

#echo $list

#削除する

rm -f ${BKDIR}/${list}

fi

fi

done

mysqldump はデータベースを指定しなかったり、--all-databases (-A) オプションを使うとデータベース全体が1ファイルにダンプできるようです。

また、上記スクリプトでは最適化を行う --opt を入れてますが、5.1 では明示的に指定しなくても --opt オプションが付くようですね。

週次、月次等のタイミングでバックアップを取りたければ、(Linux)バックアップシェルスクリプトで世代管理2にあるスクリプトを下記のようにちょこっと修正しやります。

#! /bin/sh

BKUP_DIR=/var/data/backup/day

#このスクリプトのパスを取得

MYPATH=$(cd $(dirname $0);pwd)

#バックアップ先から、指定したリポジトリ名ファイルの最新版を取得

FROM_FILE="`ls -lt ${BKUP_DIR}day/mysql* | head -n 1 | gawk '{print $9}'`"

#週次、月次のバックアップ先と、何日間のデータを残すか設定

case "$1" in

"week" ) TO_DIR="/var/data/backup/week/"

DAYAGO="+22"

;;

"month" ) TO_DIR="/var/data/backup/month/"

DAYAGO="+61"

;;

esac

#古いファイルを削除

find $TO_DIR -mtime $DAYAGO -type f -exec rm -f {} \;

#コピー

cp ${FROM_FILE} ${TO_DIR}

参考:

mysqlhotcopyでバックアップ&復元 - phpspot

MySQLのバックアップをとろう!(同じサーバー内) (プログラマ 福重 伸太朗 ~基本へ帰ろう~)

MySQL のバックアップ手法いろいろ | CollectiveMeta

MySQL :: MySQL 5.1 リファレンスマニュアル :: 7.12 mysqldump - データベースバックアッププログラム

MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.9.7 mysqldump(テーブル構造とデータのダンプ)

mysqldumpでMySQLをバックアップする時のオプション | Nyan-Chew's Digital Life