解说mysql之binlog日志以及利用binlog日志恢复数据的方法

2024-02-26 19:17:03 54

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

废话不多说，下面是梳理的binlog日志操作解说：

一、初步了解binlog

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
----------------------------------------------------------------------------------------------------------------------------------------------
DDL
----DataDefinitionLanguage数据库定义语言
主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

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

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

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

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

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

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

*********************************************************************

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

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

1）MySQL主从复制：MySQLReplication在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是日志的基本名或前缀名)；

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

[root@vm-002~]#/etc/init.d/mysqldstop
[root@vm-002~]#/etc/init.d/mysqldrestart
Stoppingmysqld:[OK]
Startingmysqld:[OK]

3）查看binlog日志是否开启

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

1）查看所有binlog日志列表

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

mysql>showmasterstatus;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000002|4102|||
+------------------+----------+--------------+------------------+
1rowinset(0.00sec)

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

mysql>flushlogs;
QueryOK,0rowsaffected(0.13sec)

注意：

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

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

mysql>resetmaster;
QueryOK,0rowsaffected(0.12sec)

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

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

注意：

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

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

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

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

[root@vm-002~]#ps-ef|grepmysql
root97911021:18pts/000: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
mysql98969791021:18pts/000: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
root99169699021:18pts/000:00:00mysql-pxxxxx
root99199715021:23pts/100:00:00grep--colormysql

[root@vm-002~]#cd/var/lib/mysql/
[root@vm-002mysql]#ls
ibdata1ib_logfile0ib_logfile1mysqlmysql-bin.000001mysql-bin.000002mysql-bin.indexmysql.sockopstest

使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：
[root@vm-002mysql]#mysqlbinlogmysql-bin.000002
..............
#at624
#16092521:29:53serverid1end_log_pos796Querythread_id=3exec_time=0error_code=0
SETTIMESTAMP=1474810193/*!*/;
insertintomember(`name`,`sex`,`age`,`classid`)values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #执行的sql语句
/*!*/;
#at796
#16092521:29:53serverid1end_log_pos823Xid=17 #执行的时间
.............

解释：

serverid1：数据库主机的服务号；
end_log_pos796：sql结束时的pos节点
thread_id=11：线程号

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

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

命令格式：

mysql>showbinlogevents[IN'log_name'][FROMpos][LIMIT[offset,]row_count];

参数解释：

IN'log_name'：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROMpos：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT[offset,]：偏移量(不指定就是0)
row_count：查询总条数(不指定就是所有行)

mysql>showbinlogeventsin'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:Serverver:5.1.73-log,Binlogver:4
***************************2.row***************************
Log_name:mysql-bin.000002
Pos:106
Event_type:Query
Server_id:1
End_log_pos:188
Info:use`ops`;droptablecustomers
***************************3.row***************************
Log_name:mysql-bin.000002
Pos:188
Event_type:Query
Server_id:1
End_log_pos:529
Info:use`ops`;CREATETABLEIFNOTEXISTS`member`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENT,
`name`varchar(16)NOTNULL,
`sex`enum('m','w')NOTNULLDEFAULT'm',
`age`tinyint(3)unsignedNOTNULL,
`classid`char(6)DEFAULTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=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`;insertintomember(`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*/
7rowsinset(0.00sec)

ERROR:
Noqueryspecified

mysql>

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

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

mysql>showbinlogevents\G;

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

mysql>showbinlogeventsin'mysql-bin.000002'\G;

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

mysql>showbinlogeventsin'mysql-bin.000002'from624\G;

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

mysql>showbinlogeventsin'mysql-bin.000002'from624limit10\G;

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

mysql>showbinlogeventsin'mysql-bin.000002'from624limit2,10\G;

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

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

mysql>useops；
mysql>CREATETABLEIFNOTEXISTS`member`(
->`id`int(10)unsignedNOTNULLAUTO_INCREMENT,
->`name`varchar(16)NOTNULL,
->`sex`enum('m','w')NOTNULLDEFAULT'm',
->`age`tinyint(3)unsignedNOTNULL,
->`classid`char(6)DEFAULTNULL,
->PRIMARYKEY(`id`)
->)ENGINE=InnoDBDEFAULTCHARSET=utf8;
QueryOK,0rowsaffected(0.10sec)

mysql>showtables;
+---------------+
|Tables_in_ops|
+---------------+
|member|
+---------------+
1rowinset(0.00sec)

mysql>descmember;
+---------+---------------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+---------+---------------------+------+-----+---------+----------------+
|id|int(10)unsigned|NO|PRI|NULL|auto_increment|
|name|varchar(16)|NO||NULL||
|sex|enum('m','w')|NO||m||
|age|tinyint(3)unsigned|NO||NULL||
|classid|char(6)|YES||NULL||
+---------+---------------------+------+-----+---------+----------------+
5rowsinset(0.00sec)

事先插入两条数据

