5.7新特性：mysql打开binlog的SQL记录，排查问题更快捷

5.7版本中，我们又有了一个新的功能：
binlog_rows_query_log_events 
默认关闭 ，可选打开，建议打开，还是比较有用的。可以看到row格式下的sql语句，方便排查问题和恢复数据。 

 

(1)首先是先从binlog中恢复出来。
/usr/local/mysql/3308/bin/mysqlbinlog /log/binlog/3308/bin.000006 -vv >/tmp/ss.sql

# at 6433161
#160726 16:30:48 server id 3308161  end_log_pos 6433226 CRC32 0xf8016d90        GTID    last_committed=124      sequence_number=125
SET @@SESSION.GTID_NEXT= 'e08d636f-47de-11e6-af3d-0050569e70f2:150'/*!*/;
# at 6433226
#160726 16:30:48 server id 3308161  end_log_pos 6433299 CRC32 0xce9b4c6e        Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1469521848/*!*/;
BEGIN
/*!*/;
# at 6433299
#160726 16:30:48 server id 3308161  end_log_pos 6433353 CRC32 0xd3574f25        Rows_query
# insert into t values(2,'ujuj')            可以直接还原原生SQL语句。
# at 6433353
#160726 16:30:48 server id 3308161  end_log_pos 6433401 CRC32 0x0cde3e29        Table_map: `tmp_2`.`t` mapped to number 160
# at 6433401
#160726 16:30:48 server id 3308161  end_log_pos 6433446 CRC32 0x5c3dc545        Write_rows: table id 160 flags: STMT_END_F

BINLOG '
uB+XVx2BejIANgAAAEkqYgCAAB5pbnNlcnQgaW50byB0IHZhbHVlcygyLCd1anVqJyklT1fT
uB+XVxOBejIAMAAAAHkqYgAAAKAAAAAAAAEABXRtcF8yAAF0AAIDDwJjAAMpPt4M
uB+XVx6BejIALQAAAKYqYgAAAKAAAAAAAAEAAgAC//wCAAAABHVqdWpFxT1c
'/*!*/;
### INSERT INTO `tmp_2`.`t`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='ujuj' /* VARSTRING(99) meta=99 nullable=1 is_null=0 */

直接就是SQL语句。非常使用，坏处就是binlog又变大了。

那么我们可以得到一个方便的SQL执行情况：
[root@test11 ~]# more /tmp/ss.sql | grep --ignore-case "Rows_query" -A5 -B4
SET TIMESTAMP=1469521816/*!*/;
BEGIN
/*!*/;
# at 6432924
#160726 16:30:16 server id 3308161  end_log_pos 6432992 CRC32 0xc007f27f     Rows_query
# update dept set loc='boston' where deptno=40
# at 6432992
#160726 16:30:16 server id 3308161  end_log_pos 6433045 CRC32 0x38b0706c     Table_map: `test`.`dept` mapped to number 130
# at 6433045
#160726 16:30:16 server id 3308161  end_log_pos 6433130 CRC32 0xb1098c91     Update_rows: table id 130 flags: STMT_END_F
--
SET TIMESTAMP=1469521848/*!*/;
BEGIN
/*!*/;
# at 6433299
#160726 16:30:48 server id 3308161  end_log_pos 6433353 CRC32 0xd3574f25     Rows_query
# insert into t values(2,'ujuj')
# at 6433353
#160726 16:30:48 server id 3308161  end_log_pos 6433401 CRC32 0x0cde3e29     Table_map: `tmp_2`.`t` mapped to number 160
# at 6433401
#160726 16:30:48 server id 3308161  end_log_pos 6433446 CRC32 0x5c3dc545     Write_rows: table id 160 flags: STMT_END_F

 

可以更加简单：
[root@test11 ~]# more /tmp/ss.sql | grep --ignore-case "Rows_query" -A2
#160726 16:30:16 server id 3308161  end_log_pos 6432992 CRC32 0xc007f27f     Rows_query
# update dept set loc='boston' where deptno=40
# at 6432992
--
#160726 16:30:48 server id 3308161  end_log_pos 6433353 CRC32 0xd3574f25     Rows_query
# insert into t values(2,'ujuj')
# at 6433353

 

直接过滤出来SQL：
[root@test11 ~]# more /tmp/ss.sql | grep --ignore-case "Rows_query" -A2 | grep -E 'insert|update|delete'
# update dept set loc='boston' where deptno=40
# insert into t values(2,'ujuj')
这种方法只能过滤出，insert,update,delete操作。

 

但是对于create,drop,alter等操作，就无法过滤出来。
来个完整的：
more /tmp/ss.sql | grep -E 'insert|update|delete|alter|drop|create|use'
create table bonus
create table salgrade ( grade int,losal int,hisal int )
create table cust (cno int primary key,lname varchar(30),fname varchar(30),sex char(1),height int,weight int)
create table cust (cno int primary key auto_increment,lname varchar(30),fname varchar(30),sex char(1),height int,weight int)
create index idx_test_1 on cust(sex,height,lname,fname)
drop index idx_test_1 on cust
create index idx_test_1 on cust(sex,lname,fname)
drop index idx_test_1 on cust
create index idx_test_1 on cust(sex,height)
drop index idx_test_1 on cust
create index idx_test_1 on cust(sex,height,lname,fname,cno)
drop index idx_test_1 on cust
create index idx_test_1 on cust(sex,lname,fname,cno)
drop index idx_test_1 on cust
create index idx_test_1 on cust(height,lname,fname,cno,sex,weight)
# update dept set loc='boston' where deptno=40
# insert into t values(2,'ujuj')
drop database tmp_2
create database tmp
use `tmp`/*!*/;
create table t(id int)
# insert into t values(2),(12)
# delete from t where id=2
alter table t add column name varchar(30)
alter table t add primary key(id)
alter table t drop primary key

过滤出来use，主要是获取schema。基本可以做任何事情了。