mysql备份还原-基于binlog的增量备份还原

来源:本站原创 mysql_mariadb 超过217 views围观 0条评论

 

启用binlog

vi my.cnf

log-bin=/var/lib/mysql/mysql-bin.log,如果是这样的话log-bin=mysql-bin.log默认在datadir目录下面

[root@BlackGhost mysql]# ls |grep mysql-bin
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
mysql-bin.000006
mysql-bin.index

 

查看mysql-bin.000002这样的文件里面到底是什么东西

[root@BlackGhost mysql]# mysqlbinlog   /var/lib/mysql/mysql-bin.000002 > /tmp/add.sql

binlog增量备份和增量还原

1,增量备份

既然我们知道了,mysql里面新增加的数据在mysql-bin这样的文件里面,我们只要把mysql-bin这样的文件进行备份就可以了。

cp /var/lib/mysql/mysql-bin* /data/mysql_newbak/

 

我们现在要结合Binlog来恢复,但前提要找出误操作前的pos点

 

通过事件的位置来恢复(不完全恢复)

 

  1. [root@localhost ~]# mysqlbinlog -v –base64-output=DECODE-ROWS localhost-bin.000002 |grep -C 10 -i "drop database"

  2. ### INSERT INTO `xuanzhi`.`tb1`

  3. ### SET

  4. ### @1=5

  5. ### @2=’ee’

  6. # at 290

  7. #170327 21:10:55 server id 1313306 end_log_pos 321 CRC32 0x825a2f99 Xid = 78

  8. COMMIT/*!*/;

  9. # at 321  <–开始

  10. #170327 21:19:25 server id 1313306 end_log_pos 422   <–结束点 CRC32 0x8c139cac Query thread_id=2 exec_time=0 error_code=0

  11. SET TIMESTAMP=1490620765/*!*/;

  12. drop database xuanzhi

 

上面的黄色加粗的就是,一个是start-position,一个是stop-position

 

从上面可以看到,误操作前的pos点是321,那我们现在通过binlog来进行数据恢复:

  1. [root@localhost mysql-5.6]# mysqlbinlog –start-position=329 –stop-position=321 localhost-bin.000001 localhost-bin.000002 |mysql -uroot -p123456 xuanzhi

–start-position是备份后记录下的pos点,

–stop-position是误操前的pos点,

如果多个binlog文件,那么start-position是第一个binlog文件的pos点,stop-position是最后一个binlog的pos点

 

通过事件的时间来恢复(不完全恢复)

我们可以通过参数–start-datetime–stop-datetime指定恢复binlog日志的起止时间点,时间使用DATETIME格式。

    比如在时间点2005-04-20 10:00:00我们删除掉一个库,我们要恢复该时间点前的所有日志

[root@localhost /]# mysqlbinlog –stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root

    我们可能几个小时后才发现该错误,后面又有一系列的增删查改等操作,我们还需要恢复后续的binlog,我们可以指定起始时间

 

组合

和基于时间点恢复类是,但是更加精确.因为同一时间点可能有多条SQL语句执行;

例:

#mysqlbinlog –start-date="2010-10-31 9:55:00"  –stop-date="2010-10-31 10:05:00" /usr/local/mysql/var/mysql-bin.000013 > /tmp/mysql_restore.sql

该命令将在/tmp/目录下创建小的文件,编辑它找到错误语句前后的位置号,例如前后位置号分别是368312 和 368315

(2)恢复了以前的备份文件后,输入

#mysqlbinlog –stop-position="368312" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

#mysqlbinlog –start-position="368315" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot –p

 

总结:

        一、在恢复全备数据之前必须将该binlog文件移出,否则恢复过程中,会继续写入语句到binlog,最终导致增量恢复数据部分变得比较混乱

        二、做好数据文件及binlog的备份至关重要,但不是备份完就算了,要定期进行数据恢复测试或演练

        三、恢复时建议对外停止更新,即禁止更新数据库

文章出自:CCIE那点事 http://www.jdccie.com/ 版权所有。本站文章除注明出处外,皆为作者原创文章,可自由引用,但请注明来源。 禁止全文转载。
本文链接:http://www.jdccie.com/?p=4187转载请注明转自CCIE那点事
如果喜欢:点此订阅本站