Decrypt mysqlbinlog parsing binlog garbled.

Label Mysqlmessy codeMysqlbinlo
305 people read comments(0) Collection Report
Classification:

MySQL library binlog log out is garbled, as shown below:
BINLOG
IXZqVhNIAAAALQAAAGcBAAAAAHoAAAAAAAEABHRlc3QAAno0AAEDAABUOcnY
IXZqVh5IAAAAKAAAAI8BAAAAAHoAAAAAAAEAAgAB//4BAAAAcu+UpA==, if forced to use -v out of the SQL statement, the problem is very close, today's in-depth study to find the core issues.


1, the problem of binlog log, look at the next log format

Show variables like mysql>'%binlog%';
+-----------------------------------------+----------------------+
|Variable_name|Value|
+-----------------------------------------+----------------------+
Binlog_cache_size One million forty-eight thousand five hundred and seventy-six|
Binlog_checksum CRC32|
Binlog_direct_non_transactional_updates OFF|
Binlog_format MIXED|
Binlog_max_flush_queue_time Zero|
Binlog_order_commits ON|
Binlog_row_image FULL|
Binlog_rows_query_log_events OFF|
Binlog_stmt_cache_size Thirty-two thousand seven hundred and sixty-eight|
Innodb_api_enable_binlog OFF|
Innodb_locks_unsafe_for_binlog OFF|
Max_binlog_cache_size 18446744073709547520|
Max_binlog_size One billion seventy-three million seven hundred and forty-one thousand eight hundred and twenty-four|
Max_binlog_stmt_cache_size 18446744073709547520|
Sync_binlog One|
+-----------------------------------------+----------------------+
FifteenIn set rows (Zero point zero oneSEC)

Mysql>

Log format is MIXED, this indicates that some special UUID and now () and the like will be recorded into row, the other is still record SQL mode.


2, test examples:

Use test mysql>;
Table information ReadingForCompletionOfTableAndNames column
Can turn YouOffFeature thisTo GetQuicker startup aWith-A

Changed Database
Create table Z4 mysql>Select One AsA;
OK Query,OneAffected row (Zero point zero twoSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Insert mysql>IntoZ4Select Two;
OK Query,OneAffected row (ZeroSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Mysql>

Blog for CSDN blogger Huang Shan (mchdba), the original address is:Http://prog3.com/sbdm/blog/mchdba/article/details/50300035, declined to reprint.


3, check the binlog, open is garbled mode, see SQL statement, as shown below

Binlog_new]# ll [root@mysql5.6.12
Total amount 32
-rw-rw1. MySQL MySQL 10 21:09 mysql-bin.000001 14312
-rw-rw1. MySQL MySQL 11 15:06 mysql-bin.000002 1754912
-rw-rw1. MySQL MySQL 11 15:07 mysql-bin.000003 61812
-rw-rw1. MySQL MySQL 11 15:06 mysql-bin.index 13512
Binlog_new]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000003 [root@mysql5.6.12
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
15:06:46 server ID server 120 end_log_pos 72 0x9961ff72 Start: binlog V CRC32 4, V 5.6.12-log created #151211 151211 15:06:46
This binlog is either # Warning: in use or was not closed properly.
BINLOG '
BnZqVg9IAAAAdAAAAHgAAAABAAQANS42LjEyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXL/
千年虫=
'/ *!*;
# 120
# 151211 15:07:13服务器ID 72 end_log_pos 192 CRC32 0x3ea43b0e查询thread_id = 732 = 0 = 0 error_code exec_time
 时间戳=1449817633/ *!*;
“@”会话pseudo_thread_id =。732/ *!*;
“@”会话foreign_key_checks =。1“,”会话sql_auto_is_null =。0“,”会话unique_checks =。1“,”会话自动提交=。1/ *!*;
“@”会话sql_mode =。1075838976/ *!*;
“@”会话auto_increment_increment =。1“,”会话auto_increment_offset =。1/ *!*;
/ *!C utf8mb4 *// *!*“@”会话character_set_client =。45“,”会话collation_connection =。45“,”会话collation_server =。45/ *!*;
“@”会话lc_time_names =。0/ *!*;
“@”会话collation_database =。默认/ *!*;
开始
/ *!*;
# 192
# 151211 15:07:13服务器ID 72 end_log_pos 314 CRC32 0xcaec51ae查询thread_id = 732 = 0 = 0 error_code exec_time
使用测试/ *!* 时间戳=1449817633/ *!*;
创建  ` Z4 `一个 国际的1  默认 “0”
)
/ *!*;
# 314
# 151211 15:07:13服务器ID 72 end_log_pos 359 CRC32 0xd8c93954 table_map:`测试`。` Z4 `映射到122号
# 359
# 151211 15:07:13服务器ID 72 end_log_pos 399 CRC32 0xa494ef72 write_rows:表ID 122旗:stmt_end_f

