发现MySQL库的binlog日志出来都是乱码,如下所示:
BINLOG ’
IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY
IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==,如果强行用-v出来也得不到具体执行的sql语句,这个问题困扰了很近,今天深入研究才发现核心问题所在。
日志格式是MIXED的,这个表示一些特殊的uuid以及now()之类会记录成row,其它的仍然是记录sql模式。
2,测试例子: mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table z4 select 1 as a; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into z4 select 2; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> 3,查看binlog,打开是乱码模式,看不到执行的sql语句,如下所示 [root@mysql5.6.12 binlog_new]# ll 总用量 32 -rw-rw----. 1 mysql mysql 143 12月 10 21:09 mysql-bin.000001 -rw-rw----. 1 mysql mysql 17549 12月 11 15:06 mysql-bin.000002 -rw-rw----. 1 mysql mysql 618 12月 11 15:07 mysql-bin.000003 -rw-rw----. 1 mysql mysql 135 12月 11 15:06 mysql-bin.index [root@mysql5.6.12 binlog_new]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 /*!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 #151211 15:06:46 server id 72 end_log_pos 120 CRC32 0x9961ff72 Start: binlog v 4, server v 5.6.12-log created 151211 15:06:46 # Warning: this binlog is either in use or was not closed properly. BINLOG ' BnZqVg9IAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXL/ YZk= '/*!*/; # at 120 #151211 15:07:13 server id 72 end_log_pos 192 CRC32 0x3ea43b0e Query thread_id=732 exec_time=0 error_code=0 SET TIMESTAMP=1449817633/*!*/; SET @@session.pseudo_thread_id=732/*!*/; 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 utf8mb4 *//*!*/; SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=45/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 192 #151211 15:07:13 server id 72 end_log_pos 314 CRC32 0xcaec51ae Query thread_id=732 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1449817633/*!*/; CREATE TABLE `z4` ( `a` int(1) NOT NULL DEFAULT '0' ) /*!*/; # at 314 #151211 15:07:13 server id 72 end_log_pos 359 CRC32 0xd8c93954 Table_map: `test`.`z4` mapped to number 122 # at 359 #151211 15:07:13 server id 72 end_log_pos 399 CRC32 0xa494ef72 Write_rows: table id 122 flags: STMT_END_F BINLOG ' IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA== '/*!*/; # at 399 #151211 15:07:13 server id 72 end_log_pos 430 CRC32 0xd1ab5b55 Xid = 6908 COMMIT/*!*/; # at 430 #151211 15:07:20 server id 72 end_log_pos 502 CRC32 0xdfc3212d Query thread_id=732 exec_time=0 error_code=0 SET TIMESTAMP=1449817640/*!*/; BEGIN /*!*/; # at 502 #151211 15:07:20 server id 72 end_log_pos 547 CRC32 0xc59aab0e Table_map: `test`.`z4` mapped to number 122 # at 547 #151211 15:07:20 server id 72 end_log_pos 587 CRC32 0x648b02a4 Write_rows: table id 122 flags: STMT_END_F BINLOG ' KHZqVhNIAAAALQAAACMCAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAAAOq5rF KHZqVh5IAAAAKAAAAEsCAAAAAHoAAAAAAAEAAgAB//4CAAAApAKLZA== '/*!*/; # at 587 #151211 15:07:20 server id 72 end_log_pos 618 CRC32 0x9b35600a Xid = 6915 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]#