使用mysqlbinlog工具进行基于位置或时间点的数据恢复_MySQL

使用mysqlbinlog工具进行基于位置或时间点的恢复MySQL备份一般采取全备份加日志备份的方式，比如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL Server故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。用来进行全备和日志备的工具各种各样，各有其特色，在这里不做描述。本文主要讲解一下在回复完全备份后，如何应用备份的二进制日志来将数据恢复到指定的位置或时间点。这里有个十分重要的工具——mysqlbinlog,专门用来查看二进制日志。我们以一些列子来说明问题：

先看看如何在MySQL Server中直接查看有哪些二进制日志文件及文件中包含哪些事件。

先清空MySQL Server上的所有二进制日志mysql> reset master;Query OK, 0 rows affected (0.00 sec)查看MySQL Server上的二进制日志mysql> show binary logs;+---------------------+-----------+| Log_name | File_size |+---------------------+-----------+| VMS00781-bin.000001 | 120 |+---------------------+-----------+

查看二进制日志中的事件

mysql>show binlog events;+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+

执行一些DML操作

mysql> delete from ab limit 2;Query OK, 2 rows affected (1.01 sec)

重新开始一个新的日志文件

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

执行一些DML操作

mysql> delete from ab limit 1;Query OK, 1 row affected (0.00 sec)mysql> delete from ab limit 2;Query OK, 2 rows affected (0.01 sec)查看MySQL Server上的二进制日志mysql> show binary logs;+---------------------+-----------+| Log_name | File_size |+---------------------+-----------+| VMS00781-bin.000001 | 372 || VMS00781-bin.000002 | 515 |+---------------------+-----------+查看二进制日志中的事件mysql> show binlog events;+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 || VMS00781-bin.000001 | 120 | Query | 36 | 192 | BEGIN || VMS00781-bin.000001 | 192 | Table_map | 36 | 238 | table_id: 204 (test.ab) || VMS00781-bin.000001 | 238 | Delete_rows | 36 | 291 | table_id: 204 flags: STMT_END_F || VMS00781-bin.000001 | 291 | Xid | 36 | 322 | COMMIT /* xid=289981 */ || VMS00781-bin.000001 | 322 | Rotate | 36 | 372 | VMS00781-bin.000002;pos=4 |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+默认显示可找到的第一个二进制日志文件中的事件，包含了事件的开始位置、结束位置、事件类型、信息等内容。可以看到，第一个事件为格式描述事件；第二个为查询事件，事务开始；第三个为表映射事件，第四个为我们执行的删除操作，第五个为Xid时间是自动提交事务的动作，第六个为日志轮换事件，是我们执行flush logs开启新日志文件引起的。查看指定的二进制日志中的事件mysql> show binlog events in 'VMS00781-bin.000002';+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+| VMS00781-bin.000002 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 || VMS00781-bin.000002 | 120 | Query | 36 | 192 | BEGIN || VMS00781-bin.000002 | 192 | Table_map | 36 | 238 | table_id: 204 (test.ab) || VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F || VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT /* xid=290004 */ || VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN || VMS00781-bin.000002 | 385 | Table_map | 36 | 431 | table_id: 204 (test.ab) || VMS00781-bin.000002 | 431 | Delete_rows | 36 | 484 | table_id: 204 flags: STMT_END_F || VMS00781-bin.000002 | 484 | Xid | 36 | 515 | COMMIT /* xid=290005 */ || VMS00781-bin.000002 | 515 | Query | 36 | 593 | flush slow logs || VMS00781-bin.000002 | 593 | Query | 36 | 671 | flush slow logs |+---------------------+-----+-------------+-----------+-------------+---------------------------------------+该命令还包含其他选项以便灵活查看SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]mysql> show binlog events in 'VMS00781-bin.000002' from 120 limit 2,3;+---------------------+-----+-------------+-----------+-------------+---------------------------------+| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |+---------------------+-----+-------------+-----------+-------------+---------------------------------+| VMS00781-bin.000002 | 238 | Delete_rows | 36 | 282 | table_id: 204 flags: STMT_END_F || VMS00781-bin.000002 | 282 | Xid | 36 | 313 | COMMIT /* xid=290004 */ || VMS00781-bin.000002 | 313 | Query | 36 | 385 | BEGIN |+---------------------+-----+-------------+-----------+-------------+---------------------------------+SHOW BINARY LOGS 等价于 SHOW MASTER LOGSPURGE BINARY LOGS用于里二进制日志，如：PURGE BINARY LOGS TO 'mysql-bin.010';PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';RESET MASTER 与 RESET SLAVE前者清空index文件中列出的所有二进制日志，重置index文件为空，并创建一个新的二进制日志文件，一般用于MASTER首次启动时。后者使SLAVE忘记其在MASTER二进制日志文件中的复制位置，它会删除master.info、relay-log.info 和所有中继日志文件并开始一个新的中继日志文件，以便于开始一个干净的复制。在使用RESET SLAVE前需先关闭 SLAVE复制线程。上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助mysqlbinlog这个工具。查看：shell> mysqlbinlog [options] log_file ...比如：mysqlbinlog [options] VMS00781-bin.000001输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而略不同。二进制日志文件中具体内容的含义以及mysqlbinlog的可用选项可参考相关手册。这里就一些需要特别注意的情况进行说明。

