从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件

MYSQLdump全备和增量备份

发布:蔺要红08-29分类: MYSQL


全量备份和增量备份

mysqldump命令详解              https://www.linyaohong.com/blog/mysql/230.html
mysql binlog开启以及详细说明   https://www.linyaohong.com/blog/mysql/250.html

常见备份方案
1.只有一个主库,深夜访问低谷,锁表进行全量备份,定时进行增量备份
1.一主多从,某一个不对外服务的从库上开启binlog,进行定时全备和增量备份

增量恢复: 利用二进制日志和全备进行恢复的过程,称为增量恢复

    1.前提是必须开启binlog
    2.有全备文件
    3.有全备文件之后的时候到出问题时刻的所有增量binlog文件

人为或者程序执行SQL语句等误操作,才需要增量恢复,此时所有的从库也执行了误操作语句
如果物理故障或SQL故障,直接切到从库即可,无需恢复

全量备份
# 备份库为:linyaohong

mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -F -R --triggers --single-transaction --master-data=2 --events  linyaohong|gzip >/backup/all.sql.gz

# --hex-blob  如果数据库中有blob字段或者其他大字段,需要加上此参数,否则数据会丢失
# 顺便测试了一下排除test表,测试成功
mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -F -R --single-transaction --master-data=2 --triggers --events --ignore-table=linyaohong.test linyaohong|gzip >/backup/all.sql.gz

增量备份
#!/bin/bash
BakDir=/backup/
BinDir=/data/3306/
LogFile=/backup/bak.log
BinFile=/data/3306/mysql-bin.index
mysqladmin -uroot -p111111 -S /data/3306/mysql.sock flush-logs
Counter=`wc -l $BinFile|awk '{print $1}'`
NextNum=0
for file in `cat $BinFile`
do 
    base=`basename $file`
    NextNum=`expr $NextNum + 1`
    if [ $NextNum -eq $Counter ]
    then
        echo $base skip! >> $LogFile
    else
        dest=$BakDir/$base
        if (test -e $dest)
        then
            echo $base exist! >> $LogFile
        else
            cp $BinDir/$base $BakDir/
            echo $base copying >> $LogFile
        fi
    fi
done
echo `date +"%Y年%m月%d日 %H:%M:%S"` Bakup successful>> $LogFile

全量备份和增量备份恢复
gunzip < /backup/all.sql.gz|mysql -uroot  -p111111 -S /data/3306/mysql.sock   # 首先恢复全量

# 第二步也最最重要的一步,确定需要恢复哪些增量备份,(或改下脚本按天存放)
gzip -cd all.sql.gz > all.sql # 保留源文件解压全备数据
[root@test01 /backup]# grep CHANGE all.sql    # 可以确的是要备份的库,备份后是从mysql-bin.000003开始记录
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

# 此时的状态是数据库删除了一些数据(恢复的话,先暂停服务,禁止对外提供服务,否则很难处理恢复过程中新增加的数据)

# 第三步-手动执行增量备份,会把当前的binlog以及未来得及增量备份的binlog复制过来
  也可以手动把未备份的binlog拷贝过来(一定不要进行二次破坏,做任何事情都不能在源文件操作,)

# 第四步,处理binlog日志(假如刷新后的mysql-bin为0008),那么可以确定的是 mysql-bin.000003一直到 mysql-bin.000007为需要恢复的数据(并且还要处理删除语句)
mysqlbinlog -d linyaohong mysql-bin.000003 > bin.sql # 测试的没有3-7这么多binlog,只有1个,多个同理

删除bin.sql里包含DELETE的语句(前提是知道删了那些内容)
把找出的语句删除(如下文展示)

DELETE FROM `linyaohong`.`test` WHERE `id` = 134872
/*!*/;
# at 743
#190830  1:06:43 server id 1  end_log_pos 770 	Xid = 1497
COMMIT/*!*/;
# at 770
#190830  1:06:43 server id 1  end_log_pos 844 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1567098403/*!*/;
BEGIN
/*!*/;
# at 844
#190830  1:06:43 server id 1  end_log_pos 964 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1567098403/*!*/;
DELETE FROM `linyaohong`.`test` WHERE `id` = 134873
/*!*/;
# at 964

# 恢复binlog日志
 mysql -uroot  -p111111 -S /data/3306/mysql.sock  < bin.sql 
# 最后验证数据库
 
温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站