定期的に発行SQLのdump

毎日一回DBのdumpをとっているんですが、もっと細かくdumpとりたい。っていうお話が来ました。

なのでbinlogから定期的にsqlのdumpをとるようにしました。

パフォーマンスとかあんま考えなくてもいい感じだったので、1時間に１回定期的にbinlogから更新系SQLを抜き取っています。

#!/bin/sh

### MySQLのパスワード
MYSQL_PASSWORD='**********'

### プロジェクトのフルパス
PROJECT_PATH='/var/www/project/'

### dumpファイル格納先
DUMPFILE_PATH='data/dump'

### 開始日
START_DATE=`date -d "1 hours ago" "+%Y-%m-%d %H:00:00"`

### 終了日
END_DATE=`date "+%Y-%m-%d %H:00:00"`

### ファイル接尾語
FILE_SUFFIX=`date "+%H"`

### dumpファイル名
FILENAME="today_sql_${FILE_SUFFIX}.sql"

### mysqlデータ格納先フォルダ
MYSQL_DIR=/var/lib/mysql

### bin-log-indexファイル名
MYSQL_BIN_LOG_FILENAME='mysql-bin.index'

### 使用中のbinログファイル名
BINLOG_FILE=`echo ${MYSQL_PASSWORD} | sudo -S tail -n 1 ${MYSQL_DIR}/${MYSQL_BIN_LOG_FILENAME}`

### dump実行
echo ${MYSQL_PASSWORD} | sudo -S mysqlbinlog $MYSQL_DIR/$BINLOG_FILE \
    --start-datetime "${START_DATE}" \
    --stop-datetime "${END_DATE}" | \
    perl -e 'while(<>){ chomp; next if m!^#!; if ( m{/*!*/;$} ) { $p .= $_; print "$p\n"; $p="" } else { $p .= $_." "} }' | \
    perl -nle 'm!^(DELETE FROM|REPLACE INTO|INSERT INTO|UPDATE)\s+([^ ]+)!i && print' > $PROJECT_PATH/$DUMPFILE_PATH/$FILENAME

1日1回のdumpだけだと最悪丸一日の空白があるので、それをなるべく埋めるための1時間に１回のdumpになります。