mysql全量备份和增量备份

mysql全量备份脚本

#!/bin/bash
Data_back="/data/data_backup/";
Date=`date +"%Y_%m_%d"`
mysqldump -uroot -proot  --quick --events --flush-logs  --single-transaction  XXXX> ${Data_back}XXXX_${Date}.sql

mysql增量备份脚本

#!/bin/bash
Binlogdir="/usr/local/mysql/var/"
Binlogbackup="/data/data_binlog/"
Date=`date +"%Y_%m_%d"`
Binloglis=(`ls ${Binlogdir} | grep mysql-bin.0`)
binlognum=${#Binloglis[@]}
start_time=`date +%Y-%m-%d_%H:%M:%S`
mysql -uroot -proot -e "flush logs;"
echo -e "\n\n\n"
echo "-------------binlog_back_up start at "${start_time}"-----------------------------------"
for((i=0;i<$binlognum;i++));do
if [ ${i} == `expr ${binlognum} - 1` ]
then
        echo ${Binloglis[i]}": last file"
else
        backupfilename=${Binlogbackup}${Binloglis[i]}
        backfilename=${Binlogdir}${Binloglis[i]}
        if [ -e $backupfilename ]
        then
                echo ${backupfilename}": backup file exist"
        else
                cp $backfilename $backupfilename
                if [ -e $backupfilename ]
                then
                        echo ${backupfilename}": backup success"
                else
                        echo ${backupfilename}": backup fail"
                fi
        fi
fi
done
end_time=`date +%Y-%m-%d_%H:%M:%S`
echo "-------------binlog_back_up start at "${end_time}"-----------------------------------"

设置crontab任务，执行备份脚本。先执行的是增量备份脚本，然后执行的是全量备份脚本

#每个星期日凌晨3:00执行完全备份脚本
0 3 * * 0 /bin/bash -x /root/Mysql-FullyBak.sh >/dev/null 2>&1
#周一到周六凌晨3:00做增量备份
0 3 * * 1-6 /bin/bash -x /root/Mysql-DailyBak.sh >/dev/null 2>&1

binlog备份恢复操作

恢复数据从一个位置4到位置1285的数据
#mysqlbinlog --no-defaults --start-position="4" --stop-position="1285" /data/data_binlog/mysql-bin.000006 | mysql -u root -p

a、提取指定的binlog日志
# mysqlbinlog /opt/data/APP01bin.000001  
# mysqlbinlog /opt/data/APP01bin.000001|grep insert  
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
insert into tb values(2,'jack')
 
b、提取指定position位置的binlog日志
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001  
 
c、提取指定position位置的binlog日志并输出到压缩文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz  
 
d、提取指定position位置的binlog日志导入数据库
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p  
 
e、提取指定开始时间的binlog并输出到日志文件
# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql  
 
f、提取指定位置的多个binlog日志文件
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more  
 
g、提取指定数据库binlog并转换字符集到UTF8
# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql  
 
h、远程提取日志，指定结束时间
# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more  
 
i、远程提取使用row格式的binlog日志并输出到本地文件
# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql ~