MySQL备份脚本

MySQL备份脚本

#!/bin/bash
#backup_mysql_data

source /etc/pro1file
source ~/.bash_profile

set -ex

#变量数据
bkdate=`date "+%Y%m%d%H%M%S"`
mysql_user=root
mysql_pass='Mysoft123!'
Datadisk="/data/mysqlbak"   # 数据盘一般比较大
BackupDir=$Datadisk/mysql_bak/$bkdate
LogDir=$Datadisk/mysql_bak
MysqlList=`mysql -u$mysql_user -p$mysql_pass -h 127.0.0.1 -P 3306 -e 'show databases;' 2>/dev/null|sed '1d' | grep -v performance_schema |grep -v information_schema`

if [ ! -d $LogDir ];then
mkdir -p $LogDir
#chown -R myerp:myerp $LogDir
chmod -R 775 $LogDir
fi

#备份数据库
function database () {
echo "`date "+%m %d %H:%M:%S"` Begin backup database" >> $LogDir/backmysql.log
if [ ! -d $BackupDir ];then
mkdir -p $BackupDir
#chown -R myerp:myerp $BackupDir
chmod -R 775 $BackupDir
fi
#开始备份
for n in $MysqlList;
do
echo "`date "+%m %d %H:%M:%S"` Begin backup $n database" >> $LogDir/backmysql.log
mysqldump -u$mysql_user -p$mysql_pass -h 127.0.0.1 -P 3306 -B $n -E -R --triggers --single-transaction --order-by-primary --hex-blob --add-drop-table --routines --events --set-gtid-purged=OFF |sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' -e 's/DEFINER[ ]*=.*FUNCTION/FUNCTION/' -e 's/DEFINER[ ]*=.*PROCEDURE/PROCEDURE/' -e 's/DEFINER[ ]*=.*TRIGGER/TRIGGER/' -e 's/DEFINER[ ]*=.*EVENT/EVENT/' > $BackupDir/$n.sql
echo "`date "+%m %d %H:%M:%S"` backup database done" >> $LogDir/backmysql.log
done

}

function compress () {
echo "`date "+%m %d %H:%M:%S"` Begin complete database " >> $LogDir/backmysql.log
cd $BackupDir
for i in $MysqlList;
do
tar czvf $i.tar.gz --remove-files $i.sql
done
echo "`date "+%m %d %H:%M:%S"` data compress done" >> $LogDir/backmysql.log
}

#删除7天之前的数据
#function deletebk () {
#find $Datadisk/mysql_bak -type d -mtime +7 -exec rm -rf {} \;
#echo "`date "+%m %d %H:%M:%S"` delete database than 7 days" >> $LogDir/backmysql.log
#}

echo `date "+%m %d %H:%M:%S"` "All is OK" >> $LogDir/backmysql.log


database
compress
#deletebk


echo "mysql数据库备份完成"