文档章节

Mysql使用binlog恢复数据解决误操作问题的两种方法

Sgmder
 Sgmder
发布于 2018/03/07 16:46
字数 969
阅读 1093
收藏 2

>新搭建的个人博客,欢迎光临<

为保证没有其他参数配置影响,重新安装配置了一台最小化安装的CentOS7虚拟机

1. 基础知识

安装mysql5.6数据库 https://my.oschina.net/sgmder/blog/1631045

Mysql binlog初步理解 https://my.oschina.net/sgmder/blog/1631432

 

2. 配置mysql,开启binlog、修改binlog模式为Row Level模式

vi /etc/my.cnf 

修改mysql配置文件,在[mysqld]下增加以下内容

# 注释: 开启binlog 文件名以mysql-bin开头
log-bin = mysql-bin
# 注释: 备份恢复模式不需要开启Row模式 闪回需要开启Row模式
binlog_format="ROW"

重启mysql数据库 binlog开启 生成文件/var/lib/mysql/mysql-bin.000001

service mysqld restart

登录数据库 然后创建测试数据库demo和测试表user

mysql> create database demo;
Query OK, 1 row affected (0.00 sec)
mysql> use demo;
Database changed
mysql> CREATE TABLE `user` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NULL, `type` int(8) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

准备完毕,刷新掉binlog文件,此时binlog文件mysql-bin.000001结束,进入mysql-bin.000002记录

mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

备份数据库

[root@localhost ~]# mysqldump -u root -p --databases demo > db_demo_bak.sql

3. 使用备份+binlog记录方式回滚数据库

进入数据库 先插入2条测试数据

mysql> insert into user (id,name,type) value (10001,'A','1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id,name,type) value (10002,'B','1');
Query OK, 1 row affected (0.00 sec)

查看数据

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | A    |    1 |
| 10002 | B    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

模拟误操作 update修改数据库数据

mysql> update user set name = 'C';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | C    |    1 |
| 10002 | C    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

user表的name字段被误操作修改,抓紧刷新掉binlog文件,mysql-bin.000002结束,后续操作进入mysql-bin.000003

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)

此时,查看一下mysql的binlog文件

[root@localhost ~]# ls /var/lib/mysql
auto.cnf  ibdata1      ib_logfile1  mysql-bin.000001  mysql-bin.000003  mysql.sock
demo      ib_logfile0  mysql        mysql-bin.000002  mysql-bin.index   performance_schema

那么,mysql-bin.000002文件记录了从备份到误操作的所有数据库执行,现在我们需要恢复掉被update误操作掉的数据

先使用备份恢复数据库 此时表user没有数据

mysql> user demo;
mysql> drop table user;
mysql> source /root/db_demo_bak.sql
mysql> select * from user;
Empty set (0.00 sec)

查看mysql-bin.000002记录的操作,查看各操作的Pos和End_log_pos

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.39-log, Binlog ver: 4 |
| mysql-bin.000002 | 120 | Query       |         1 |         192 | BEGIN                                 |
| mysql-bin.000002 | 192 | Table_map   |         1 |         243 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 243 | Write_rows  |         1 |         289 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 289 | Xid         |         1 |         320 | COMMIT /* xid=147 */                  |
| mysql-bin.000002 | 320 | Query       |         1 |         392 | BEGIN                                 |
| mysql-bin.000002 | 392 | Table_map   |         1 |         443 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 443 | Write_rows  |         1 |         489 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 489 | Xid         |         1 |         520 | COMMIT /* xid=148 */                  |
| mysql-bin.000002 | 520 | Query       |         1 |         592 | BEGIN                                 |
| mysql-bin.000002 | 592 | Table_map   |         1 |         643 | table_id: 72 (demo.user)              |
| mysql-bin.000002 | 643 | Update_rows |         1 |         723 | table_id: 72 flags: STMT_END_F        |
| mysql-bin.000002 | 723 | Xid         |         1 |         754 | COMMIT /* xid=149 */                  |
| mysql-bin.000002 | 754 | Rotate      |         1 |         801 | mysql-bin.000003;pos=4                |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
14 rows in set (0.00 sec)

120 - 320 第一次insert

320 - 520 第二次insert

520 - 754 误操作update