二进制日志文件的格式包含行模式、语句模式和混合模式（也即有服务器决定在什么情况下记录什么类型的日志），基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v（--verbose）选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可

以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容，如

mysqlbinlog -v VMS00781-bin.000001mysqlbinlog -vv VMS00781-bin.000001另外mysqlbinlog和可以通过--read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些而外的连接参数，如--host，--password ，--port，--user，--socket，--protocol等，这些参数仅在指定了--read-from-remote-server后有效。无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用与MySQL Server进行基于时间点、位置或数据库的恢复。比如：mysqlbinlog VMS00781-bin.000001 | mysql -uusername -p或者先将二进制日志写到.sql文件中，然后在mysql客户端执行这些文件，比如：mysqlbinlog VMS00781-bin.000001 > /tmp/VMS00781-bin.000001.sqlmysql>source /tmp/VMS00781-bin.000001.sql这里有几个比较关键的参数:--database=db_name, -d db_name该参数使mysqlbinlog仅从本地二进制日志中输出指定的db_name被use命令选作默认数据库时产生的日志事件。行为类似于mysqld的--binlog-do-db命令。若该参数指定了多次那么只有最后一次指定的内容有效。参数具体的影响依赖于二进制日志格式，只有在使用行模式的日志格式时该参数才能保证一致性。基于语句或混合模式的二进制日志格式中因为可能存在跨库的更新导致--database参数表现不同的行为，从而不能保证数据一致性。mysqlbinlog VMS00781-bin.000001 -d testDB | mysql -uusername -p--force-read, -f使用了该参数后mysqlbinlog工具在读取到不能识别的日志事件时会打印出warning，忽略事件并继续执行，没有此参数的情况下mysqlbinlog会停止。mysqlbinlog VMS00781-bin.000001 -d testDB -f | mysql -uusername -p--no-defaults阻止mysqlbinlog工具从任何配置文件读取参数， .mylogin.cnf除外（以便于安全的保存密码）mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults| mysql -uusername -p--start-datetime=datetime--stop-datetime=datetime上边一组参数用于指定恢复开始时间点和结束时间点，可以一起或单独给出，也可与--start-position，--stop-position混用mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults --start-datetime=datetime --stop-position=NNNNNN | mysql -uusername -p--start-position=N, -j N--stop-position=N上边一组参数用于指定恢复开始位置和结束位置，可以一起或单独给出也可与--start-datetime，--stop-datetime混用mysqlbinlog VMS00781-bin.000001 -d testDB -f --no-defaults --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p需要还原的二进制日志文件通常不止一个，那么要是有多个二进制日志文件需要还原呢，该注意些什么？首先，可以选择上述直接重定向到mysql客户端的方法或先导入到.sql文件然后执行.sql文件的方式逐个应用二进制日志文件。但是这里存在一个隐患，及，如果二进制日志中记录有使用临时表的情况，那么当上一个日志应用完，在新连接中应用下一个二进制日志时临时表就会丢失，引起错误。所以，安全的方式是多个二进制文件同时执行。如：mysqlbinlog VMS00781-bin.000001 VMS00781-bin.000002 VMS00781-bin.000003 --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

