MySQL脚本（percona-toolkit）

全量备份

点击(此处)折叠或打开

1. #!/bin/bash
2. #Edit by Wang Wen'an @ 2015-06-15
3. USER=user
4. PWD=password
5. BASE_DIR=/home/backup/
6. LOG=backup_history.log
7. 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)
8. if [ $? -eq 0 ]; then
9.     echo "full backup finished! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
10.     find $BASE_DIR -maxdepth 1 -name '20*-*-*' -type d -mtime +1 | xargs echo "these backups are going to delete: ">> $BASE_DIR$LOG
11.     find $BASE_DIR -maxdepth 1 -name '20*-*-*' -type d -mtime +1 | xargs rm -rf {} \;
12.     if [ $? -eq 0 ]; then
13.         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
14.     else
15.         echo "delete the expired full backups failed!.....time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
16.     fi
17. else
18.     echo "full backup failed! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
19. fi

增量备份

点击(此处)折叠或打开

1. #!/bin/bash
2. #Edit by Wang Wen'an @ 2015-06-15
3. USER=user
4. PWD=password
5. BASE_DIR=/home/backup/
6. INCR_DIR=/home/backup/incr-$(date -d today +%Y-%m-%d)/
7. LOG=backup_history.log
8. if [ ! -d $INCR_DIR ]; then
9.     mkdir $INCR_DIR && echo "make incremental dir complete : $INCR_DIR" >> $BASE_DIR$LOG
10. else
11.     echo "make incremental dir is exist : $INCR_DIR , skip the process....." >> $BASE_DIR$LOG
12. fi
13. 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)
14. if [ $? -eq 0 ]; then
15.     echo "incremental backup finished! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
16.     find $BASE_DIR -maxdepth 1 -name 'incr-20*-*-*' -type d -mtime +1 | xargs echo "these backups are going to delete: ">> $BASE_DIR$LOG
17.     find $BASE_DIR -maxdepth 1 -name 'incr-20*-*-*' -type d -mtime +1 | xargs rm -rf {} \;
18.     if [ $? -eq 0 ]; then
19.         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
20.     else
21.         echo "delete the expired incrmental backups failed!.......time:$(date -d today +%Y-%m-%d\ %H:%M:%S) " >> $BASE_DIR$LOG
22.     fi
23. else
24.     echo "incremantal backup failed! time:$(date -d today +%Y-%m-%d\ %H:%M:%S)" >> $BASE_DIR$LOG
25. fi

Linux定时任务设置（参考）

点击(此处)折叠或打开

1. 1 0 * * * source /home/backup/script/full_backup.sh
   
2. */30 * * * * source /home/backup/script/incr_backup.sh

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

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

点击(此处)折叠或打开

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29510932/viewspace-1787716/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29510932/viewspace-1787716/