mysqlbinlog怎么恢复数据

Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

众所周知，binlog日志对于mysql数据库来说是十分重要的。在数据丢失的紧急情况下，我们往往会想到用binlog日志功能进行数据恢复（定时全备份+binlog日志恢复增量数据部分），化险为夷！

一、简单了解binlog

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

===========================================================

DDL

- Data Definition Language 数据库定义语言

主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML

- Data Manipulation Language 数据操纵语言

主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

===========================================================

mysqlbinlog常见的选项有以下几个：

--start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间

--stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间取值和上述一样

--start-position：从二进制日志中读取指定position 事件位置作为开始。

--stop-position：从二进制日志中读取指定position 事件位置作为事件截至

===========================================================

一般来说开启binlog日志大概会有1%的性能损耗。

binlog日志有两个最重要的使用场景

1）MySQL主从复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves来达到

master-slave数据一致的目的。

2）自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

binlog日志包括两类文件

1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件

2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

二、开启binlog日志功能

1）编辑打开mysql配置文件/etc/mys.cnf

[root@vm-002 ~]# vim /etc/my.cnf

在[mysqld] 区块添加

log-bin=mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名)

注意：每次服务器（数据库）重启，服务器会调用flush logs;，新创建一个binlog日志！

2）重启mysqld服务使配置生效

[root@vm-002 ~]# /etc/init.d/mysqld stop

[root@vm-002 ~]# /etc/init.d/mysqld restart

Stopping mysqld: [ OK ]

Starting mysqld: [ OK ]

3）查看binlog日志是否开启

mysql> show variables like 'log_%';

+---------------------------------+---------------------+

| Variable_name | Value |

+---------------------------------+---------------------+

| log_bin | ON |

| log_bin_trust_function_creators | OFF |

| log_bin_trust_routine_creators | OFF |

| log_error | /var/log/mysqld.log |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_queries | OFF |

| log_warnings | 1 |

+---------------------------------+---------------------+

9 rows in set (0.00 sec)

三、常用的binlog日志操作命令

1）查看所有binlog日志列表

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 149 |

| mysql-bin.000002 | 4102 |

+------------------+-----------+

2 rows in set (0.00 sec)

2）查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000002 | 4102 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

3）flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

