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。基本可以做任何事情了。