使用mysqlbinlog工具恢复insert操作 120 - 520 

[root@localhost ~]# mysqlbinlog --start-position=120 --stop-position=520 --database=demo /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -u root -p mima666 -v demo

mysqlbinlog的详细使用方法参看官方文档 https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html

查看下user表数据

mysql> select * from user;
+-------+------+------+
| id    | name | type |
+-------+------+------+
| 10001 | A    |    1 |
| 10002 | B    |    1 |
+-------+------+------+
2 rows in set (0.00 sec)

Ok,数据恢复成功

4. 使用binlog+MyFlash闪回方式回滚数据库

参考美团的开源项目MyFlash

https://gitee.com/mirrors/MyFlash

https://github.com/Meituan-Dianping/MyFlash

© 著作权归作者所有

Sgmder
粉丝 6
博文 11
码字总数 4513
作品 0
合肥
程序员
私信 提问
DRDS 数据恢复重磅发布,全方位保障您的数据安全

背景介绍 数据库存储着企业的核心数据,在企业中占据非常重要的位置,一旦出现SQL注入,数据误删的情况,影响的不仅仅是业务,还会泄露用户的个人信息。因此,数据库的数据安全问题十分重要。...

zhaowei121
05/28
10
0
MySQL阶段六——MySQL备份与增量备份

MySQL备份与增量备份 01)MySQL备份常用参数 myisam引擎压根不支持事务,所以不能用--single-transaction操作;myisam实现数据一致采用参数--lock-all-tables,这样在flush tables with read ...

花开半夏qb
2017/05/27
0
0
MySQL 备份和恢复机制

一、 备份恢复策略 进行备份或恢复操作时需要考虑一些因素: 确定要备份的表的存储引擎是事务型还是非事务型,两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。 确定使用全备份...

waterandair
2017/11/13
0
0
mysql数据库误删除后的数据恢复操作说明

mysql数据库误删除后的数据恢复操作说明 在日常运维工作中,对于数据库的备份是至关重要的!数据库对于网站的重要性使得我们对 MySQL 数据库的管理不容有失!然而是人总难免会犯错误,说不定...

MQ_douer
2017/06/10
0
0
mysql数据误删恢复

mysql环境:ubuntu; 1.首先确定是否开启日志功能; 如果你的log_bin选项为OFF,此种方法无法进行数据修复,这里做学习恢复数据使用,了解binlog恢复数据流程; 2.若未开启log_bin,需要进行...

NotFoundException
2016/11/15
74
0

没有更多内容

加载失败,请刷新页面

加载更多

一起来学Java8(四)——复合Lambda

在一起来学Java8(二)——Lambda表达式中我们学习了Lambda表达式的基本用法,现在来了解下复合Lambda。 Lambda表达式的的书写离不开函数式接口,复合Lambda的意思是在使用Lambda表达式实现函...

猿敲月下码
20分钟前
6
0
debian10使用putty配置交换机console口

前言:Linux的推广普及,需要配合解决实际应用方能有成效! 最近强迫自己用linux进行实际工作,过程很痛苦,还好通过网络一一解决,感谢各位无私网友博客的帮助! 系统:debian10 桌面:xfc...

W_Lu
52分钟前
10
0
aelf Enterprise 0.8.0 beta有奖公测,“Bug奖金计划”重磅开启

2019年9月30日,aelf Enterprise 0.8.0 beta版正式发布。aelf Enterprise 0.8.0 beta是一个完备的区块链系统, 包含完备的区块链系统、开发套件、开发文档、以及配套的基础应用和基础服务。 ...

AELF开发者社区
53分钟前
10
0
oracle 初始化数据库脚本

create user lpf identified by 123456; create tablespace lpf_ts_cms datafile '/opt/app/oracle/product/11.2.0/lpf.dbf' size 200M; alter user lpf default tablespace lpf_ts_cms; sel......

internetafei
58分钟前
8
0
深入了解Redis底层数据结构

说明 说到Redis的数据结构,我们大概会很快想到Redis的5种常见数据结构:字符串(String)、列表(List)、散列(Hash)、集合(Set)、有序集合(Sorted Set),以及他们的特点和运用场景。不过它们是...

TurboSanil
58分钟前
7
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部