Query OK, 0 rows affected (0.13 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 149 |

| mysql-bin.000002 | 4145 |

| mysql-bin.000003 | 106 |

+------------------+-----------+

3 rows in set (0.00 sec)

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4）重置(清空)所有binlog日志

mysql> reset master;

Query OK, 0 rows affected (0.12 sec)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 106 |

+------------------+-----------+

1 row in set (0.00 sec)

四、查看binlog日志内容，常用有两种方式

1）使用mysqlbinlog自带查看命令法：

注意：

-->binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看

-->binlog日志与数据库文件在同目录中

-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项

查看mysql的数据存放目录，从下面结果可知是/var/lib//mysql

[root@vm-002 ~]# ps -ef|grep mysql

root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql

mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock

root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx

root 9919 9715 0 21:23 pts/1 00:00:00 grep --color mysql

[root@vm-002 ~]# cd /var/lib/mysql/

[root@vm-002 mysql]# ls

ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test

使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：

[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002

..............

# at 624

#160925 21:29:53 server id 1 end_log_pos 796 Querythread_id=3exec_time=0error_code=0

SET TIMESTAMP=1474810193/*!*/;

insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')#执行的sql语句

/*!*/;

# at 796

#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17#执行的时间

.............

解释：

server id 1 ：数据库主机的服务号；

end_log_pos 796： sql结束时的pos节点

thread_id=11：线程号

2）上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息

下面介绍一种更为方便的查询命令：

命令格式：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,] ：偏移量(不指定就是0)

row_count ：查询总条数(不指定就是所有行)

mysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 125 |

| mysql-bin.000002 | 823 |

+------------------+-----------+

2 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002'\G;

*************************** 1. row ***************************

Log_name: mysql-bin.000002

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 106

Info: Server ver: 5.1.73-log, Binlog ver: 4

*************************** 2. row ***************************

Log_name: mysql-bin.000002

Pos: 106

Event_type: Query

Server_id: 1

End_log_pos: 188

Info: use `ops`; drop table customers

*************************** 3. row ***************************

Log_name: mysql-bin.000002

Pos: 188

Event_type: Query

Server_id: 1

End_log_pos: 529

Info: use `ops`; CREATE TABLE IF NOT EXISTS `member` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(16) NOT NULL,

`sex` enum('m','w') NOT NULL DEFAULT 'm',

`age` tinyint(3) unsigned NOT NULL,

`classid` char(6) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

*************************** 4. row ***************************

Log_name: mysql-bin.000002

Pos: 529

Event_type: Query

Server_id: 1

End_log_pos: 596

Info: BEGIN

*************************** 5. row ***************************

Log_name: mysql-bin.000002

Pos: 596

Event_type: Intvar

Server_id: 1

End_log_pos: 624

Info: INSERT_ID=1

*************************** 6. row ***************************

Log_name: mysql-bin.000002

Pos: 624

Event_type: Query

Server_id: 1

End_log_pos: 796

Info: use `ops`; insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')

*************************** 7. row ***************************

Log_name: mysql-bin.000002

Pos: 796

Event_type: Xid

Server_id: 1

End_log_pos: 823

Info: COMMIT /* xid=17 */

7 rows in set (0.00 sec)

ERROR:

No query specified

mysql>

上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！

如下操作示例：

a）查询第一个(最早)的binlog日志：

mysql> show binlog events\G;

b）指定查询 mysql-bin.000002这个文件：

mysql> show binlog events in 'mysql-bin.000002'\G;

c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起：

mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

五、利用binlog日志恢复mysql数据

以下对ops库的member表进行操作

mysql> use ops；

mysql> CREATE TABLE IF NOT EXISTS `member` (

-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

-> `name` varchar(16) NOT NULL,

-> `sex` enum('m','w') NOT NULL DEFAULT 'm',

-> `age` tinyint(3) unsigned NOT NULL,

-> `classid` char(6) DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.10 sec)

mysql> show tables;

+---------------+

| Tables_in_ops |

+---------------+

| member |

+---------------+

1 row in set (0.00 sec)

mysql> desc member;

+---------+---------------------+------+-----+---------+----------------+

+---------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| sex | enum('m','w') | NO | | m | |

| age | tinyint(3) unsigned | NO | | NULL | |

+---------+---------------------+------+-----+---------+----------------+

5 rows in set (0.00 sec)

事先插入两条数据

mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');

Query OK, 2 rows affected (0.08 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

+----+-----------+-----+-----+---------+

2 rows in set (0.00 sec)

下面开始进行场景模拟：

1）

ops库会在每天凌晨4点进行一次完全备份的定时计划任务，如下：

[root@vm-002 ~]# crontab -l

0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

这里手动执行下，将ops数据库备份到/opt/backup/ops_$(date +%F).sql.gz文件中：

[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

Enter password:

[root@vm-002 ~]# ls /opt/backup/

ops_2016-09-25.sql.gz

-----------------

参数说明：

-B：指定数据库

-F：刷新日志

-R：备份存储过程等

-x：锁表

--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

-----------------

待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！

由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生

一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作

查看一下：

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | 106 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。

2）

早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。

比如：在ops库下member表内插入、修改了数据等等：

先是早上进行插入数据：

mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6');

Query OK, 5 rows affected (0.08 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | xiaoer | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

3）

中午又执行了修改数据操作：

mysql> update ops.member set name='李四' where id=4;

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update ops.member set name='小二' where id=2;

Query OK, 1 row affected (0.06 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | 小二 | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | 李四 | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

4）

在下午18:00的时候，悲剧莫名其妙的出现了！

手贱执行了drop语句，直接删除了ops库！吓尿！

mysql> drop database ops;

Query OK, 1 row affected (0.02 sec)

5）

这种时候，一定不要慌张！！！

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

先备份一下最后一个binlog日志文件：

[root@vm-002 ~]# cd /var/lib/mysql/

[root@vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/

`mysql-bin.000003' -> `/opt/backup/mysql-bin.000003'

[root@vm-002 mysql]# ls /opt/backup/

mysql-bin.000003 ops_2016-09-25.sql.gz

接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003

这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件。

mysql> flush logs;

Query OK, 0 rows affected (0.13 sec)

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000004 | 106 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

6）

读取binlog日志，分析问题。

读取binlog日志的方法上面已经说到。

方法一：使用mysqlbinlog读取binlog日志：

[root@vm-002 ~]# cd /var/lib/mysql/

[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003

方法二：登录服务器，并查看(推荐此种方法)

mysql> show binlog events in 'mysql-bin.000003';

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

| mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 |

| mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN |

| mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID=3 |

| mysql-bin.000003 | 201 | Query | 1 | 444 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`gsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') |

| mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ |

| mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN |

| mysql-bin.000003 | 538 | Query | 1 | 646 | use `ops`; update ops.member set name='李四' where id= |

| mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ |

| mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN |

| mysql-bin.000003 | 740 | Query | 1 | 848 | use `ops`; update ops.member set name='小二' where id= |

| mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid=69 */ |

| mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops |

| mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos=4 |

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+

13 rows in set (0.00 sec)

或者：

mysql> show binlog events in 'mysql-bin.000003'\G;

.........

*************************** 12. row ***************************

Log_name: mysql-bin.000003

Pos: 875

Event_type: Query

Server_id: 1

End_log_pos: 954

Info: drop database ops

*************************** 13. row ***************************

Log_name: mysql-bin.000003

Pos: 954

Event_type: Rotate

Server_id: 1

End_log_pos: 997

Info: mysql-bin.000004;pos=4

13 rows in set (0.00 sec)

通过分析，造成数据库破坏的pos点区间是介于 875--954 之间（这是按照日志区间的pos节点算的），只要恢复到875前就可。

7）

先把凌晨4点全备份的数据恢复：

[root@vm-002 ~]# cd /opt/backup/

[root@vm-002 backup]# ls

mysql-bin.000003 ops_2016-09-25.sql.gz

[root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz

[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql

Enter password:

--------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */

--------------

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */

--------------

.............

--------------

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */

--------------

这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。

mysql> show databases;#发现ops库已经恢复回来了

mysql> use ops;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+---------------+

| Tables_in_ops |

+---------------+

| member |

+---------------+

1 row in set (0.00 sec)

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

+----+-----------+-----+-----+---------+

2 rows in set (0.00 sec)

mysql>

但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--18:00之间的数据还没有恢复回来！！

怎么办呢？

莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。

8）

从binlog日志恢复数据

恢复命令的语法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码数据库名

--------------------------------------------------------

常用参数选项解释：

--start-position=875 起始pos点

--stop-position=954 结束pos点

--start-datetime="2016-9-25 22:01:08" 起始时间点

--stop-datetime="2019-9-25 22:09:46" 结束时间点

--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)

--------------------------------------------------------

不常用选项：

-u --user=name 连接到远程主机的用户名

-p --password[=name] 连接到远程主机的密码

-h --host=name 从远程主机上获取binlog日志

--read-from-remote-server 从某个MySQL服务器上读取binlog日志

--------------------------------------------------------

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

a）完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)

[root@vm-002 backup]# cp /var/lib/mysql/mysql-bin.000003 /opt/backup

[root@vm-002 backup]# mysqlbinlog /opt/backup/mysql-bin.000003 > /opt/backup/000003.sql

[root@vm-002 backup]# vim /opt/backup/000003.sql #删除里面的drop语句

[root@vm-002 backup]# mysql -uroot -p -v < /opt/backup/000003.sql

温馨提示：

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

可参考：https://www.cnblogs.com/kevingrace/p/5904800.html

b）指定pos结束点恢复(部分恢复)：

--stop-position=471 pos结束节点（按照事务区间算，是471）

注意：

此pos结束节点介于“member表原始数据”与更新“name='李四'”之前的数据，这样就可以恢复到更改“name='李四'”之前的数据了。

操作如下：

[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | xiaoer | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

恢复截止到更改“name='李四'”之间的数据（按照事务区间算，是673）

[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | 李四 | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

c）指定pso点区间恢复(部分恢复)：

更新 name='李四' 这条数据，日志区间是Pos[538] --> End_log_pos[646]，按事务区间是：Pos[471] --> End_log_pos[673]

更新 name='小二' 这条数据，日志区间是Pos[740] --> End_log_pos[848]，按事务区间是：Pos[673] --> End_log_pos[875]

c1）

单独恢复 name='李四' 这步操作，可这样：

按照binlog日志区间单独恢复：

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c2）

单独恢复 name='小二' 这步操作，可这样：

按照binlog日志区间单独恢复：

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=740 --stop-position=848 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

按照事务区间单独恢复

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=673 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

c3）

将 name='李四'、name='小二' 多步操作一起恢复，需要按事务区间，可这样：

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

查看数据库：

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | 小二 | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | 李四 | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

这样，就恢复了删除前的数据状态了！！

=====================================================

另外：也可以指定时间节点区间恢复(部分恢复)，就是说除了用pos节点的办法进行恢复，也可以通过指定时间节点区间进行恢复，

按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。

如上，误删除ops库后：

先进行全备份恢复

[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql

查看ops数据库

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

+----+-----------+-----+-----+---------+

2 rows in set (0.00 sec)

mysql>

查看mysq-bin00003日志，找出时间节点

[root@vm-002 ~]# cd /var/lib/mysql

[root@vm-002 mysql]# mysqlbinlog mysql-bin.000003

.............

BEGIN

/*!*/;

# at 173

#160925 21:57:19 server id 1 end_log_pos 201 Intvar

SET INSERT_ID=3/*!*/;

# at 201

#160925 21:57:19 server id 1 end_log_pos 444 Query thread_id=3 exec_time=0 error_code=0

use `ops`/*!*/;

SET TIMESTAMP=1474811839/*!*/;

insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'),('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') #执行的sql语句

/*!*/;

# at 444

#160925 21:57:19 server id 1 end_log_pos 471 Xid = 66#开始执行的时间

COMMIT/*!*/;

# at 471

#160925 21:58:41 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0#结束时间

SET TIMESTAMP=1474811921/*!*/;

BEGIN

/*!*/;

# at 538

#160925 21:58:41 server id 1 end_log_pos 646 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1474811921/*!*/;

update ops.member set name='李四' where id=4#执行的sql语句

/*!*/;

# at 646

#160925 21:58:41 server id 1 end_log_pos 673 Xid = 68#开始执行的时间

COMMIT/*!*/;

# at 673

#160925 21:58:56 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0#结束时间

SET TIMESTAMP=1474811936/*!*/;

BEGIN

/*!*/;

# at 740

#160925 21:58:56 server id 1 end_log_pos 848 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1474811936/*!*/;

update ops.member set name='小二' where id=2#执行的sql语句

/*!*/;

# at 848

#160925 21:58:56 server id 1 end_log_pos 875 Xid = 69#开始执行的时间

COMMIT/*!*/;

# at 875

#160925 22:01:08 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0#结束时间

SET TIMESTAMP=1474812068/*!*/;

drop database ops

/*!*/;

# at 954

#160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

恢复到更改“name='李四'”之前的数据

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:57:19" --stop-datetime="2016-09-25 21:58:41" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | xiaoer | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:41" --stop-datetime="2016-09-25 21:58:56" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | guohuihui | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | 李四 | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:56" --stop-datetime="2016-09-25 22:01:08" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops

mysql> select * from member;

+----+-----------+-----+-----+---------+

| id | name | sex | age | classid |

+----+-----------+-----+-----+---------+

| 1 | wangshibo | m | 27 | cls1 |

| 2 | 小二 | w | 27 | cls2 |

| 3 | yiyi | w | 20 | cls1 |

| 4 | 李四 | m | 22 | cls3 |

| 5 | zhangsan | w | 21 | cls5 |

| 6 | lisi | m | 20 | cls4 |

| 7 | wangwu | w | 26 | cls6 |

+----+-----------+-----+-----+---------+

7 rows in set (0.00 sec)

这样，就恢复了删除前的状态了！

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

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,793评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 87,567评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,342评论 0赞 338
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,825评论 1赞 277
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,814评论 5赞 368
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,680评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,033评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,687评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 42,175评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,668评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,775评论 1赞 332
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,419评论 4赞 321
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,020评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,978评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,206评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,092评论 2赞 351
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,510评论 2赞 343

mysqlbinlog怎么恢复数据

推荐阅读更多精彩内容