7.3.1 バックアップポリシーの確立

役に立つように、バックアップは定期的にスケジュールする必要があります。完全バックアップ (特定の時点でのデータのスナップショット) は、MySQL でいくつかのツールを使用して実行できます。たとえば、MySQL Enterprise Backup は、InnoDB データファイルのバックアップ時にオーバーヘッドを最小にし、中断を防ぐ最適化を伴うインスタンス全体の物理バックアップを実行できます。mysqldump はオンライン論理バックアップを提供します。この説明では mysqldump を使用します。

負荷が少ない日曜日の午後 1 時に、次のコマンドを使用して、すべてのデータベースのすべての InnoDB テーブルの完全バックアップを作成するとします。

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

mysqldump によって生成される結果の .sql ファイルには、あとでダンプしたテーブルのリロードに使用できる SQL INSERT ステートメントのセットが含まれます。

このバックアップ操作では、ダンプの最初ですべてのテーブルに対するグローバル読み取りロックを取得します (FLUSH TABLES WITH READ LOCK を使用して)。このロックが取得されるとすぐに、バイナリログの座標が読み取られ、ロックが解除されます。FLUSH ステートメントが発行されたときに長い更新ステートメントが実行中の場合、バックアップ操作はそれらのステートメントが終了するまで停止する可能性があります。その後、ダンプはロックフリーとなり、テーブルの読み取りと書き込みを妨げません。

先に、バックアップするテーブルは InnoDB テーブルであるとしたため、--single-transaction は、一貫性読み取りを使用し、mysqldump によって表示されたデータが変更されないことを保証します。(ほかのクライアントによる InnoDB テーブルへの変更は、mysqldump プロセスによって表示されません)。バックアップ操作に非トランザクションテーブルが含まれる場合、一貫性には、バックアップ中にそれらが変更されない必要があります。たとえば、mysql データベース内の MyISAM テーブルの場合、バックアップ中に、MySQL アカウントへの管理上の変更があってはなりません。

完全バックアップが必要ですが、それらを作成するために常に都合がよいとは限りません。それらは大きなバックアップファイルを生成し、生成に時間がかかります。それらは、連続した各完全バックアップに、前回の完全バックアップから変更されていない部分でもすべてのデータが含まれるという点で、最適ではありません。初期完全バックアップを作成し、次に増分バックアップを作成するほうが効率的です。増分バックアップは小さく、生成にかかる時間が少なくなります。このトレードオフは、リカバリ時に、完全バックアップをリロードするだけではデータをリストアできないことです。増分バックアップを処理して、増分の変更もリカバリする必要があります。

増分バックアップを作成するには、増分の変更を保存する必要があります。MySQL では、これらの変更はバイナリログで表されるため、MySQL サーバーを常に --log-bin オプションで起動して、そのログを有効にしてください。バイナリロギングが有効にされていると、サーバーはデータの更新中に、各データの変更をファイルに書き込みます。--log-bin オプションで起動し、数日間実行していた MySQL サーバーのデータディレクトリを調べると、これらの MySQL バイナリログファイルが見つかります。

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

MySQL サーバーは再起動するたびに、シーケンスの次の番号を使用して、新しいバイナリログファイルを作成します。サーバーが実行している間、FLUSH LOGS SQL ステートメントを発行するか、mysqladmin flush-logs コマンドによって、手動で、それに現在のバイナリログファイルをクローズし、新しいファイルを開始するように伝えることもできます。mysqldump にはログをフラッシュするオプションもあります。データディレクトリ内の .index ファイルには、ディレクトリ内のすべての MySQL バイナリログのリストが含まれます。

MySQL バイナリログは、増分バックアップのセットを形成するため、リカバリに重要です。完全バックアップの作成時にログをフラッシュさせる場合、その後作成されるバイナリログファイルには、バックアップ以降に行われたすべてのデータの変更が含まれます。ここで、前述の mysqldump コマンドを少し修正して、完全バックアップの時点で MySQL バイナリログをフラッシュするようにし、ダンプファイルに新しい現在のバイナリログの名前が含まれるようにします。

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases > backup_sunday_1_PM.sql

