mysqlbinlog解析binlog乱码问题解密-优快云博客

本文链接：https://blog.youkuaiyun.com/hfdchhu/article/details/84050733

本文主要探讨了在处理MySQL binlog时遇到的乱码问题，以及如何通过--base64-output=DECODE-ROWS -v选项来查看SQL语句。分析了binlog格式为MIXED时导致的问题，并通过调整隔离级别为REPEATABLE-READ和切换binlog_format为STATEMENT或MIXED的测试，解释了解决方案。同时，文章介绍了MySQL binlog的使用和分析对于数据库管理和业务优化的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.youkuaiyun.com/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

发现mysql库的binlog日志出来都是乱码，如下所示：
BINLOG ’
IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY
IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==，如果强行用-v出来也得不到具体执行的sql语句，这个问题困扰了很近，今天深入研究才发现核心问题所在。

1，binlog日志的困扰，先看下日志格式

mysql> show variables like '%binlog%';+-----------------------------------------+----------------------+| Variable_name                           | Value                |+-----------------------------------------+----------------------+| binlog_cache_size                       | 1048576              || binlog_checksum                         | CRC32                || binlog_direct_non_transactional_updates | OFF                  || binlog_format                           | MIXED                || binlog_max_flush_queue_time             | 0                    || binlog_order_commits                    | ON                   || binlog_row_image                        | FULL                 || binlog_rows_query_log_events            | OFF                  || binlog_stmt_cache_size                  | 32768                || innodb_api_enable_binlog                | OFF                  || innodb_locks_unsafe_for_binlog          | OFF                  || max_binlog_cache_size                   | 18446744073709547520 || max_binlog_size                         | 1073741824           || max_binlog_stmt_cache_size              | 18446744073709547520 || sync_binlog                             | 1                    |+-----------------------------------------+----------------------+15 rows in set (0.01 sec)mysql> 
  
  
  
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23

日志格式是MIXED的，这个表示一些特殊的uuid以及now()之类会记录成row，其它的仍然是记录sql模式。

2，测试例子：

mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table z4 select 1 as a;Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> insert into z4 select 2;Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0mysql> 
  
  
  
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14

blog为csdn博主黄杉(mchdba)所有，原地址为：http://blog.youkuaiyun.com/mchdba/article/details/50300035，谢绝转载。

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 'BnZqVg9IAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXL/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=0SET 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=0use `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_FBINLOG 'IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnYIXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA=='/*!*/;# at 399#151211 15:07:13 server id 72  end_log_pos 430 CRC32 0xd1ab5b55     Xid = 6908COMMIT/*!*/;# at 430#151211 15:07:20 server id 72  end_log_pos 502 CRC32 0xdfc3212d     Query   thread_id=73