mysql>insertintomember(`name`,`sex`,`age`,`classid`)values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2');
QueryOK,2rowsaffected(0.08sec)
Records:2Duplicates:0Warnings:0
mysql>select*frommember;
+----+-----------+-----+-----+---------+
|id|name|sex|age|classid|
+----+-----------+-----+-----+---------+
|1|wangshibo|m|27|cls1|
|2|guohuihui|w|27|cls2|
+----+-----------+-----+-----+---------+
2rowsinset(0.00sec)

下面开始进行场景模拟：

1）

ops库会在每天凌晨4点进行一次完全备份的定时计划任务，如下：
[root@vm-002~]#crontab-l
04***/usr/bin/mysqldump-uroot-p-B-F-R-x--master-data=2ops|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=2ops|gzip>/opt/backup/ops_$(date+%F).sql.gz
Enterpassword:
[root@vm-002~]#ls/opt/backup/
ops_2016-09-25.sql.gz
-----------------

参数说明：

-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGEMASTER语句以及binlog文件及位置点信息
-----------------

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

由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生
一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作

查看一下：

mysql>showmasterstatus;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000003|106|||
+------------------+----------+--------------+------------------+
1rowinset(0.00sec)

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

2）

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

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

先是早上进行插入数据：

mysql>insertintoops.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');
QueryOK,5rowsaffected(0.08sec)
Records:5Duplicates:0Warnings:0

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

3）

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

mysql>updateops.membersetname='李四'whereid=4;
QueryOK,1rowaffected(0.07sec)
Rowsmatched:1Changed:1Warnings:0

mysql>updateops.membersetname='小二'whereid=2;
QueryOK,1rowaffected(0.06sec)
Rowsmatched:1Changed:1Warnings:0

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

4）

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

手贱执行了drop语句，直接删除了ops库！吓尿！
mysql>dropdatabaseops;
QueryOK,1rowaffected(0.02sec)

5）

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

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

先备份一下最后一个binlog日志文件：
[root@vm-002~]#cd/var/lib/mysql/
[root@vm-002mysql]#cp-vmysql-bin.000003/opt/backup/
`mysql-bin.000003'->`/opt/backup/mysql-bin.000003'
[root@vm-002mysql]#ls/opt/backup/
mysql-bin.000003ops_2016-09-25.sql.gz

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

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

mysql>flushlogs;
QueryOK,0rowsaffected(0.13sec)

mysql>showmasterstatus;
+------------------+----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+----------+--------------+------------------+
|mysql-bin.000004|106|||
+------------------+----------+--------------+------------------+
1rowinset(0.00sec)

6）

读取binlog日志，分析问题。
读取binlog日志的方法上面已经说到。

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

[root@vm-002~]#cd/var/lib/mysql/
[root@vm-002mysql]#mysqlbinlogmysql-bin.000003

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

mysql>showbinlogeventsin'mysql-bin.000003';

