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

使用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 |
+---------------------+-----------+

查看二进制日志中的事件

执行一些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 LOGS
PURGE 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.000001
mysqlbinlog -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.sql
mysql>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文件（适用于日志量不多的情况）