或mysqlbinlog VMS00781-bin.00000[1-3] --start-position=NNNNNN --stop-datetime=datetime | mysql -uusername -p

当多个二进制日志文件同时执行时，--start-position和--stop-position分别只应用于第一个列出的二进制日志文件和最后一个列出的二进制日志文件当然也可以先将多个二进制日志文件的输出导到同一个.sql文件最后在执行该.sql文件（适用于日志量不多的情况）

小编还为您整理了以下内容，可能对您也有帮助：

mysqlbinlog 怎么将数据库恢复到指定的时间

$request = curl_init('http://vps_ip/test.mp3');
curl_setopt($request, CURLOPT_POST, true);
curl_setopt(
$request,
CURLOPT_POSTFIELDS,
array(
'file' => '@' . realpath('/home/test.mp3')
));
curl_setopt($request, CURLOPT_RETURNTRANSFER, true);
echo curl_exec($request);
// close the session
curl_close($request);
>

mysqlbinlog 怎么将数据库恢复到指定的时间

$request = curl_init('http://vps_ip/test.mp3');
curl_setopt($request, CURLOPT_POST, true);
curl_setopt(
$request,
CURLOPT_POSTFIELDS,
array(
'file' => '@' . realpath('/home/test.mp3')
));
curl_setopt($request, CURLOPT_RETURNTRANSFER, true);
echo curl_exec($request);
// close the session
curl_close($request);
>

mysql怎样通过binlog恢复被恶意删除的数据

1. 以前我错误的认为mysql的日志可以恢复到任何时间的状态，其实并不是这样，这个恢复是有前提的，就是你至少得有一个从日志记录开始后的数据库备份，通过日志恢复数据库实际上只是一个对以前操作的回放过程而已，不用想得太复杂，既然是回放你就得注意了，如果你执行了两次恢复那么就相当于是回放了两次，后果如何你自己应该清楚了吧。

2. 要想通过日志恢复数据库，在你的my.cnf文件里应该有如下的定义，log-bin=mysql-bin，这个是必须的.binlog-do-db=db_test,这个是指定哪些数据库需要日志，如果有多个数据库就每行一个，如果不指定的话默认就是所有数据库.

[mysqld]

log-bin=mysql-bin

binlog-do-db=db_test

binlog-do-db=db_test2

3.删除二进制日志:

a.mysql> system ls -ltr /var/lib/mysql/bintest*;

mysql>reset master(清空所有的二进制日志文件)

b.purge master logs to 'bintest.000006';(删除bintest.000006之前的二进制日志文件)

c.purge master logs before '2007-08-10 04:07:00'(删除该日期之前的日志)

d.在my.cnf 配置文件中[mysqld]中添加:

expire_logs_day=3设置日志的过期天数,过了指定的天数,会自动删除

4.下面就是恢复操作了

特别提示，mysql每次启动都会重新生成一个类似mysql-bin.000003的文件，如果你的mysql每天都要重新启动一次的话，这时候你就要特别注意不要选错日志文件了。