binlog”
ixzqvhniaaaalqaaagcbaaaaahoaaaaaaaeabhrlc3qaano0aaedaabuocny
ixzqvh5iaaaakaaaai8baaaaahoaaaaaaaeaagab / 4baaaacu +尿激酶= =
'/ *!*;
# 399
# 151211 15:07:13服务器ID 72 end_log_pos 430 CRC32 0xd1ab5b55 XID = 6908
承诺/ *!*;
# 430
# 151211 15:07:20服务器ID 72 end_log_pos 502 CRC32 0xdfc3212d查询thread_id = 732 = 0 = 0 error_code exec_time
 时间戳=1449817640/ *!*;
开始
/ *!*;
# 502
# 151211 15:07:20服务器ID 72 end_log_pos 547 CRC32 0xc59aab0e table_map:`测试`。` Z4 `映射到122号
# 547
# 151211 15:07:20服务器ID 72 end_log_pos 587 CRC32 0x648b02a4 write_rows:表ID 122旗:stmt_end_f

binlog”
khzqvhniaaaalqaaacmcaaaaahoaaaaaaaeabhrlc3qaano0aaedaaaoq5rf
khzqvh5iaaaakaaaaescaaaaahoaaaaaaaeaagab / 4caaaapaklza = =
'/ *!*;
# 587
# 151211 15:07:20服务器ID 72 end_log_pos 618 CRC32 0x9b35600a XID = 6915
承诺/ *!*;
分隔符;
#终点 日志文件
回滚增加了通过mysqlbinlog */;
/ *!50003集completion_type = @ old_completion_type *// *!50530集@ @会话。pseudo_slave_mode = 0 * /;
[ root@mysql5.6.12 binlog_new ] #

PS:大家看到/usr/local/ MySQL /斌/ mysqlbinlog MySQL 00000解析出来的都是khzqvhniaaaalqaaacmcaaaaahoaaaa这样的乱码格式3箱。


4、谷歌、得知可以用–Base64输出= decode-rows V查看出来SQL语句,如下所示

