转载备份。

以下脚本保存为 /etc/mysql_backup/mysql_backup.sh：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#!/bin/bash

dbserver='127.0.0.1'
dbuser='root'
dbpasswd='password'
dbname='test_db'
backtime=`date +%Y%m%d`
logfile='/etc/mysql_backup/mysql_backup.log'
datapath="/etc/mysql_backup/data"

echo "" >> ${logfile}
echo "-------------------------------------------------" >> ${logfile}
echo "备份时间为${backtime},备份数据库表 ${dbname} 开始" >> ${logfile}
for table in $dbname; do
    cd $datapath
    source=`mysqldump -h ${dbserver} -u ${dbuser} -p${dbpasswd} ${table} > ${backtime}.sql` 2>> ${logfile};
    if [ "$?" == 0 ];then
        tar zcf ${backtime}-${table}.tar.gz ${backtime}.sql > /dev/null # 进行压缩
        rm -f ${backtime}.sql
        find $datapath -name "*.tar.gz" -type f -mtime +30 -exec rm -rf {} \; > /dev/null 2>&1  # 删除 30 天前备份
        echo "数据库表 ${dbname} 备份成功!!" >> ${logfile}
    else
        echo "数据库表 ${dbname} 备份失败!!" >> ${logfile}
    fi
done

然后加入 crontab，凌晨四点执行：

1
0 4 * * * /etc/profile; bash /etc/mysql_backup/mysql_backup.sh

crontab 的使用方法可以参考 crontab