#!/bin/sh #DATABASE INFO DB_NAME="ppdb" DB_USER="root" DB_PASS="password" DISPOSE_TABLE="dispose_ticles" RST_TABLE="match_rst" DB_IP=100.55.1.129 BIN_DIR="/usr/bin" BAK_DIR="/home/mysql_bak/data" DATE=`date +%Y%m%d_%H%M%S` #mkdir -p $BAK_DIR #备份包 形成压缩包 $BIN_DIR/mysqldump $DB_NAME $DISPOSE_TABLE > $BAK_DIR/$DISPOSE_TABLE.dump_$DATE.sql $BIN_DIR/mysqldump $DB_NAME $DISPOSE_TABLE | gzip > $BAK_DIR/$DISPOSE_TABLE.dump_$DATE.sql.gz $BIN_DIR/mysqldump $DB_NAME $RST_TABLE > $BAK_DIR/$RST_TABLE.dump_$DATE.sql $BIN_DIR/mysqldump $DB_NAME $RST_TABLE | gzip > $BAK_DIR/$RST_TABLE.dump_$DATE.sql.gz #定期删除60天的备份包 find $BAK_DIR -name "name_*.sql.gz" -type f -mtime +60 -exec rm {} \; > /dev/null 2>&1 #30天前的指定库表数据删除操作 (当前时间减去30天） delete_date=`date --date=‘30 day ago‘ +%Y-%m-%d` echo "delete_date=$delete_date" #删除rst表信息 rst_sql="delete from $RST_TABLE where update_time <= $delete_date order by update_time;"; echo "rst_sql=$rst_sql" #ret=$(mysql -u $DB_USER -h ${DB_IP} -p${DB_PASS} $DB_NAME -e "$sql"); ret=$(mysql -h${DB_IP} $DB_NAME -e "$rst_sql"); echo $ret #删除dispose表信息 dispose_sql="delete from $DISPOSE_TABLE where judge_time <= $delete_date order by judge_time;"; echo "dispose_sql=$dispose_sql" ret=$(mysql -h${DB_IP} $DB_NAME -e "$dispose_sql"); echo $ret

2.定时设置：每隔30天的1点进行备份。

[root@mysql_bak]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# | .------------- hour (0 - 23)
# | | .---------- day of month (1 - 31)
# | | | .------- month (1 - 12) OR jan,feb,mar,apr ...
# | | | | .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# | | | | |
# * * * * * user-name command to be executed
0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1

[root@mysql_bak]# crontab -e
0 1 */30 * * /home/mysql_bak/mysql_bak.sh > /dev/null 2>&1

重启crontab服务
service crond restart

20170304 21:10 于家中床前

作者：铭毅天下
转载请标明出处，