+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
|Log_name|Pos|Event_type|Server_id|End_log_pos|Info|
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
|mysql-bin.000003|4|Format_desc|1|106|Serverver:5.1.73-log,Binlogver: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`;insertintoops.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`;updateops.membersetname='李四'whereid=|
|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`;updateops.membersetname='小二'whereid=|
|mysql-bin.000003|848|Xid|1|875|COMMIT/*xid=69*/|
|mysql-bin.000003|875|Query|1|954|dropdatabaseops|
|mysql-bin.000003|954|Rotate|1|997|mysql-bin.000004;pos=4|
+------------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------------------------------------------------------------+
13rowsinset(0.00sec)

或者：

mysql>showbinlogeventsin'mysql-bin.000003'\G;
.........
.........
***************************12.row***************************
Log_name:mysql-bin.000003
Pos:875
Event_type:Query
Server_id:1
End_log_pos:954
Info:dropdatabaseops
***************************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
13rowsinset(0.00sec)

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

7）

先把凌晨4点全备份的数据恢复：
[root@vm-002~]#cd/opt/backup/
[root@vm-002backup]#ls
mysql-bin.000003ops_2016-09-25.sql.gz
[root@vm-002backup]#gzip-dops_2016-09-25.sql.gz
[root@vm-002backup]#mysql-uroot-p-v Enterpassword:
--------------
/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT*/
--------------

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

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

--------------
/*!40111SETSQL_NOTES=@OLD_SQL_NOTES*/
--------------

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

mysql>showdatabases; #发现ops库已经恢复回来了
mysql>useops;
Readingtableinformationforcompletionoftableandcolumnnames
Youcanturnoffthisfeaturetogetaquickerstartupwith-A

Databasechanged
mysql>showtables;
+---------------+
|Tables_in_ops|
+---------------+
|member|
+---------------+
1rowinset(0.00sec)

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|id|name|sex|age|classid|
+----+-----------+-----+-----+---------+
|1|wangshibo|m|27|cls1|
|2|guohuihui|w|27|cls2|
+----+-----------+-----+-----+---------+
2rowsinset(0.00sec)

mysql>

但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--18:00之间的数据还没有恢复回来！！
怎么办呢？
莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。

8）

从binlog日志恢复数据
恢复命令的语法格式：
mysqlbinlogmysql-bin.0000xx|mysql-u用户名-p密码数据库名

--------------------------------------------------------
常用参数选项解释：
--start-position=875起始pos点
--stop-position=954结束pos点
--start-datetime="2016-9-2522:01:08"起始时间点
--stop-datetime="2019-9-2522: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-002backup]#/usr/bin/mysqlbinlog/var/lib/mysql/mysql-bin.000003|/usr/bin/mysql-uroot-p123456-vops

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

--stop-position=471pos结束节点（按照事务区间算，是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-vops

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

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

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

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-vops

按照事务区间单独恢复
[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-vops

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-vops

按照事务区间单独恢复
[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-vops

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-vops

查看数据库：

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

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

另外：

也可指定时间节点区间恢复(部分恢复)：
除了用pos节点的办法进行恢复，也可以通过指定时间节点区间进行恢复，按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。

如上，误删除ops库后：

先进行全备份恢复
[root@vm-002backup]#mysql-uroot-p-v

查看ops数据库

mysql>select*frommember;
+----+-----------+-----+-----+---------+
|id|name|sex|age|classid|
+----+-----------+-----+-----+---------+
|1|wangshibo|m|27|cls1|
|2|guohuihui|w|27|cls2|
+----+-----------+-----+-----+---------+
2rowsinset(0.00sec)

mysql>

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

[root@vm-002~]#cd/var/lib/mysql
[root@vm-002mysql]#mysqlbinlogmysql-bin.000003
.............
.............
BEGIN
/*!*/;
#at173
#16092521:57:19serverid1end_log_pos201Intvar
SETINSERT_ID=3/*!*/;
#at201
#16092521:57:19serverid1end_log_pos444Querythread_id=3exec_time=0error_code=0
use`ops`/*!*/;
SETTIMESTAMP=1474811839/*!*/;
insertintoops.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语句
/*!*/;
#at444
#16092521:57:19serverid1end_log_pos471Xid=66 #开始执行的时间
COMMIT/*!*/;
#at471
#16092521:58:41serverid1end_log_pos538Querythread_id=3exec_time=0error_code=0 #结束时间
SETTIMESTAMP=1474811921/*!*/;
BEGIN
/*!*/;
#at538
#16092521:58:41serverid1end_log_pos646Querythread_id=3exec_time=0error_code=0
SETTIMESTAMP=1474811921/*!*/;
updateops.membersetname='李四'whereid=4 #执行的sql语句
/*!*/;
#at646
#16092521:58:41serverid1end_log_pos673Xid=68 #开始执行的时间
COMMIT/*!*/;
#at673
#16092521:58:56serverid1end_log_pos740Querythread_id=3exec_time=0error_code=0 #结束时间
SETTIMESTAMP=1474811936/*!*/;
BEGIN
/*!*/;
#at740
#16092521:58:56serverid1end_log_pos848Querythread_id=3exec_time=0error_code=0
SETTIMESTAMP=1474811936/*!*/;
updateops.membersetname='小二'whereid=2 #执行的sql语句
/*!*/;
#at848
#16092521:58:56serverid1end_log_pos875Xid=69 #开始执行的时间
COMMIT/*!*/;
#at875
#16092522:01:08serverid1end_log_pos954Querythread_id=3exec_time=0error_code=0 #结束时间
SETTIMESTAMP=1474812068/*!*/;
dropdatabaseops
/*!*/;
#at954
#16092522:09:46serverid1end_log_pos997Rotatetomysql-bin.000004pos:4
DELIMITER;
#Endoflogfile
ROLLBACK/*addedbymysqlbinlog*/;
/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

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

[root@vm-002~]#/usr/bin/mysqlbinlog--start-datetime="2016-09-2521:57:19"--stop-datetime="2016-09-2521:58:41"--

database=ops/var/lib/mysql/mysql-bin.000003|/usr/bin/mysql-uroot-p123456-vops

[root@vm-002~]#/usr/bin/mysqlbinlog--start-datetime="2016-09-2521:58:41"--stop-datetime="2016-09-2521:58:56"--database=ops/var/lib/mysql/mysql-bin.000003|/usr/bin/mysql-uroot-p123456-vops
mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

[root@vm-002~]#/usr/bin/mysqlbinlog--start-datetime="2016-09-2521:58:56"--stop-datetime="2016-09-2522:01:08"--database=ops/var/lib/mysql/mysql-bin.000003|/usr/bin/mysql-uroot-p123456-vops
mysql>select*frommember;
+----+-----------+-----+-----+---------+
|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|
+----+-----------+-----+-----+---------+
7rowsinset(0.00sec)

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

总结：

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

以上这篇解说mysql之binlog日志以及利用binlog日志恢复数据的方法就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持毛票票。

解说mysql之binlog日志以及利用binlog日志恢复数据的方法

热门推荐

随机推荐