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

这里有个十分重要的工具mdash;mdash;mysqlbinlog,专门用来查看二进制日志。MySQL备份一般采取全备份加日志备份的方式，比如每天

MySQL备份一般采取全备份加日志备份的方式，比如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL Server故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。用来进行全备和日志备的工具各种各样，各有其特色，在这里不做描述。本文主要讲解一下在回复完全备份后，如何应用备份的二进制日志来将数据恢复到指定的位置或时间点。

--------------------------------------分割线 --------------------------------------

用mysqldump和mysqlbinlog的MySQL数据恢复实验

Ubuntu 14.04下安装MySQL

《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF

Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主从服务器

Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群

Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二进制安装

--------------------------------------分割线 --------------------------------------

这里有个十分重要的工具——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 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复制线程。