Linux下mysql定时备份操作

1.操作系统
[root@localhost ~]# cat /proc/version
Linux version 2.6.18-53.el5PAE ([e-mail]) (gcc version 4.1.2 20070626 (Red Hat 4.1.2-14)) #1 SMP Mon Nov 12 02:55:09 EST 2007
[root@localhost ~]# cat /etc/redhat-release
CentOS release 5 (Final)

2.数据库 用户名root 密码china123
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.1.50, for pc-linux-gnu (i686) using readline 5.1
3.备份整库脚本Shell

 

3.1 备份方式一：单库备份,备份到/tools/db_backup/pgs与/tools/db_backup/iteye下，自动删除3天前的文件
backup_pgs_byday.sh内容为
#! /bin/bash
mysqldump -cp --user=root --password='china123' pgs | gzip > /tools/db_backup/pgs/pgs-`date +%Y%m%d%H`.sql.gz ;
mysqldump -cp --user=root --password='china123' iteye | gzip > /tools/db_backup/iteye/iteye-`date +%Y%m%d%H`.sql.gz ;

 

 

 

find /tools/db_backup/pgs -type f -ctime 3 -exec rm -f {} \;
find /tools/db_backup/iteye -type f -ctime 3 -exec rm -f {} \;

 

 

 

3.2 备份方式二：备份上一个月指定表的数据,,备份到/tools/db_backup/pgs_bak 下
backup_his_bymonth.sh内容为
#! /bin/bash
YEAR="`date +"%Y"`"
MON1=`date +"%m"`

 

if [ $MON1 -eq 01 ]; then
{
        MONTH=12
        YEAR=`expr $YEAR - 1`
};
else
{
        MON2=`expr $MON1 - 1`
        if [ $MON1 -lt 10 ]; then
                SUPP=0
                MONTH="$SUPP$MON2"
        fi
};
fi
MYDATE="$YEAR-$MONTH" 
mysqldump -cp -t --single-transaction --user=root --password='china123' --where="DATE_FORMAT(donetime,'%Y-%m') = '`expr $MYDATE`'" pgs T_NODE_INSTANCE | gzip > /tools/db_ba
ckup/pgs_bak/T_NODE_INSTANCE_`expr $MYDATE`_BAK.sql.gz;
mysqldump -cp -t --single-transaction --user=root --password='china123' --where="DATE_FORMAT(donetime,'%Y-%m') = '`expr $MYDATE`'" pgs T_NODE_INSTANCE_HIS | gzip > /tools/db_ba
ckup/pgs_bak/T_NODE_INSTANCE_HIS_`expr $MYDATE`_BAK.sql.gz;

 

4.定时任务 
4.1查看定时任务
[root@localhost /]# crontab -u root -l

 

4.2编辑定时任务
[root@localhost /]# crontab -u root -e
0 1 * * * /tools/db_backup/backup_pgs_byday.sh
0 1 1 * * /tools/db_backup/backup_his_bymonth.sh

 

4.3解释
0 1 * * * /tools/db_backup/backup_pgs_byday.sh 表示每天的凌晨1点开始备份
0 1 1 * * /tools/db_backup/backup_his_bymonth.sh 表示每个月1号的凌晨1点开始备份

 

4.4开启crontab服务
[root@localhost /]# /sbin/service crond restart
Stopping crond: [  OK  ]
Starting crond: [  OK  ]

 

4.5再次查看是否存在
[root@localhost /]# crontab -u root -l
0 1 * * * /tools/db_backup/backup_pgs_byday.sh
0 1 1 * * /tools/db_backup/backup_his_bymonth.sh