mysql 全量备份的脚本
#!/bin/bash
user=root
userPWD=root
database=cloud
bakDir=/backup/mysql/weekly_backup
if [ ! -d $bakDir ]
then
mkdir -p "$bakDir"
fi
CONTAINER=mysql
DATE=`date +%Y%m%d`
LogFile=$bakDir/$DATE-full-mysqlbak.log
echo " " >> $LogFile
echo " " >> $LogFile
echo "--------------------------" >> $LogFile
echo $(date +"%y-%m-%d %H:%M:%S") >>$LogFile
echo "-----------------" >> $LogFile
cd $bakDir
DumpFile=$DATE.sql.gz
docker exec $CONTAINER mysqldump -u$user -p$userPWD -F --quick --single-transaction $database | gzip > $DumpFile
echo "Dump Done" >> $LogFile
echo "[$DumpFile]Backup Success!" >> $LogFile
find $bakDir/ -type f -mtime +10 -exec rm {} \; > /dev/null 2>&1
coscmd upload -r -s /backup/mysql/weekly_backup mysql/ > /dev/null
增量备份
#!/bin/bash
user=root
userPWD=root
database=cloud
CONTAINER=mysql
docker exec $CONTAINER mysqladmin -u$user -p$userPWD flush-logs
if [ ! -d /backup/mysql/daily_backup ]
then
mkdir /backup/mysql/daily_backup
fi
daily_databakDir=/backup/mysql/daily_backup
mysqlDataDir=/var/lib/docker/volumes/docker-compose_mysql_data/_data
eMailFile=$daily_databakDir/email.txt
DATE=`date +%Y%m%d`
logFile=$daily_databakDir/mysql$DATE.log
echo " " > $eMailFile
echo "-----------------------" >> $eMailFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $eMailFile
echo "-------------------------" >> $eMailFile
Time=$(date "-d 3 day ago" +%Y%m%d%H:%M:%S)
StartTime=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")
echo "Delete 3 days before the log" >>$eMailFile
docker exec $CONTAINER mysql -u$user -p$userPWD -e "purge master logs before '${Time}'" && echo "delete 3 days before log" | tee -a $eMailFile
filename=`cat $mysqlDataDir/binlog.index |awk -F "./" '{print $2}'`
for i in $filename
do
echo "$StartTime start backup binlog" >> $eMailFile
docker exec $CONTAINER mysqlbinlog -u$user -p$userPWD -d $database --start-datetime="$StartTime" /var/lib/mysql/$i | gzip >> $daily_databakDir/daily$DATE.sql.gz | tee -a $eMailFile
done
if [ $? = 0 ]
then
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} \; > /dev/null 2>&1
cd $daily_databakDir
echo "Daily backup succeed" >> $eMailFile
else
echo "Daily backup fail" >> $eMailFile
fi
cat $eMailFile > $logFile
find $daily_databakDir -name "*.log" -type f -mtime +32 -exec rm {} \; > /dev/null 2>&1
数据库恢复
mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/binlog.000027 --result-file=mysql0002.sql
mysqlbinlog -v /var/lib/mysql/binlog.000027 --start-position=4 --stop-position=1073803484 | mysql -uroot -123456