このコマンドの実行後、--flush-logs オプションによって、サーバーにそのログをフラッシュさせるため、データディレクトリには新しいバイナリログファイル gbichot2-bin.000007 が格納されます。--master-data オプションは mysqldump でその出力にバイナリログ情報を書き込ませるため、結果の .sql ダンプファイルにはこれらの行が含まれます。

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

mysqldump コマンドで完全バックアップを作成しているため、これらの行は 2 つのことを意味します。

  • ダンプファイルには、gbichot2-bin.000007 以降のバイナリログファイルに書き込まれた変更の前に行われたすべての変更が含まれます。

  • バックアップ後にログに記録されたすべてのデータの変更は、ダンプファイルに存在しませんが、gbichot2-bin.000007 以降のバイナリログファイルに存在します。

月曜日の午後 1 時に、ログをフラッシュし、新しいバイナリログファイルを開始することによって、増分バックアップを作成できます。たとえば、mysqladmin flush-logs コマンドを実行すると、gbichot2-bin.000008 が作成されます。日曜日の午後 1 時の完全バックアップから月曜日の午後 1 時までのすべての変更は、gbichot2-bin.000007 ファイル内にあります。この増分バックアップは重要であるため、それを安全な場所にコピーすることをお勧めします。(たとえば、それをテープや DVD にバックアップするか、別のマシンにコピーします。)火曜日の午後 1 時に、さらに mysqladmin flush-logs コマンドを実行します。月曜日の午後 1 時から火曜日の午後 1 時までのすべての変更が、gbichot2-bin.000008 ファイル内にあります (これもどこか安全な場所にコピーするべきです)。

MySQL バイナリログはディスク領域を占有します。領域を解放するため、ときどきそれらをパージします。これを実行する 1 つの方法は、完全バックアップを作成したときなど、必要なくなったバイナリログを削除することです。

shell> mysqldump --single-transaction --flush-logs --master-data=2 \
         --all-databases --delete-master-logs > backup_sunday_1_PM.sql
注記

サーバーがレプリケーションマスターサーバーである場合、スレーブサーバーでまだバイナリログの内容を完全に処理していない可能性があるため、mysqldump --delete-master-logs で MySQL バイナリログを削除することは危険な場合があります。PURGE BINARY LOGS ステートメントの説明では、MySQL バイナリログを削除する前に確認すべきことを説明しています。セクション13.4.1.1「PURGE BINARY LOGS 構文」を参照してください。


User Comments
  Posted by santm on May 12, 2006
We had one problem where we had too many bin files. As our database was heavily used the file size was quite big there were too many of them.

Please be sure to have a policy where you remove your old transactional log files as mentioned in the last example.

I could not found the disk pace was mentioned in Binary LOG Manual page.

  Posted by Laurent DENIS on June 20, 2006
Here's a scrip i wrote to backup my db, using a user that's not "mysql".

There's an entry in the sudoers file to allow the user to read the log files

The backup (mysql) user has "super,reload,all" privileges + its password in .my.cnf

The backup path is on a NAS filesystem.

What's missing is a function to remove old full backups if needed.

------------------------------------------------------
#!/bin/bash

MODE=$1
MYSQLUSER=backup
BINLOGPATH=/var/log/mysql
BINLOGNAME=mysql-bin
ARCHIVEPATH=/usr/app/security/var/data/backups/mysql/

