mysql增量备份以及日志修复方法

Song • 54次浏览 • 0个评论 • 2018-12-25 09:06:12

一、准备增量测试数据

  • a、准备第一份增量数据
    mysql> create database backup_test;
    mysql> use backup_test;
    mysql> create table test (c1 int(10), c2 varchar(20)) engine=innodb;
    mysql> insert into test values (11, 'test1'),(12, 'test2'),(13, 'test3'),(14, 'test4'),(15, 'test5');
  • b、备份前需要将数据库加读锁,防止数据在备份时写入。
    mysql> flush tables with read lock;
  • c、通过命令flush logs;log日志刷盘,写入当前binlog(mysql-bin.000002),在生成一个新的binlog(mysql-bin.000003)为下次增备做准备。
    mysql> flush logs;
  • d、将binlog第一个增备文件mysql-bin.000002直接复制保存即可。也可以将二进制文件导出到文本文件保存,在linux命令行下执行
    cd mysql-bin文件位置
    mysqlbinlog --no-defaults mysql-bin.000002 > ~/test1.sql
  • e、解除表锁。
    mysql> unlock tables;

    至此第一个增量备份全部结束,将增量binlog文件mysql-bin.000002或者有binlog文件导出的文本文件~/test1.sql保存即可。数据库再有新的数据更新会记录在新的binlog(mysql-bin.000003)里面。

二、binlog恢复操作:

如上我们可以随意修改test中的数据以方便进行还原测试,你也可以直接清空,删除表:

1、使用binlog直接还原

mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -p123

执行后你会发现数据恢复到了你删除之前,因为我们提前flush logs;了存储文件,所以可以正常恢复。但是生活不是演习,你常常会在一不小心删除了数据以后才发现问题,即日志存储文件还是位于mysql-bin.000001,如果执行一次上面的命令,你会发现你的数据并没有恢复。WHY? 因为其实你已经恢复了,但是你又把它删除了。

此时我们需要使用mysqlbinlog的时间和位置界定参数:

  • --start-datetime:恢复的开始时间
  • --stop-datetime:恢复的结束时间
  • --start-position:恢复开始的位置
  • --stop-position:恢复结束的位置

使用以上的参数,我们可以恢复到删除前一秒:

mysqlbinlog --no-defaults --start-datetime='2016-01-04 15:11:00' --stop-datetime='2016-01-04 15:11:00' mysql-bin.000001 | mysql -uroot -p123

或者执行我们删除之前的代码:

mysqlbinlog --no-defaults --start-position=249 --stop-position=249 mysql-bin.000001 | mysql -uroot -p123

使用--database参数,从binlog日志中恢复单个库:

mysqlbinlog --database=test mysql-bin.000001 |mysql -uroot -p123

如上命令使用mysqlbinlog恢复指定的test数据库,只限本地log日志。

不常用选项:

  • -u --user=name日志恢复到的服务器账号
  • -p --password[=name]日志恢复到的服务器密码
  • -h --host=name日志恢复到的服务器IP,本机不用填写
  • --read-from-remote-server从某个MySQL服务器上读取binlog日志

2、通过binlog文本文件source还原

上面我们已经通过~/test1.sql导出了日志文件,使用mysqlbinlogbinlog日志导出成文本文件,使用source执行该文件:

source ~/test1.sql;

常见错误解决办法:

1、binlog恢复数据会出现重复创建数据么?

答案是不会,在恢复过程中如果出现重复创建数据记录,将会出现Duplicate entry '2' for key 'PRIMARY'这样的错误并且结束程序执行。

2、如何在报错的情况下继续执行数据

有时候我们不记得开始和结束时间,希望直接执行而不是遇到错误停止:

  • --force-read, -f

使用此选项,如果mysqlbinlog读取它无法识别的二进制日志事件,它将输出警告,忽略该事件,然后继续。如果没有此选项,mysqlbinlog会在读取此类事件时停止。

例:

mysqlbinlog --no-defaults --start-datetime='2018-12-26 14:31:00' --stop-datetime='2018-12-26 14:38:00' mysql-bin.000001 | mysql -uroot -p123 -f

更多操作:MySQL数据库备份还原(基于binlog的增量备份)

提交评论
要回复文章请先登录注册
用户评论
  • 没有评论
    公告
    中国晚上好,随时随地分享前沿科技,好吃好玩,有趣有料的新鲜事;本站可以分享资源,技术,观点,态度;同时希望大家做一个文明的分享者!
    广告
    pytorch中文网