(注意：下面有一些技巧，这些东西才是最宝贵的哟，普通的东东手册上都有，这可是我摸索出来的哟，别人我都不告诉他。

技巧1 :

在下面你将看到 mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd 类似的语句，但是它一次只能操作一个日志文件，如果你变通一下变成这样 mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.0* | mysql -u root -pmypwd 那么它基本上就会表示出的所有的日志文件了，这样可解决你忘记在哪一个日志文件中的问题，当然你也可以用这种写法更完美,mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -pmypwd ，看到[0-9]*这个东东了吧，它表示以数字开头的任何字符，方便吧！

技巧2:

你可以通过--one-database 参数选择性的恢复单个数据库，example在下面，爽吧。

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

技巧3:

如果你老人家已经使用过 /usr/local/mysql5/bin/mysqlbinlog --start-date="2005-04-20 9:55:00" /var/data/mysql5/mysql-bin.0* > /home/db/tt.sql 类似的语句将日志导成了ASCII文本文件，那么你就可以直接在phpmyadmin或者其它什么乱七糟八的的客户端里执行这个文件文件就行了，因为它本身就是一个标准的sql文件，比如想让文件里面的某些语句不执行，OK，it's easy,找到它们删除即可，然后再放进去执行就OK滴啦！这个可是灰常灰常的爽哟。。。。。。

技巧4:

我来给大家讲一下，下面这条语句都做了什么

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd --one-database db_test

这是把mysql-bin.000001这个二进制文件里的内容转换成ASCII文件(也就是sql语句)，直接通过管道操作符"|"传输给 mysql这个程序，然后过滤掉其它数据库的语句，只在db_test里执行。

技巧5:

着了，多打了一个技巧，现在暂时没内容，等以后再加吧！！！

)

下面部份摘录自网上。

如果MySQL服务器启用了二进制日志，你可以使用mysqlbinlog工具来恢复从指定的时间点开始 (例如，从你最后一次备份)直到现在或另一个指定的时间点的数据。关于启用二进制日志的信息，参见5.11.3节，“二进制日志”。对于 mysqlbinlog的详细信息，参见mysql手册8.6节，“mysqlbinlog：用于处理二进制日志文件的实用工具”。

要想从二进制日志恢复数据，你需要知道当前二进制日志文件的路径和文件名。一般可以从选项文件(即my.cnf or my.ini，取决于你的系统)中找到路径。如果未包含在选项文件中，当服务器启动时，可以在命令行中以选项的形式给出。启用二进制日志的选项为-- log-bin。要想确定当前的二进制日志文件的文件名，输入下面的MySQL语句：

SHOW BINLOG EVENTS G

你还可以从命令行输入下面的内容：

mysql --user=root -pmy_pwd -e 'SHOW BINLOG EVENTS G'

将密码my_pwd替换为服务器的root密码。

1. 指定恢复时间

对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格式的起止时间。举例说明，假设在今天上午10:00(今天是2005年4月20日)，执行SQL语句来删除一个大表。要想恢复表和数据，你可以恢复前晚上的备份，并输入：

mysqlbinlog --stop-date="2005-04-20 9:59:59" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd

该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使日期和时间再次运行mysqlbinlog：

mysqlbinlog --start-date="2005-04-20 10:01:00" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd

在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。

2. 指定恢复位置

也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方法为：

mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00"

/var/log/mysql/mysql-bin.000001 > /tmp/mysql_restore.sql

该命令将在/tmp目录创建小的文本文件，将显示执行了错误的SQL语句时的SQL语句。你可以用文本编辑器打开该文件，寻找你不要想重复的语句。如果二进制日志中的位置号用于停止和继续恢复操作，应进行注释。用log_pos加一个数字来标记位置。使用位置号恢复了以前的备份文件后，你应从命令行输入下面内容：

mysqlbinlog --stop-position="368312" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/mysql-bin.000001 | mysql -u root -pmypwd

上面的第1行将恢复到停止位置为止的所有事务。下一行将恢复从给定的起始位置直到二进制日志结束的所有事务。因为mysqlbinlog的输出包括每个SQL语句记录之前的SET TIMESTAMP语句，恢复的数据和相关MySQL日志将反应事务执行的原时间。

解析如何通过Mysql的二进制日志恢复数据库数据（图文详解）

