php读取binlog,使用mysqlbinlog提取二进制日志

MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件，我们可以从binlog提取出来

MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件，我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复。本文主要描述了如果提取binlog日志，并给出相关示例。

有关binlog的介绍与描述请参考：MySQL 二进制日志(Binary Log)

1、提取mysqlbinlog的几种方式

a、使用show binlog events方式可以获取当前以及指定binlog的日志，不适宜提取大量日志。

b、使用mysqlbinlog命令行提取(适宜批量提取日志)。

2、演示show binlog events方式

mysql> show variables like 'version';

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

| Variable_name | Value |

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

| version | 5.6.12-log |

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

mysql> show binary logs;

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

| Log_name | File_size |

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

| APP01bin.000001 | 120 |

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

a、只查看第一个binlog文件的内容(show binlog events)

mysql> use replication;

Database changed

mysql> select * from tb;

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

| id | val |

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

| 1 | robin |

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

mysql> insert into tb values(2,'jack');

Query OK, 1 row affected (0.02 sec)

mysql> flush logs;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb values(3,'fred');

Query OK, 1 row affected (0.00 sec)

mysql> show binary logs;

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

| Log_name | File_size |

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

| APP01bin.000001 | 409 |

| APP01bin.000002 | 363 |

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

mysql> show binlog events;

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

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

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

| APP01bin.000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |

| APP01bin.000001 | 120 | Query | 11 | 213 | BEGIN |

| APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') |

| APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ |

| APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 |

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

-- 在上面的结果中第3行可以看到我们执行的SQL语句，第4行为自动提交

-- Author : Leshami

-- Blog :

b、查看指定binlog文件的内容(show binlog events in 'binname.xxxxx')

mysql> show binlog events in 'APP01bin.000002';

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

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

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

| APP01bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |

| APP01bin.000002 | 120 | Query | 11 | 213 | BEGIN |

| APP01bin.000002 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(3,'fred') |

| APP01bin.000002 | 332 | Xid | 11 | 363 | COMMIT /* xid=394 */ |

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

本文原创发布php中文网，转载请注明出处，感谢您的尊重！