binlog_format=row显示语句

binlog_format=row的情况下： 
对delete的内容，通过 mysqlbinlog找到 真实的delete语句， 也可以通过这种方法 ，构成一个insert语句，然后回滚这个操作 
binlog_format=statement是不行的。 

例： 
mysql> show variables like '%binlog_format%' 
-> ; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| binlog_format | ROW | 
+---------------+-------+ 
1 row in set (0.00 sec) 


mysql> delete from test where c1=1; 
Query OK, 1 row affected (0.00 sec) 

mysql> commit; 
Query OK, 0 rows affected (0.00 sec) 

mysql> flush tables; 
Query OK, 0 rows affected (0.00 sec) 

mysql> flush logs; 
Query OK, 0 rows affected (0.03 sec) 

然后通过mysqlbinlog去解析文件： 

[ mysql@localhost  data]$ mysqlbinlog  --base64-output=DECODE-ROWS -vvv  1.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#150326 16:29:51 server id 1 end_log_pos 120 CRC32 0x848eaa68 Start: binlog v 4, server v 5.6.23-enterprise-commercial-advanced-log created 150326 16:29:51 at startup 
ROLLBACK/*!*/; 
# at 120 
#150326 16:31:05 server id 1 end_log_pos 192 CRC32 0xff1cedd3 Query thread_id=1 exec_time=0 error_code=0 
SET TIMESTAMP=1427358665/*!*/; 
SET @@session.pseudo_thread_id=1/*!*/; 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 
SET @@session.sql_mode=1075838976/*!*/; 
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; 
/*!\C utf8 *//*!*/; 
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; 
SET @@session.lc_time_names=0/*!*/; 
SET @@session.collation_database=DEFAULT/*!*/; 
BEGIN 
/*!*/; 
# at 192 
#150326 16:31:05 server id 1 end_log_pos 240 CRC32 0xa94c3d4d Table_map: `test`.`test` mapped to number 72 
# at 240 
#150326 16:31:05 server id 1 end_log_pos 280 CRC32 0x5c82f891 Delete_rows: table id 72 flags: STMT_END_F 
### DELETE FROM `test`.`test` 
### WHERE 
### @1=1 /* SHORTINT meta=0 nullable=0 is_null=0 */ 
### @2=1 /* SHORTINT meta=0 nullable=0 is_null=0 */ 
# at 280 
#150326 16:31:05 server id 1 end_log_pos 353 CRC32 0xe73c713a Query thread_id=1 exec_time=0 error_code=0 
SET TIMESTAMP=1427358665/*!*/; 
COMMIT 
/*!*/; 
# at 353 
#150326 16:31:15 server id 1 end_log_pos 432 CRC32 0xbaf36942 Query thread_id=1 exec_time=0 error_code=0 
use `test`/*!*/; 
SET TIMESTAMP=1427358675/*!*/; 
flush tables 
/*!*/; 
# at 432 
#150326 16:31:18 server id 1 end_log_pos 471 CRC32 0xf9c3f69c Rotate to 1.000002 pos: 4 
DELIMITER ; 
# End of log file 
ROLLBACK /* added by mysqlbinlog */; 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 



如果是 binlog_format=statement的话，得到的结果是 
use `test`/*!*/; 
SET TIMESTAMP=1427360555/*!*/; 
delete from test where c1=4 
/*!*/; 
# at 300 
#150326 17:02:35 server id 1 end_log_pos 380 CRC32 0x189c0ceb Query thread_id=1 exec_time=0 error_code=0 
SET TIMESTAMP=1427360555/*!*/; 
COMMIT 
不能重构出来原来的insert

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-1475224/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-1475224/