mysql生产环境下的分库备份脚本

最近写了一个mysql下的分库备份的基本，现在是放在生产环境下使用的，目前运行没有任何问题。放出来给有需要的朋友！

[[email protected] script]#cat mysql_back.sh
#!/bin/bash
#curl:www.g6k.cn
scripath=/script
logfile=/script/logs/mysqlback.log
backpath=/backup/mysqlback/`date +%F`
pushckpath=/home/mysqlbackup/backup/mysqlback/

remotehost_ip=xxxx # 远程主机ip
remotehost_key=~/.ssh/xxxx
remotehost_port=22
hosttype=`hostname`   # 机器类型

mysql=/usr/local/mysql/bin/mysql
mysqldump=/usr/local/mysql/bin/mysqldump

ssh_cmd="ssh -p ${remotehost_port} -o StrictHostKeyChecking=no -i ${remotehost_key}  root@${remotehost_ip}"
scp_cmd="scp -l 16000 -P ${remotehost_port} -o StrictHostKeyChecking=no -i ${remotehost_key}"

[ ! -d ${backpath} ] && mkdir -p ${backpath}
[ ! -d ${scripath}/logs ] && mkdir -p ${scripath}/logs

cd ${scripath}
echo "========================================"  >> ${logfile}
echo "`date "+%F %H:%M:%S"` 开始备份MySQL数据"  >> ${logfile}       #写入日志
#过滤不需要的库
DBname=`$mysql --defaults-extra-file=/script/myback.cnf -e "show databases"|sed 1d|egrep -v "schema|test|mysql"` 
Dumpcmd="$mysqldump --defaults-extra-file=/script/myback.cnf"   #这是配置文件路径说明下下面

${ssh_cmd} "ls ${pushckpath}/${hosttype}/`date +%F`"  &>/dev/null
if [ $? -ne 0 ];then
    ${ssh_cmd}  "mkdir -p ${pushckpath}/${hosttype}/`date +%F`"
fi

for sqlname in $DBname   #循环出库名称
do
    $Dumpcmd --databases $sqlname  |gzip >${backpath}/${sqlname}.sql.gz    #备份库并压缩
    if [ $? -eq 0 ];then
        echo "`date "+%F %H:%M:%S"` ${sqlname}数据库备份成功" >> ${logfile} 
    fi
    ${scp_cmd} ${backpath}/${sqlname}.sql.gz root@${remotehost_ip}:${pushckpath}/${hosttype}/`date +%F`/    #拷贝到远端
    if [ $? -eq 0 ];then
        echo "`date "+%F %H:%M:%S"` ${sqlname}数据库远程备份成功" >> ${logfile}
    fi
done

#cd /backup/mysqlback
#find /backup/mysqlback/*/* -mtime +7 -exec rm -f {} \;  # 删除七天以前的备份数据

另外由于mysql版本的升级不能在脚本中使用密码，使用会出现警告导致设置定时任务备份失败，需要写入到配置文件中
写入格式如下

[[email protected] script]# cat myback.cnf 
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4
host = 127.0.0.1
user = root
password = 'password'

这样就可以备份你这台服务器下的所有数据库并排除test等不需要的数据库了！
欢迎转发但是请注明出处！

mysql生产环境下的分库备份脚本