全量备份
点击(此处)折叠或打开
- #!/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定时任务设置（参考）
点击(此处)折叠或打开
- 1 0 * * * source /home/backup/script/full_backup.sh
- */30 * * * * source /home/backup/script/incr_backup.sh
每天的备份用年-月-日来保存，增量备份带前缀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
