全量备份

点击(此处)折叠或打开

#!/bin/bash
#Edit by Wang Wen'an @ 2015-06-15
USER=user
PWD=password
BASE_DIR=/home/backup/
LOG=backup_history.log
innobackupex --defaults-file=/etc/my.cnf --user=$USER --password=$PWD --parallel=8 --compress --compress-threads=8 --no-timestamp --use-memory=2G $BASE_DIR$(date -d today +%Y-%m-%d)
if [ $? -eq 0 ]; then
echo "full backup finished! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
find $BASE_DIR -maxdepth 1 -name '20*-*-*' -type d -mtime +1 | xargs echo "these backups are going to delete: ">> $BASE_DIR$LOG
find $BASE_DIR -maxdepth 1 -name '20*-*-*' -type d -mtime +1 | xargs rm -rf {} \;
if [ $? -eq 0 ]; then
echo "delete the expired full backups completed!(the list is shown above).......time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
else
echo "delete the expired full backups failed!.....time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
fi
else
echo "full backup failed! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
fi

增量备份

点击(此处)折叠或打开

#!/bin/bash
#Edit by Wang Wen'an @ 2015-06-15
USER=user
PWD=password
BASE_DIR=/home/backup/
INCR_DIR=/home/backup/incr-$(date -d today +%Y-%m-%d)/
LOG=backup_history.log
if [ ! -d $INCR_DIR ]; then
mkdir $INCR_DIR && echo "make incremental dir complete : $INCR_DIR" >> $BASE_DIR$LOG
else
echo "make incremental dir is exist : $INCR_DIR , skip the process....." >> $BASE_DIR$LOG
fi
innobackupex --defaults-file=/etc/my.cnf --user=$USER --password=$PWD --parallel=8 --compress --compress-threads=8 --use-memory=2G --incremental $INCR_DIR --incremental-basedir=$BASE_DIR$(date -d today +%Y-%m-%d)
if [ $? -eq 0 ]; then
echo "incremental backup finished! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
find $BASE_DIR -maxdepth 1 -name 'incr-20*-*-*' -type d -mtime +1 | xargs echo "these backups are going to delete: ">> $BASE_DIR$LOG
find $BASE_DIR -maxdepth 1 -name 'incr-20*-*-*' -type d -mtime +1 | xargs rm -rf {} \;
if [ $? -eq 0 ]; then
echo "delete the expired incrmental backups completed!(the list is shown above).......time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
else
echo "delete the expired incrmental backups failed!.......time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
fi
else
echo "incremantal backup failed! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
fi

Linux定时任务设置（参考）

点击(此处)折叠或打开

每天的备份用年-月-日来保存，增量备份带前缀incr
懒人自用~

自动汇总每天的慢查询日志

点击(此处)折叠或打开

#!/bin/bash
#Edit by Wang Wen'an @ 2015-09-06
PATH_LOG=/var/log/
LOG=mysql-slow-query.log
PATH_SLOW=/home/backup/slow_log/
SLOW_LOG=mysql-slow.log.$(date -d yesterday +%Y%m%d)
pt-query-digest $PATH_LOG$LOG > $PATH_SLOW$SLOW_LOG
if [ $? -eq 0 ]; then
echo "report of slow query is generated, time:$(date -d today +%Y-%m-%d\ %H:%M:%S)"
cat /dev/null > $PATH_LOG$LOG
if [ $? -eq 0 ]; then
echo "log of slow query is cleaned, time:$(date -d today +%Y-%m-%d\ %H:%M:%S)"
fi
fi

ITPub博客

MySQL脚本（percona-toolkit）