mysqlbinlog解析binlog乱码问题解密(3)

确实可以看到sql语句,不过都是row模式的,如下所示:

### INSERT INTO `test`.`z4` ### SET ### @1=2 # at 587

看到不到应用程序或者客户端执行的真正sql语句,这样也不利于进行业务分析数据分析,无助于对程序的优化。

5,问题分析

这样binlog日志格式MIXED都录制为乱码,那我将换成STATEMENT格式看看是否会持续乱码?,修改完my.cnf后,重启mysql数据库,开始建表测试,但是报错如下:

mysql> create table z3 select 1 as a; ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED. mysql>

问题发现了,看到问题贺新郎,隔离级别太低了,我的默认隔离级别是READ-COMMITTED,所以导致binlog记录的必须都是row模式,解析出来的是乱码,强行用-v显示出来也是row模式,所以我将隔离级别升级为REPEATABLE-READ的话,就会有row格式也会有statement格式了。接下来为了用2个小实例来验证我的判断:

4.1 REPEATABLE-READ和STATEMENT测试结果

**所以我将隔离级别升级为REPEATABLE-READ,binlog设置为binlog_format=STATEMENT
如下所示:**

# Set the default transaction isolation level. Levels available are: # READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE transaction_isolation = REPEATABLE-READ binlog_format=STATEMENT

1

然后重启mysql,看binlog的记录形式是啥样的,如下所示:

BEGIN /*!*/; # at 219 # at 251 #151211 16:15:02 server id 72 end_log_pos 251 CRC32 0x4ea440db Intvar SET INSERT_ID=10550/*!*/; #151211 16:15:02 server id 72 end_log_pos 435 CRC32 0xa37c5f2d Query thread_id=1 exec_time=0 error_code=0 use `parking_db`/*!*/; SET TIMESTAMP=1449821702/*!*/; INSERT INTO access_log.access_log VALUES(NULL,CONNECTION_ID(),NOW(),USER(),CURRENT_USER()) /*!*/; # at 435 #151211 16:15:02 server id 72 end_log_pos 466 CRC32 0x2970e89a Xid = 3 COMMIT/*!*/; # at 466 #151211 16:16:22 server id 72 end_log_pos 569 CRC32 0xbe43b367 Query thread_id=2 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1449821782/*!*/; create table z4 select 1 as a /*!*/; # at 569 #151211 16:16:32 server id 72 end_log_pos 648 CRC32 0x69b2383c Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1449821792/*!*/; BEGIN /*!*/; # at 648 #151211 16:16:32 server id 72 end_log_pos 745 CRC32 0xcd1721a4 Query thread_id=2 exec_time=0 error_code=0 SET TIMESTAMP=1449821792/*!*/; insert into z4 select 2 /*!*/; # at 745 #151211 16:16:32 server id 72 end_log_pos 776 CRC32 0xfc0dcfc4 Xid = 70 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@mysql5.6.12 binlog_new]#

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/ed6cb83d7ebc0b94afa8d00de46e310d.html