[根@ MySQL5612binlog_new ] # /usr/local/ MySQL /斌/ mysqlbinlog——Base64输出= decode-rows V MySQL仓3 00000
/ *!50530集@ @会话。pseudo_slave_mode = 1 * // *!40019集@ @会话。max_insert_delayed_threads = 0 * // *!50003集“old_completion_type = @ @ completion_type,completion_type = 0 * /;
分隔符/ *!*# 4
# 151211 15:06:46服务器ID 72 end_log_pos 120 CRC32 0x9961ff72开始:binlog V 4,V 151211 15:06:46 5.6.12-log创建服务器
#警告:这binlog正在使用或没有正确关闭。
# 120
# 151211 15:07:13服务器ID 72 end_log_pos 192 CRC32 0x3ea43b0e查询thread_id = 732 = 0 = 0 error_code exec_time
设置时间戳=1449817633/ *!*;
设置“@session.pseudo_thread_id =732/ *!*;
设置“@session.foreign_key_checks =1,“@session.sql_auto_is_null =0,“@session.unique_checks =1,“@session.autocommit =1/ *!*;
设置“@session.sql_mode =1075838976/ *!*;
设置“@session.auto_increment_increment =1,“@session.auto_increment_offset =1/ *!*/ *!C utf8mb4 *// *!*;
设置“@session.character_set_client =45,“@session.collation_connection =45,“@session.collation_server =45/ *!*;
设置“@session.lc_time_names =0/ *!*;
集@ @会议collation_database =默认。/ *!*;
开始
/ *!*# 192
# 151211 15:07:13服务器ID 72 end_log_pos 314 CRC32 0xcaec51ae查询thread_id = 732 = 0 = 0 error_code exec_time
使用测试/ *!*;
设置时间戳=1449817633/ *!*;
创建` Z4 `(
一个国际的1默认“0”/ *!*# 314
# 151211 15:07:13服务器ID 72 end_log_pos 359 CRC32 0xd8c93954 table_map:`测试`。` Z4 `映射到122号
# 359
# 151211 15:07:13服务器ID 72 end_log_pos 399 CRC32 0xa494ef72 write_rows:表ID 122旗:stmt_end_f
# # #插入`测试` ` ` Z4。
# # #集
# # # @ 1 = 1
# 399
# 151211 15:07:13服务器ID 72 end_log_pos 430 CRC32 0xd1ab5b55 XID = 6908
承诺/ *!*# 430
# 151211 15:07:20服务器ID 72 end_log_pos 502 CRC32 0xdfc3212d查询thread_id = 732 = 0 = 0 error_code exec_time
设置时间戳=十四亿四千九百八十一万七千六百四十/ *!*;
开始
/ *!*# 502
# 151211 15:07:20服务器ID 72 end_log_pos 547 CRC32 0xc59aab0e table_map:`测试`。` Z4 `映射到122号
# 547
# 151211 15:07:20服务器ID 72 end_log_pos 587 CRC32 0x648b02a4 write_rows:表ID 122旗:stmt_end_f
# # #插入`测试` ` ` Z4。
# # #集
# # # @ 1 = 2
# 587
# 151211 15:07:20服务器ID 72 end_log_pos 618 CRC32 0x9b35600a XID = 6915
承诺/ *!*;
分隔符;
日志文件#结束
回滚/ * *添加/ mysqlbinlog/ *!50003集completion_type = @ old_completion_type *// *!50530集@ @会话。pseudo_slave_mode = 0 * /;
[根@ MySQL5612binlog_new ] #

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

# # #插入`测试` ` ` Z4。
# # #集
# # # @ 1 = 2
# 587

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


5、问题分析

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

MySQL >创建表3选择  作为一;
误差一千六百六十五(hy000):无法执行声明:不可能  二进制日志自binlog_format =声明至少一张桌子使用存储引擎有限公司行日志记录。InnoDB有限公司事务隔离级别时的行日志记录 承诺 未提交。
MySQL >

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


4.1 repeatable-read和声明测试结果

*所以我将隔离级别升级为repeatable-read,binlog设置为binlog_format =声明
如下所示:**

#这个默认 交易 隔离 水平。可用水平:
#-中立,•承诺,可重复—,序列化
transaction_isolation =重复—
binlog_format =声明

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

开始
/ *!*;
# 219
# 251
# 151211 16:15:02服务器ID 72 end_log_pos 251 CRC32 0x4ea440db IntVar
insert_id =一万零五百五十/ *!*;
# 151211 16:15:02服务器ID 72 end_log_pos 435 CRC32 0xa37c5f2d查询thread_id = 1 = 0 = 0 error_code exec_time
使用` parking_db `/ *!* 时间戳=十四亿四千九百八十二万一千七百零二/ *!*;
插入 access_log.access_log价值观,connection_id(),now(),用户(),current_user())
/ *!*;
# 435
# 151211 16:15:02服务器ID 72 end_log_pos 466 CRC32 0x2970e89a XID = 3
承诺/ *!*;
# 466
# 151211 16:16:22服务器ID 72 end_log_pos 569 CRC32 0xbe43b367查询thread_id = 2 = 0 = 0 error_code exec_time
使用测试/ *!* 时间戳=十四亿四千九百八十二万一千七百八十二/ *!*;
创建 Z4选择  作为一
/ *!*;
# 569
# 151211 16:16:32服务器ID 72 end_log_pos 648 CRC32 0x69b2383c查询thread_id = 2 = 0 = 0 error_code exec_time
 时间戳=十四亿四千九百八十二万一千七百九十二/ *!*;
