在mysql5.6版本将设置binlog_rows_query_log_events=1,在binlog格式为row的模式下,也可通过SHOW BINLOG EVENTS in 'binlog.000001';查看完整的sql语句
mysql> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | OFF |
+------------------------------+-------+
1 row in set (0.01 sec)
开启
mysql> set global binlog_rows_query_log_events=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'binlog_rows_query_log_events';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| binlog_rows_query_log_events | ON |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW BINLOG EVENTS in 'binlog.000001';
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 181 | 123 | Server ver: 5.7.17-log, Binlog ver: 4 |
| binlog.000001 | 123 | Previous_gtids | 181 | 154 | |
| binlog.000001 | 154 | Anonymous_Gtid | 181 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 219 | Query | 181 | 336 | use `bhs`; create table test(id int(4),name varchar(22)) |
| binlog.000001 | 336 | Anonymous_Gtid | 181 | 401 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 401 | Query | 181 | 472 | BEGIN |
| binlog.000001 | 472 | Table_map | 181 | 521 | table_id: 219 (bhs.test) |
| binlog.000001 | 521 | Write_rows | 181 | 563 | table_id: 219 flags: STMT_END_F |
| binlog.000001 | 563 | Xid | 181 | 594 | COMMIT /* xid=10 */ |
| binlog.000001 | 594 | Anonymous_Gtid | 181 | 659 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 659 | Query | 181 | 730 | BEGIN |
| binlog.000001 | 730 | Table_map | 181 | 779 | table_id: 219 (bhs.test) |
| binlog.000001 | 779 | Write_rows | 181 | 821 | table_id: 219 flags: STMT_END_F |
| binlog.000001 | 821 | Xid | 181 | 852 | COMMIT /* xid=11 */ |
| binlog.000001 | 852 | Anonymous_Gtid | 181 | 917 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 917 | Query | 181 | 988 | BEGIN |
| binlog.000001 | 988 | Table_map | 181 | 1037 | table_id: 219 (bhs.test) |
| binlog.000001 | 1037 | Write_rows | 181 | 1079 | table_id: 219 flags: STMT_END_F |
| binlog.000001 | 1079 | Xid | 181 | 1110 | COMMIT /* xid=12 */ |
| binlog.000001 | 1110 | Anonymous_Gtid | 181 | 1175 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1175 | Query | 181 | 1246 | BEGIN |
| binlog.000001 | 1246 | Table_map | 181 | 1295 | table_id: 219 (bhs.test) |
| binlog.000001 | 1295 | Delete_rows | 181 | 1351 | table_id: 219 flags: STMT_END_F |
| binlog.000001 | 1351 | Xid | 181 | 1382 | COMMIT /* xid=56 */ |
| binlog.000001 | 1382 | Anonymous_Gtid | 181 | 1447 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1447 | Query | 181 | 1562 | use `bhs`; DROP TABLE `test` /* generated by server */ |
| binlog.000001 | 1562 | Anonymous_Gtid | 181 | 1627 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1627 | Query | 181 | 1744 | use `bhs`; create table test(id int(4),name varchar(22)) |
| binlog.000001 | 1744 | Anonymous_Gtid | 181 | 1809 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 1809 | Query | 181 | 1880 | BEGIN |
| binlog.000001 | 1880 | Table_map | 181 | 1929 | table_id: 221 (bhs.test) |
| binlog.000001 | 1929 | Write_rows | 181 | 1971 | table_id: 221 flags: STMT_END_F |
| binlog.000001 | 1971 | Xid | 181 | 2002 | COMMIT /* xid=80 */ |
| binlog.000001 | 2002 | Anonymous_Gtid | 181 | 2067 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 2067 | Query | 181 | 2138 | BEGIN |
| binlog.000001 | 2138 | Table_map | 181 | 2187 | table_id: 221 (bhs.test) |
| binlog.000001 | 2187 | Write_rows | 181 | 2229 | table_id: 221 flags: STMT_END_F |
| binlog.000001 | 2229 | Xid | 181 | 2260 | COMMIT /* xid=86 */ |
| binlog.000001 | 2260 | Anonymous_Gtid | 181 | 2325 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 2325 | Query | 181 | 2396 | BEGIN |
| binlog.000001 | 2396 | Table_map | 181 | 2445 | table_id: 221 (bhs.test) |
| binlog.000001 | 2445 | Write_rows | 181 | 2487 | table_id: 221 flags: STMT_END_F |
| binlog.000001 | 2487 | Xid | 181 | 2518 | COMMIT /* xid=92 */ |
+---------------+------+----------------+-----------+-------------+----------------------------------------------------------+
43 rows in set (0.00 sec)