操作步骤:
1, 将脚本放到任意位置下, 不容易被误删即可
2, 定时任务, 一周执行一次脚本
脚本如下:
- #!bin/bash
- backuppath=/data/MySQL/backup
- date=$(date +%Y%m%d%H%M)
- SQLuser=root
- SQLpwd=password
- mysqldump=/usr/local/MySQL/bin/mysqldump
- MySQL=/usr/local/MySQL/bin/MySQL
- HIS_Date=`date -d $(date -d "-30 day" +%Y%m%d) +%s` #取 30 天之前的时间戳
- Sql_Date=`date -d $(date -d "-7 day" +%Y%m%d) +%s` #取 7 天之前的时间戳
- # 进入 MySQL 数据库, 清除 zabbix 历史数据
- $MySQL -u$SQLuser -p$SQLpwd -e"
- use zabbix;
- delete from history WHERE 'clock' < $Sql_Date;
- delete from history_uint WHERE 'clock' < $Sql_Date;
- delete from history_str WHERE 'clock' < $Sql_Date;
- delete from history_text WHERE 'clock' < $Sql_Date;
- delete from history_log WHERE 'clock' < $Sql_Date;
- exit
- "
- # 建立备份目录
- if [ ! -e $backuppath ];then
- mkdir -p $backuppath
- fi
- # 开始备份 zabbix 库
- $mysqldump -u$SQLuser -p$SQLpwd zabbix >$backuppath/zabbix$date.sql
- # 删除 1 个月前的备份数据
- find /data/MySQL/backup -mtime +30 -type f -name \zabbix*.sql -exec rm -f {} \;
在数据库历史数据过大, 可以考虑 truncate 掉历史数据.
- $MySQL -u$SQLuser -p$SQLpwd -e"
- use zabbix;
- truncate table history;
- truncate table history_log;
- truncate table history_uint;
- truncate table history_str;
- truncate table history_text;
- exit
- "
转自
来源: http://www.bubuko.com/infodetail-2867010.html