| APP01bin.000001 | 238 | Query? ? ? |? ? ? ? 11 |? ? ? ? 310 | BEGIN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| APP01bin.000001 | 310 | Table_map? |? ? ? ? 11 |? ? ? ? 358 | table_id: 74 (test.t1)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| APP01bin.000001 | 358 | Write_rows? |? ? ? ? 11 |? ? ? ? 402 | table_id: 74 flags: STMT_END_F? ? ? ? ? ? ? ? ? ? ? ? ? |
| APP01bin.000001 | 402 | Xid? ? ? ? |? ? ? ? 11 |? ? ? ? 433 | COMMIT /* xid=30 */? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| APP01bin.000001 | 433 | Query? ? ? |? ? ? ? 11 |? ? ? ? 517 | use `test`; truncate table t1? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------------+
7 rows in set (0.00 sec)
--获取binlog位置
mysql> show variables like 'log_bin_basename';
+------------------+--------------------+
| Variable_name? ? | Value? ? ? ? ? ? ? |
+------------------+--------------------+
| log_bin_basename | /opt/data/APP01bin |
+------------------+--------------------+
3、演示提取binlog日志
#未使用base64-output选项的情形，即缺省值为AUTO
SHELL>? mysqlbinlog /opt/data/APP01bin.000001|grep truncate -B15
# at 310
#141218 16:28:05 server id 11? end_log_pos 358 CRC32 0xe0025004? ? ? ? Table_map: `test`.`t1` mapped to number 74
# at 358
#141218 16:28:05 server id 11? end_log_pos 402 CRC32 0x3452dcfe? ? ? ? Write_rows: table id 74 flags: STMT_END_F
BINLOG '? ? ? ? ? ? ? ? ? ? ? ? ? ? ? #这个BINLOG部分是真实的SQL语句，无法看到具体内容
FZCSVBMLAAAAMAAAAGYBAAAAAEoAAAAAAAEABHRlc3QAAnQxAAICDwI8AAMEUALg
FZCSVB4LAAAALAAAAJIBAAAAAEoAAAAAAAEAAgAC//wBAAVyb2Jpbv7cUjQ=
'/*!*/;
# at 402
#141218 16:28:05 server id 11? end_log_pos 433 CRC32 0xbe26740a? ? ? ? Xid = 30
COMMIT/*!*/;
# at 433
#141218 16:29:00 server id 11? end_log_pos 517 CRC32 0x89c52d6a? ? ? ? Query? thread_id=1? ? exec_time=0? ? error_code=0
SET TIMESTAMP=1418891340/*!*/;
truncate table t1
#使用-v参数的情形，可以看到我们操作生成的SQL语句了，为insert into ..@1之类的形式，如果-vv则输出列的描述信息
#BINLOG部分依旧被显示出来
SHELL>? mysqlbinlog -v /opt/data/APP01bin.000001|grep truncate -B20
/*!*/;
# at 310
#141218 16:28:05 server id 11? end_log_pos 358 CRC32 0xe0025004? ? ? ? Table_map: `test`.`t1` mapped to number 74
# at 358
#141218 16:28:05 server id 11? end_log_pos 402 CRC32 0x3452dcfe? ? ? ? Write_rows: table id 74 flags: STMT_END_F
BINLOG '
FZCSVBMLAAAAMAAAAGYBAAAAAEoAAAAAAAEABHRlc3QAAnQxAAICDwI8AAMEUALg
FZCSVB4LAAAALAAAAJIBAAAAAEoAAAAAAAEAAgAC//wBAAVyb2Jpbv7cUjQ=
'/*!*/;
### INSERT INTO `test`.`t1`
### SET
###? @1=1
###? @2='robin'
# at 402
#141218 16:28:05 server id 11? end_log_pos 433 CRC32 0xbe26740a? ? ? ? Xid = 30
COMMIT/*!*/;
# at 433
#141218 16:29:00 server id 11? end_log_pos 517 CRC32 0x89c52d6a? ? ? ? Query? thread_id=1? ? exec_time=0? ? error_code=0
SET TIMESTAMP=1418891340/*!*/;
truncate table t1
#添加--base64-output=DECODE-ROWS选项来抑制BINLOG的显示，如下我们看不到了BINLOG部分
SHELL>? mysqlbinlog --base64-output=DECODE-ROWS -v /opt/data/APP01bin.000001|grep truncate -B20
/*!*/;
# at 238
#141218 16:28:05 server id 11? end_log_pos 310 CRC32 0x60507739? ? ? ? Query? thread_id=1? ? exec_time=0? ? error_code=0
SET TIMESTAMP=1418891285/*!*/;
BEGIN
/*!*/;
# at 310
#141218 16:28:05 server id 11? end_log_pos 358 CRC32 0xe0025004? ? ? ? Table_map: `test`.`t1` mapped to number 74
# at 358
#141218 16:28:05 server id 11? end_log_pos 402 CRC32 0x3452dcfe? ? ? ? Write_rows: table id 74 flags: STMT_END_F
### INSERT INTO `test`.`t1`
### SET
###? @1=1
###? @2='robin'
# at 402
#141218 16:28:05 server id 11? end_log_pos 433 CRC32 0xbe26740a? ? ? ? Xid = 30
COMMIT/*!*/;
# at 433
#141218 16:29:00 server i