整理自网络
(mysqlbinlog命令 -v 显示每个字段插入的值, -v -v显示每个字段的注释,包括数据类型)
MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。
binlog日志属于二进制文件,实现基于时间点的恢复或者不完全恢复时必须要用到二进制日志。
下面给出了提取binlog日志的例子:
1.查看mysql数据库版本
mysql> show variables like 'version';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| version | 5.6.28-log |
+---------------+------------+
1 row in set (0.00 sec)
2.查看binlog格式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
3.查看当前binlog文件,binlog列表最后一行。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 65426 |
| mysql-bin.000002 | 1176885 |
| mysql-bin.000003 | 2064 |
| mysql-bin.000004 | 120 |
| mysql-bin.000005 | 2848 |
| mysql-bin.000006 | 975 |
| mysql-bin.000007 | 631 |
| mysql-bin.000008 | 191 |
+------------------+-----------+
8 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 191
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5933d78c-a22b-11e5-af00-0800273fac50:1-13
1 row in set (0.00 sec)
4.创建表,插入数据。
create table binlogtest(id serial,name varchar(20));
insert into binlogtest values (1,'lijingkuan');
insert into binlogtest values (2,'test')
5.在数据库中查询mysql binlog中的events;