mysql增量备份配置详情_MySQL完全备份+增量备份

mysql增量备份脚本#!/bin/bashdateDIR=$(date -d "yesterday" +"%y-%m-%d")mkdir -p /data/backup/mysql/binlog/$dateDIR##刷新缓存中的日志mysqladmin -uroot -p flush-logs#7天前的时间TIME=$(date "-d 7 day ago" +"%Y-%m-%d %H:%M:%S")#24小时前的时间StartTime=$(date "-d 1 day ago" +"%Y-%m-%d %H:%M:%S")#删除7天前的2进制文件mysql -uroot -p -e "purge master logs before '${TIME}';"#针对每个数据库for db in $(mysql -uroot -p -e "show databases" | grep -e "zabbixdatabases" -e"serverdatabases")do#针对所有的日志文件for log in $(cat /var/lib/mysql/mysql-bin.index)do#备份每个数据库24小时之前到现在时间的所有日志，写入到相应文件中mysqlbinlog -uroot -p -d $db --start-datetime="$StartTime" ${log} > /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR}.sqldone#tar jcvf /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR}.tar.bz2 /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR} 2&>/dev/null#rm -rf /data/backup/mysql/binlog/$dateDIR/${db}_${dateDIR}done#删除20天以前的增量备份文件和目录find /data/backup/mysql/binlog/* -mtime +19 -type d -exec rm -rf {} \;mysql全备份脚本1 #!/bin/bash23 date=`date +%Y-%m-%d`45 bakdir="/home/mysqlbackup"6 mvdir="/home/mysqlbackup/mv"7 logFile="/home/mysqlbackup/log/mysqlbak.log"8 user="root"9 pass="passwd"10 filename="database-$date.sql"11 filenamee="databases-$date.sql"1213 if [ ! -d $bakdir ];then mkdir -pv $bakdir;fi1415         cd $bakdir16         mv $bakdir/$filename.tgz $mvdir/17         mv $bakdir/$filenamee $mvdir/18         rm -f $bakdir/$filename1920 /home/mysql/bin/mysqldump --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --flush-logs --events --single-transaction --quick -B dabases -u$user -p$pass > $filename21 /home/mysql/bin/mysqldump --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --flush-logs --events --single-transaction --quick -B databases -u$user -p$pass >$filenamee 2223 echo "开始打包备份文件"24 echo "$filename $filenamee 数据库备份成功，备份文件在192.168.0.10:/root/home/mysqlbackup目录下;" > $logFile25 /bin/tar czvf $filename.tgz $filename2627 /bin/find $mvdir  -type f -ctime +3 -exec rm -fr {} \;28 #/bin/tar czvf $filenamee.tgz $filename29 scp $bakdir/$filename.tgz 192.168.0.10:/root/home/mysqlbackup30 scp $bakdir/$filenamee 192.168.0.10:/root/home/mysqlbackup3132        mv $bakdir/$filename.tgz $mvdir/33        mv $bakdir/$filenamee $mvdir/34        rm -f $bakdir/$filename3536 ssh 192.168.0.10 '/bin/find /root/home/mysqlbackup -type f -atime +3 -exec rm -fr {} \;'37 #只保留最近四天的数据~或者：2 # Program3 # #    use mysqldump to Fully backup mysql data per week!4 # # History5 # #    2015-04-02 jin    first6 # # path7 # #    ....8  # 备份文集的目录，日志目录，时间，用户，密码9  BakDir="/root/djback/backup"10  LogFile="/root/djback/backup/bak.log"11  Date=`date +%Y%m%d`12  Begin=`date +"%Y年%m月%d日 %H:%M:%S"`13  user="root"14  pass="passwd"15 #如果文件不存在就创建16 if [ ! -d $BakDir ];then mkdir -pv $BakDir;fi17 cd $BakDir18 #备份文件名字和压塑后的问件名19 DumpFile=data-$Date.sql20 GZDumpFile=data-$Date.sql.tgz21 DumpFilee=databa-$Date.sql22 GZDumpFilee=databa-$Date.sql.tgz23  #备份qiv库24  /home/mysql/bin/mysqldump --default-character-set=utf8  --opt --extended-insert=false --master-data=2 --triggers -R --hex-blob --flush-logs --events --single-transaction --quick  -B data -u$user -p$pass > $BakDir/$DumpFile 25 #备份qiv_secretcode库26  /home/mysql/bin/mysqldump --default-character-set=utf8  --opt --extended-insert=false --master-data=2 --triggers -R --hex-blob --flush-logs --events --single-transaction --quick  -B  databae -u$user -p$pass > $BakDir/$DumpFilee 27 sed -n "22p" $BakDir/$DumpFilee | awk -F'=|,|;' '{print $2,$4}' >$BakDir/postion28 # 压缩备份文件29 /bin/tar czvf $GZDumpFile $DumpFile3031 /bin/tar czvf $GZDumpFilee $DumpFilee32 # 删除以前的备份文件33 /bin/rm $DumpFile34 Last=`date +"%Y年%m月%d日 %H:%M:%S"`35 echo "开始:$Begin 结束:$Last $GZDumpFile succ36 sleep 237 echo "(备份成功 备份的目录为/root/djback/backup下)"38 cd $BakDir/daily39 rm -f *40 echo "开始上传文件到192.168.0.10的/root/home/mysqlbackup/下"41 scp /root/djback/$DumpFile  192.168.0.10:/root/home/mysqlbackup/42 scp /root/djback/$DumpFilee 192.168.0.10:/root/home/mysqlbackup/43 echo "上传成功！"