setRead() {
# change properties on binlog path
sudo chmod o+rx $BINLOGPATH
sudo chmod o+r $BINLOGPATH/*
}

resetRead() {
# restore properties on binlog path
sudo chmod o-r $BINLOGPATH/*
sudo chmod o-rx $BINLOGPATH
}

copyBinlogs() {
# copy binlogs to archive dir
echo "Copying binlogs"
setRead;
for FILE in `cat $BINLOGPATH/$BINLOGNAME.index`
do
SFILE=(${FILE/*\//})
if [ ! -f $ARCHIVEPATH/$SFILE.gz ]
then
echo "- binlog $SFILE"
cp $FILE $ARCHIVEPATH
gzip $ARCHIVEPATH/$SFILE
fi
done
resetRead;
}

saveBinlogs() {
# move latest binlogs to savedir
echo "Saving binlogs"
SAVEDIR=`date +%Y%m%d`
echo $SAVEDIR
mkdir -p $ARCHIVEPATH/$SAVEDIR
mv $ARCHIVEPATH/*.gz $ARCHIVEPATH/$SAVEDIR
}

# --- Main ---
case "$MODE" in

'full')
# Weekly backup
# - copy all bin logs to backup directory
# - clean path of backup directory
echo "Weekly backup"
DATE=`date +%Y%m%d`
mysqldump -u $MYSQLUSER --single-transaction --flush-logs --master-data=2 \
--all-databases --delete-master-logs | gzip > $ARCHIVEPATH/$SAVEDIR/full-$DATE.sql.gz
copyBinlogs;
saveBinlogs;
;;

'incremental')
# Daily backup
# - flush logs
# - copy all bin logs to backup directory if not already done
echo "Mysql daily backup"
mysqladmin -u $MYSQLUSER flush-logs
copyBinlogs;
;;

*)
echo "Usage: mysql_backup [full|incremental]"
;;

esac
------------------------------------------------------

  Posted by Alexey Zilber on March 16, 2009
You should never use --delete-master-logs as the warning specifies. The above script is quite dangerous if you have many replication slaves.

A much better alternative is to do
echo 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);' | /usr/bin/mysql
  Posted by Fred Nurk on July 8, 2009
Regarding copyBinlogs() - it may be wise to preserve the creation and modification times, with 'cp -p', so it is easy to order them correctly when recovering.
  Posted by Fred Nurk on July 9, 2009
further to the comment on delete-master-logs - see
http:://bugs.mysql.com/bug.php?id=24733

In other words, delete-master-logs is bad news for versions before the fix for that bug went in.
  Posted by Quan Tong Anh on August 25, 2010
@Laurent DENIS: Are you sure you want to backup all binlog files in mysql-bin.index? I think you must exclude the latest file which has been created by the mysqladmin with --flush-log option. It will be copy at the next backup.

  Posted by Jorge Torralba on May 27, 2014
Here is a simplified script to purge old binary log files. Seems to work

#!/bin/sh

clear

SLAVE="192.168.1.100"
MASTER="192.168.1.101"
PASSWD="rootpassword"
USER="root"

MYSQLCOMMAND=`which mysql`

# Get log file info from slave
masterlogfile=`$MYSQLCOMMAND -u $USER -h $SLAVE -p$PASSWD -Bse 'show slave status \G' | grep -w 'Master_Log_File' | cut -f2 -d ":" `

# Get log file info from master
lastbin=`$MYSQLCOMMAND -u $USER -h $MASTER -p$PASSWD -Bse 'show binary logs \G' | grep Log_name | tail -1 | cut -f2 -d ":" `

# Remove spaces
lastbin=`echo $lastbin | sed 's/^ //' `

# Only purge if both master and slave have same log files
if [ $masterlogfile = $lastbin ]; then
sql="PURGE BINARY LOGS TO '$lastbin' ;"
$MYSQLCOMMAND -v -u $USER -h $MASTER -p$PASSWD -Bse "$sql"

# Show list of all binary log files after purge
$MYSQLCOMMAND -u $USER -h $MASTER -p$PASSWD -Bse 'show binary logs \G'
echo

fi

Sign Up Login You must be logged in to post a comment.