MySQL-利用binlog恢复数据

MySQL-利用binlog恢复数据

    这一篇文章里，我们来记录使用mysql-binlog来恢复数据的整个过程 

    一、前期准备

    1、建库建表
    创建数据库blog以及下面的article文章表

 create table if not exists `article` (
`id` int(11) not null auto_increment,
`title` varchar(128) not null COMMENT '文章标题',
`content` text not null COMMENT '文章内容',
`status` int(11) not null default '1' COMMENT '文章状态 0-下架 1-上架',
`create_time` int(11) COMMENT '创建时间',
`update_time` int(11) default '0' COMMENT '修改时间',
primary key (`id`)
) engine = InnoDB default CHARSET = utf8mb4 COMMENT = '文章详情表';

     插入数据：

insert into  article(`title`,`content`,`create_time`)
values ('Yii2小部件详解','测试1',unix_timestamp(now())),
('查询构建器','测试2',unix_timestamp(now())),
('使用 Gii 生成代码','测试3',unix_timestamp(now()));

      现在看下数据表中的数据：

      

     2. 备份完整的数据库

     假设现在是凌晨2点，定时任务开始执行一次完整的数据库备份

     将blog数据库备份到 /root/bak.blog.sql 文件中，执行以下命令：

    mysqldump -uroot -p123456  -F -B blog >/root/bak.blog.sql

    注意该命令中使用了 -F 选项，当备份工作刚开始时系统会刷新log日志，产生新的binlog日志来记录备份之后的数据库“增删改”操作，查看一下：

    

   备份完成后，陆续执行更新article表中数据的命令：

   update blog.article set content='测试1更改' where id=1;
   update blog.article set content='测试2更改' where id=2;
   update blog.article set content='测试3更改' where id=3;

   再过了一会儿，莫名地执行了一条悲催的SQL语句，整个数据库都没了

   drop database blog;

   这下怎么办，删库跑路? 还是冷静分析，一个人默默地承担所有。。。

   

     先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)

     show master logs;

     

     二、恢复数据

    1. 备份最后一个日志文件：

     执行下面命令：
    ll /www/server/data | grep mysql-bin
    cp -v /www/server/data/mysql-bin.000027 /root/

    

   2. 执行刷新日志索引操作

  重新开始新的binlog日志记录文件，这个时候mysql-bin.000027 这个文件不会再有后续写入了(便于我们分析原因及查找pos点)，以后所有数据库操作都会写入到下一个日志文件；
  flush logs;
  show master status;

  3. 读取binlog日志，分析问题
   show binlog events in 'mysql-bin.000027' limit 100
   通过分析，造成数据库破坏的pos点区间是介于 1567--1723 之间，只要恢复到1567前就可以啦

   

   4.  将之前凌晨2点备份的数据恢复

   /www/server/mysql/bin/mysql -uroot -p123456  -v  < /root/bak.blog.sql

   5. 从binlog日志恢复数据

   方式1： 直接恢复到1567前

   /www/server/mysql/bin/mysqlbinlog --stop-position=1567 --database=blog /www/server/data/mysql-bin.000027  | /www/server/mysql/bin/mysql -uroot -p123456 -v blog

   方式2： 指定pos点区间恢复

   此pos结束点介于导入数据与更新之间，这样可以恢复到更改"content='测试1更改'"之前的“导入测试数据”

   /www/server/mysql/bin/mysqlbinlog --stop-position=646 --database=blog /www/server/data/mysql-bin.000027  | /www/server/mysql/bin/mysql -uroot -p123456 -v blog

   说明：这里实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径

  

    1）更新 content='测试1更改' 这条数据

    日志区间是Pos[725] --> End_log_pos[922]，按事务区间是：Pos[646] --> End_log_pos[953]；

  方法1：单独恢复 content='测试1更改' 这步操作，可这样：
  /www/server/mysql/bin/mysqlbinlog --start-position=725 --stop-position=922 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog

  方法2：也可以按事务区间单独恢复，如下：
  /www/server/mysql/bin/mysqlbinlog --start-position=646 --stop-position=953 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v blog

 

    2）更新 content='测试2更改' 这条数据

   日志区间是Pos[1032] --> End_log_pos[1229]，按事务区间是：Pos[953] --> End_log_pos[1260]；

   方法1：单独恢复 content='测试2更改' 这步操作，可这样：
  /www/server/mysql/bin/mysqlbinlog --start-position=1032 --stop-position=1229 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456  -v blog

   方法2：也可以按事务区间单独恢复，如下：
  /www/server/mysql/bin/mysqlbinlog --start-position=953 --stop-position=1260 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456  -v blog

    3）更新 content='测试3更改' 这条数据

    日志区间是Pos[1339] --> End_log_pos[1536]，按事务区间是：Pos[1260] --> End_log_pos[1567]；

   方法1：单独恢复 content='测试1更改' 这步操作，可这样：
  /www/server/mysql/bin/mysqlbinlog --start-position=1339 --stop-position=1536 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot -p123456 -v  blog

   方法2：也可以按事务区间单独恢复，如下：
  /www/server/mysql/bin/mysqlbinlog --start-position=1260 --stop-position=1567 --database=blog /www/server/data/mysql-bin.000027 | /www/server/mysql/bin/mysql -uroot  -p123456  -v blog

      恢复完成，我们再看下blog中article表中的数据：

 

    我们看到：所谓的数据恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已