本篇文章主要介绍了详解如何通过Mysql的二进制日志恢复数据库数据，具有一定的参考价值，有兴趣的可以了解一下。

经常有网站管理员因为各种原因和操作，导致网站数据误删，而且又没有做网站备份，结果不知所措，甚至给网站运营和盈利带来负面影响。所以本文我们将和大家一起分享学习下如何通过Mysql的二机制日志(binlog)来恢复数据。

系统环境：

操作系统：CentOS 6.5 X64 (虚拟机)；

WEB服务：PHP+Mysql+apache；

网站：为方便，直接在本地用蝉知系统搭建一个DEMO站点；

操作步骤：

1.开启binlog功能及基本操作；

2.往站点添加数据；

3.刷新binlog日志；

4.删除数据；

5.binlog日志内容解析；

6.恢复指定数据；

1.开启binlog功能及基本操作

要使用Mysql的binlog日志功能，首先要在Mysql的配置文件中开启该功能，操作很简单。找到Mysql的配置文件，在文件中添加一行”log_bin = mysql-bin”即可。其实在我安装的各种Mysql环境中，该功能通常都是默认开启的。

开启binlog功能后，在mysql的数据库目录下就会有诸如mysql-bin.000001、mysql-bin.000002等文件，这就是mysql的二进制日志文件。每当mysql启动或手动刷新日志后都会新建一个二进制日志文件。

首先我们mysql命令行中，用”show master logs”命令查看已有的binlog文件。

2.往站点添加数据

在网站后台文章模块里，我添加了几条测试数据。

3.刷新binlog日志

此前mysql的binlog文件为mysql-bin.000001，并且在网站后台往数据库中添加了三篇文章。现在我们刷新binlog日志，会生成新的mysql-bin.000002文件，如下：

flush logs;

show master logs;

4.删除数据

这里我把刚才添加的三篇文章都删除掉。

5.binlog日志内容解析

Mysql的二进制日志文件记录的mysql的操作，比如刚才的删除操作，我们来看下日志文件的具体内容。

使用mysql的mysqlbinlog命令：

mysqlbinlog /data/mysql/mysql-bin.000002注意：因为我本地mysqlbinlog无法识别binlog配置中的default-character-set=utf8，所以这里我在命令中加上了” _no-defaults”才起作用，大家引以为鉴。

下面是日志内容部分截图：

6.恢复指定数据；

在通过mysql的binlog日志恢复数据时，我们可以指定恢复到具体时间点，这有点像服务器快照管理。所以我们现在要恢复刚才删除的那篇文章，可以从删除之前找一个时间点，并恢复到那个时间点即可。

有关mysqlbinlog命令的使用方法，我们可以通过mysqlbinlog的帮助命令进行查看，如下：

mysqlbinlog _no-defaults _help

如帮助文档所示，可以通过指定时间或指定位置来恢复数据，这里我以指定时间为例给大家演示。

我们来查看下日志文件mysql-bin.000001，如下：

mysqlbinlog -no--defaults /data/mysql/mysql-bin.000001

通过前面操作步骤我们知道，在删除数据之前，我们生成了mysql-bin.000002日志文件，所以我们只要恢复到这个时间点即可，上图中我已找到了这个时间。

命令如下：

代码如下:

mysqlbinlog _no-defaults _stop-datetime='2017-04-11 09:48:48'/data/mysql/mysql-bin.000001 |mysql _uroot _p123456

这时我们在看后台，发现刚才删除的三篇文章都已恢复回来了，从而到达我们期望的目的。

总结：

本文和大家分享了如何通过mysql的二进制日志文件恢复数据。但还是要提醒大家，在平时要做好网站数据备份，现在的一些主流CMS建站系统都会内置数据库备份功能，比如这里我用的蝉知系统，数据是网站的命脉，做好数据备份以避免后期不必要的麻烦或损失。

本文如未解决您的问题请添加抖音号：51dongshi（抖音搜索懂视），直接咨询即可。