mysqldump で MySQLバックアップを自動化

気になるところなどがあったので、cronで自動化を目指した手順をまとめ。適切なユーザー作成やロックなど、ちゃんと意識しないとですね。

環境：

• mysql 5.5.40
• 全データーベースを１つのファイルに保管する事を想定

バックアップ準備の手順

MySQLにユーザー作成

バックアップ専用ユーザーを作る事を考えます。root だと何でもできますが、cronで実行させるにはパスワードまでファイルに記載する事になる為、専用ユーザーを作成しました。

ユーザー "backupuser" を作成して権限を付与：

# ユーザー作成
GRANT SELECT, SHOW VIEW ON *.* TO backupuser@localhost IDENTIFIED BY "PASSWORD";

# ユーザー情報の反映
FLUSH PRIVILEGES;

# 確認
SHOW GRANTS FOR backupuser@localhost;

後述する"ロックをかけてdump"をする場合には、作成時の権限を次のように変更

"SELECT, SHOW VIEW" => "SELECT, SHOW VIEW, LOCK TABLES"

www.dbonline.jp

mysqldump を ロックを避けて実施

なにげにつかっていた mysqldump は、オプションを指定によってロックがかかるとの事で、大きめのDBに投げる時には注意が必要ですね。

InnoDBの場合は --single-transaction オプションにより、トランザクション分離レベルを変更してdumpする事が出来るとの事。

mysqldump -u backupuser -pPASSWORD --single-transaction --all-databases > dump_all.sql

よく例などで示されている --lock-all-tables (-x) オプションは、テーブルにロックをかけながらdumpを実施しますので更新が頻繁なシステムなどでは実施タイミングに注意が必要かも。こちらを使う場合には、ユーザーが "LOCK TABLES"権限を持つ事が必要になりますので、作成時に設定ください。

tree-tips.appspot.com

ユーザー情報のバックアップ

MySQLに接続するユーザー情報は mysql というDB内に保存されています。そのため、個別のDBをバックアップした場合にはユーザー情報が足りないという状態に。今回は全DBを1ファイルに落としているので、mysql の中身も含まれますが、もし個別DBをバックアップする場合には、mysql も別途dumpしておく事が必要です。

k-project.ariatown.net

作ったscript

前出の手順にそって、ロックしないでバックアップをするスクリプトを作成しました。

script

#!/bin/sh

FOLDER=backup_mysql
NOW=`date +"%Y%m%d-%H%M"`
DBLIST=`ls -p /var/lib/mysql/ | grep / | tr -d /`

if [ ! -d ${FOLDER} ]
  then
    mkdir ${FOLDER}
fi

echo "===== ${NOW}"
echo ${DBLIST} > ${FOLDER}/dblist.txt

# use --single-transaction instead --lock-all-tables (-x)
echo "===== dump mysql all-db & mysql-user"
mysqldump -u backupuser -pPASSWORD --single-transaction --all-databases > ${FOLDER}/all_db_dump.sql

# make tar.gz file
echo "===== make tgz file"
tar zcvf mysqlbackup_${NOW}.tgz ${FOLDER}