开始
/ *!*;
# 648
# 151211 16:16:32服务器ID 72 end_log_pos 745 CRC32 0xcd1721a4查询thread_id = 2 = 0 = 0 error_code exec_time
 时间戳=十四亿四千九百八十二万一千七百九十二/ *!*;
插入 Z4选择 
/ *!*;
# 745
# 151211 16:16:32服务器ID 72 end_log_pos 776 CRC32 0xfc0dcfc4 XID = 70
承诺/ *!*;
分隔符;
#终点 日志文件
回滚增加了通过mysqlbinlog */;
/ *!50003集completion_type = @ old_completion_type *// *!50530集@ @会话。pseudo_slave_mode = 0 * /;
[ root@mysql5.6.12 binlog_new ] #

See a similar INTO access_log.access_log VALUES USER (NULL, CONNECTION_ID (), NOW (), CURRENT_USER ()) where some of the best use of row mode, because the master copy of the time, UUID INSERT (now) will cause the time delay, so in order to data consistency, statement format is not the best choice.


4.2 REPEATABLE-READ and MIXED test results

My.cnf inside the settings:

Transaction_isolation =REPEATABLE-READ
Binlog_format=MIXED

Restart the MySQL database, the test data:

Insert mysql>IntoZ4Select Three;
OK Query,OneAffected row (ZeroSEC)
Records:OneDuplicates:ZeroWarnings:Zero

Mysql>Exit

View binlog data, you will see the into Z4 select Insert 3 this SQL record, indicating that the mixed mode, the SQL is normal, some now () has been directly resolved into row UUID format, as shown below:

[root@mysql5.6.12/usr/local/mysql/bin/mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin binlog_new]#.000007
...
# at 274
16:21:02 server ID CRC32 368 end_log_pos 72 0x156a1c51 Write_rows: table ID flags: 70 STMT_END_F #151211
### INSERT INTO `access_log`.`access_log`
### SET
### @1=10551
### @2=1
### @3=1449822062
### @4='park_user@192.168.121.243'
### @5='park_user@192.168.%'
# at 368
16:21:02 server ID CRC32 399 end_log_pos 72 0x8254defe Xid #151211 = 3
COMMIT* / / *!;
# at 399
16:21:25 server ID CRC32 478 end_log_pos 72 0xe252f5c7 Query thread_id=2 exec_time=0 error_code=0 #151211
TIMESTAMP= SETOne billion four hundred and forty-nine million eight hundred and twenty-two thousand and eighty-five* / / *!;
BEGIN
* / / *!;
# at 478
16:21:25 server ID CRC32 575 end_log_pos 72 0x34308ad6 Query thread_id=2 exec_time=0 error_code=0 #151211
`test` use* / / *!;
TIMESTAMP= SETOne billion four hundred and forty-nine million eight hundred and twenty-two thousand and eighty-five* / / *!;
Into Z4 select InsertThree
* / / *!;
# at 575
16:21:25 server ID CRC32 606 end_log_pos 72 0x67c460eb Xid #151211 = 61
COMMIT* / / *!;
DELIMITER;
End of log file #
ROLLBACKAdded by mysqlbinlog / * * /;
50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/ / *!;
50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/ / *!;
[root@mysql5.6.12Binlog_new]#
top
One
tread
Zero
Guess what you're looking for.
View comments
* above the user's comments only represent their personal views, do not represent the views or position of the CSDN website
    personal data
    • Visit942525 times
    • Integral:Fifteen thousand three hundred and seventy-five
    • Grade
    • Rank:332nd
    • Original432
    • Reprint:3
    • Translation:2
    • Comment:520
    My course
    Blog column
    Technology web connection
    Http://dev.mysql.com/doc/
    Latest comments