mysql备份脚本

#!/bin/sh



#设置用户、密码和主机IP

username=用户名

password=密码

hostip=localhost



dblists=`/usr/local/mysql/bin/mysql -u$username -p$password -h$hostip -e"SHOW DATABASES;"`

denylists=(Database mysql test information_schema performance_schema)



#循环备份数据库

for dbname in ${dblists[*]}

do

#排除不用备份的数据库

flag=0

for i in ${denylists[*]}

do



if [ "$i" = "$dbname" ]

then

flag=1

fi

done

if [ $flag -ne 1 ]

then

#指定时间日期和备份目录

backupdir=/data/bakmysql/$dbname`date +%Y-%m-%d_%H%M%S`.sql.gz

LogFile=/data/bakmysql/backlog/baklog`date +%Y-%m-%d_%H%M%S`.log



#写入备份操作日志文件

echo "-------------------------------------------" >> $LogFile

echo " " >> $LogFile

/usr/local/mysql/bin/mysql -u$username -p$password -h$hostip $dbname -e"SHOW MASTER STATUS;" >>$LogFile



#开始备份数据库

/usr/local/mysql/bin/mysqldump -u$username -p$password -h$hostip $dbname -e --max_allowed_packet=10485760 --net_buffer_length=163840 | gzip>$backupdir



echo " " >> $LogFile

/usr/local/mysql/bin/mysql -u$username -p$password -h$hostip $dbname -e"SHOW MASTER STATUS;" >>$LogFile

echo " " >> $LogFile

echo " " >> $LogFile

echo " " >> $LogFile

echo $(date +"%y-%m-%d %H:%M:%S") >> $LogFile

echo "--------------------------" >> $LogFile

fi



done

#删除七天前备份

find /data/bakmysql/ -mtime +10 -name "*.sql.gz" -exec rm -rf {} \;