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

最近写了一个mysql下的分库备份的基本，现在是放在生产环境下使用的，目前运行没有任何问题。放出来给有需要的朋友！[root@g6k.cn 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版本的升级不能在脚本中使用密码，使用会出现警告导致设置定时任务备份失败，需要写入到配置文件中

写入格式如下

[root@g6k.cn 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等不需要的数据库了！

欢迎转发但是请注明出处！