mysql上数据库太多,原来网上找的一些脚本无法满足需求,花了几个小时在原来别人写的脚本的基础上重写一份,可以满足更多的需求了
主要使用的技术为cron mysqldump mysqlhotcopy 和tar 所以支持3种备份方式一个脚本就可以了 只需要在运行的时候加参数
1 为 mysqldump
2 为 mysqlhotcopy
3 为 tar
脚本代码如下
[ccn lang="bash" tab_size="4" theme="blackboard" width="550" ]
#!/bin/bash
###########################################
# mysql 备份脚本
# 注意 默认的是每种备份方案是默认删除5个之前的
# usage: 放到crontab里
# createdevuser.sh
# create date 2010-12-07
# update date 2010-12-07
###########################################
# 需要备份的数据库 用空格隔开
BACKUP_DATABASES="xxx_db xxx_class_xxxx xxxxx_db xxxxx_dz"
# 备份数据库的用户名和密码(最好是root的,这样可以通用备份所有的库)
DB_USER=root
DB_PASS=xxxxxxx
# 备份文件的根目录
BACKUP_ROOT_PATH=/app02/bak
# mysql命令的目录
MYSQL_BIN_PATH=/usr/bin
# 是否开启NFS备份
IS_NFS_BACKUP=no
# 远程备份路径 (此路径为nfs映射的路径)
REMOTE_BACKUP_ROOT_PATH=/app01/nfs_mount/dl/website_backup
#######################################################################
## 核心方法
#######################################################################
# 打印日志
log_common()
{
if [[ ! -z $1 ]] ; then
echo -e '\E[34;48m'"\033[1m $1 \033[0m"
fi
}
log_warn()
{
if [[ ! -z $1 ]] ; then
echo -e '\E[33;48m'"\033[1m $1 \033[0m"
fi
}
log_error()
{
if [[ ! -z $1 ]] ; then
echo -e '\E[31;48m'"\033[1m $1 \033[0m"
fi
}
log_title()
{
if [[ ! -z $1 ]] ; then
echo -e '\E[35;48m'"\033[1m ********************** $1 ********************* \033[0m"
fi
}
log_para()
{
if [[ ! -z $1 && ! -z $2 ]] ; then
echo -e '\E[32;48m'"\033[1m $1 : \033[0m" $2
fi
}
backup_mysql_db()
{
# 本次备份的db名字
DBName=$1
DBUser=$DB_USER
DBPasswd=$DB_PASS
BackupPath=$BACKUP_ROOT_PATH/mysql/
RemotePATH=$REMOTE_BACKUP_ROOT_PATH/mysql/
LogFile=$BACKUP_ROOT_PATH/mysql/dbbackup.log
DBPath=/var/lib/mysql/
BackupMethod=$2
## 初始化目录
if [ ! -e $BackupPath ] ; then
mkdir -p $BackupPath
fi
# 如果开启NFS备份
if [ $IS_NFS_BACKUP == "yes" ] ; then
if [ ! -e $RemotePATH ] ; then
mkdir -p $RemotePATH
fi
fi
## 初始化备份文件
NewFile="$BackupPath""$DBName"_$(date +%y%m%d)_"$BackupMethod".tgz
RemoteFile="$RemotePATH""$DBName"_$(date +%y%m%d)_"$BackupMethod".tgz
DumpFile="$BackupPath""$DBName"_$(date +%y%m%d)
OldFile="$BackupPath""$DBName"_$(date +%y%m%d --date='5 days ago')_"$BackupMethod".tgz
OldFiler="$RemotePATH""$DBName"_$(date +%y%m%d --date='5 days ago')_"$BackupMethod".tgz
if [ $MYSQL_BACKUP_METHOD_PARA == 1 ] ; then
OldFile="$BackupPath""$DBName"_$(date +%y%m%d --date='5 days ago')_"$BackupMethod".tgz
OldFiler="$RemotePATH""$DBName"_$(date +%y%m%d --date='5 days ago')_"$BackupMethod".tgz
elif [ $MYSQL_BACKUP_METHOD_PARA == 2 ] ; then
OldFile="$BackupPath""$DBName"_$(date +%y%m%d --date='5 week ago')_"$BackupMethod".tgz
OldFiler="$RemotePATH""$DBName"_$(date +%y%m%d --date='5 week ago')_"$BackupMethod".tgz
elif [ $MYSQL_BACKUP_METHOD_PARA == 3 ] ; then
OldFile="$BackupPath""$DBName"_$(date +%y%m%d --date='5 month ago')_"$BackupMethod".tgz
OldFiler="$RemotePATH""$DBName"_$(date +%y%m%d --date='5 month ago')_"$BackupMethod".tgz
fi
echo "-------------------------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile
echo "--------------------------" >> $LogFile
# 删除不要的过期备份
if [ -f $OldFile ] ; then
rm -f $OldFile >> $LogFile 2>&1
echo "[$OldFile]Delete Old File Success!" >> $LogFile
else
echo "[$OldFile]No Old Backup File!" >> $LogFile
fi
if [ $IS_NFS_BACKUP == "yes" ] ; then
if [ -f $OldFiler ] ; then
rm -f $OldFiler >> $LogFile 2>&1
echo "[$OldFiler]Delete Old File Success!" >> $LogFile
else
echo "[$OldFiler]No Old Backup File!" >> $LogFile
fi
fi
if [ -f $NewFile ] ; then
echo "[$NewFile]The Backup File is exists,Can't Backup!" >> $LogFile
else
case $BackupMethod in
mysqldump)
if [ -z $DBPasswd ] ; then
$MYSQL_BIN_PATH/mysqldump -u $DBUser --opt $DBName > $DumpFile
else
$MYSQL_BIN_PATH/mysqldump -u $DBUser -p$DBPasswd --opt $DBName > $DumpFile
fi
tar czvf $NewFile $DumpFile >> $LogFile 2>&1
echo "[$NewFile]Backup Success!" >> $LogFile
rm -rf $DumpFile
;;
mysqlhotcopy)
rm -rf $DumpFile
mkdir $DumpFile
if [ -z $DBPasswd ] ; then
$MYSQL_BIN_PATH/mysqlhotcopy -u $DBUser $DBName $DumpFile >> $LogFile 2>&1
else
$MYSQL_BIN_PATH/mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile >>$LogFile 2>&1
fi
tar czvf $NewFile $DumpFile >> $LogFile 2>&1
echo "[$NewFile]Backup Success!" >> $LogFile
rm -rf $DumpFile
;;
*)
/etc/init.d/mysql stop >/dev/null 2>&1
tar czvf $NewFile $DBPath$DBName >> $LogFile 2>&1
/etc/init.d/mysql start >/dev/null 2>&1
echo "[$NewFile]Backup Success!" >> $LogFile
;;
esac
fi
if [ $IS_NFS_BACKUP == "yes" ] ; then
cp -ruv $NewFile $RemoteFile
fi
echo "-------------------------------------------" >> $LogFile
}
# 备份所有的
backup_mysql_db_all()
{
log_title " start backup mysql database "
# 循环db 开始备份
for db_to_name in $BACKUP_DATABASES
do
log_para "start to backup db : " " $db_to_name "
backup_mysql_db $db_to_name $MYSQL_BACKUP_METHOD
log_common "the db $db_to_name backup success !"
done
log_title " backup mysql database end "
}
# 备份策略
MYSQL_BACKUP_METHOD_PARA=$1
MYSQL_BACKUP_METHOD=mysqldump
# 检查并设置默认参数
if [[ ! -z $1 ]] ; then
log_common "your enter parameter is $1"
else
log_common "you havn't enter any method of mysql backup use 'mysqldump' by default"
MYSQL_BACKUP_METHOD_PARA=1
fi
# 初始化备份策略
if [ $MYSQL_BACKUP_METHOD_PARA == 1 ] ; then
MYSQL_BACKUP_METHOD=mysqldump
elif [ $MYSQL_BACKUP_METHOD_PARA == 2 ] ; then
MYSQL_BACKUP_METHOD=mysqlhotcopy
elif [ $MYSQL_BACKUP_METHOD_PARA == 3 ] ; then
MYSQL_BACKUP_METHOD=tar
fi
log_para "mysql backup method is " " $MYSQL_BACKUP_METHOD "
# 开始备份
backup_mysql_db_all
[/ccn]
然后在crontab里添加下面的代码
[ccn lang="bash" tab_size="4" theme="blackboard" width="550" ]
21 3 * * * sh /root/scripts/mysqlbackup.sh 1
21 3 * * 1 sh /root/scripts/mysqlbackup.sh 2
21 3 * 1 * sh /root/scripts/mysqlbackup.sh 3
[/ccn]