binlog解密

对比commit前后binlog的变化

mysql> use lyj;
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> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update lyj_t1 set name1=‘aaaaaaa’ where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> show binlog events in ‘binlog.000009’;
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log
mysql> system ls -lrt
总用量 177052
-rw-r-----. 1 mysql mysql 50331648 7月 22 17:13 ib_logfile1
-rw-r-----. 1 mysql mysql 8585216 7月 22 17:13 #ib_16384_1.dblwr
drwxr-x—. 2 mysql mysql 8192 7月 22 17:13 performance_schema
-rw-r-----. 1 mysql mysql 56 7月 22 17:13 auto.cnf
-rw-------. 1 mysql mysql 1680 7月 22 17:13 ca-key.pem
-rw-r–r--. 1 mysql mysql 1112 7月 22 17:13 ca.pem
-rw-------. 1 mysql mysql 1680 7月 22 17:13 server-key.pem
-rw-r–r--. 1 mysql mysql 1112 7月 22 17:13 server-cert.pem
-rw-------. 1 mysql mysql 1680 7月 22 17:13 client-key.pem
-rw-r–r--. 1 mysql mysql 1112 7月 22 17:13 client-cert.pem
-rw-r–r--. 1 mysql mysql 452 7月 22 17:13 public_key.pem
-rw-------. 1 mysql mysql 1676 7月 22 17:13 private_key.pem
drwxr-x—. 2 mysql mysql 143 7月 22 17:13 mysql
drwxr-x—. 2 mysql mysql 28 7月 22 17:13 sys
-rw-r-----. 1 mysql mysql 179 7月 22 17:16 binlog.000001
-rw-r-----. 1 mysql mysql 351 7月 22 17:40 binlog.000002
-rw-r-----. 1 mysql mysql 2248 7月 23 11:31 binlog.000003
-rw-r-----. 1 mysql mysql 179 7月 23 11:35 binlog.000004
drwxr-x—. 3 mysql mysql 67 7月 23 11:58 kant
drwxr-x—. 2 mysql mysql 76 7月 23 12:12 lyj
-rw-r-----. 1 mysql mysql 464098 7月 23 13:53 binlog.000005
-rw-r-----. 1 mysql mysql 3342 7月 23 13:53 ib_buffer_pool
drwxr-x—. 2 mysql mysql 187 7月 23 13:57 #innodb_temp
-rw-r-----. 1 mysql mysql 96 7月 23 13:57 binlog.index
-rw-------. 1 mysql mysql 5 7月 23 13:57 mysql.sock.lock
srwxrwxrwx. 1 mysql mysql 0 7月 23 13:57 mysql.sock
-rw-r-----. 1 mysql mysql 12582912 7月 23 13:57 ibtmp1
-rw-r-----. 1 mysql mysql 443 7月 23 13:59 binlog.000006
-rw-r-----. 1 mysql mysql 25165824 7月 23 14:00 mysql.ibd
-rw-r-----. 1 mysql mysql 10485760 7月 23 14:00 undo_001
-rw-r-----. 1 mysql mysql 10485760 7月 23 14:01 undo_002
-rw-r-----. 1 mysql mysql 12582912 7月 23 14:01 ibdata1
-rw-r-----. 1 mysql mysql 196608 7月 23 14:01 #ib_16384_0.dblwr
-rw-r-----. 1 mysql mysql 50331648 7月 23 14:01 ib_logfile0
mysql> show binlog events in ‘binlog.000006’;
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000006 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000006 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000006 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000006 | 235 | Query | 1 | 443 | use mysql; ALTER USER ‘root’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS '8DEF6DE13B2D019DC7C22094B4358078991F3CC1’ / xid=80 */ |
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

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

mysql> show binlog events in ‘binlog.000006’;
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000006 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.21, Binlog ver: 4 |
| binlog.000006 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000006 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000006 | 235 | Query | 1 | 443 | use mysql; ALTER USER ‘root’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS '8DEF6DE13B2D019DC7C22094B4358078991F3CC1’ / xid=80 / |
| binlog.000006 | 443 | Anonymous_Gtid | 1 | 522 | SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’ |
| binlog.000006 | 522 | Query | 1 | 605 | BEGIN |
| binlog.000006 | 605 | Table_map | 1 | 674 | table_id: 102 (lyj.lyj_t1) |
| binlog.000006 | 674 | Update_rows | 1 | 754 | table_id: 102 flags: STMT_END_F |
| binlog.000006 | 754 | Xid | 1 | 785 | COMMIT / xid=145 */ |
±--------------±----±---------------±----------±------------±---------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql>

另外一种方法mysqlbinlog -v -v binlog.00000

[root@node-10-80-0-86 mysql]# mysqlbinlog -v -v binlog.000006
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#200723 13:57:14 server id 1 end_log_pos 125 CRC32 0x64869983 Start: binlog v 4, server v 8.0.21 created 200723 13:57:14 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;
BINLOG ’
uiYZXw8BAAAAeQAAAH0AAAABAAQAOC4wLjIxAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAC6JhlfEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBg5mGZA==
'/!/;

at 125

#200723 13:57:14 server id 1 end_log_pos 156 CRC32 0x30b03a0e Previous-GTIDs

[empty]

at 156

#200723 13:59:53 server id 1 end_log_pos 235 CRC32 0x27affb53 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1595483993082686 immediate_commit_timestamp=1595483993082686 transaction_length=287

original_commit_timestamp=1595483993082686 (2020-07-23 13:59:53.082686 CST)

immediate_commit_timestamp=1595483993082686 (2020-07-23 13:59:53.082686 CST)

/!80001 SET @@session.original_commit_timestamp=1595483993082686//!/;
/!80014 SET @@session.original_server_version=80021//!/;
/!80014 SET @@session.immediate_server_version=80021//!/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 235

#200723 13:59:53 server id 1 end_log_pos 443 CRC32 0xaa14adca Query thread_id=10 exec_time=0 error_code=0 Xid = 80
use mysql/!/;
SET TIMESTAMP=1595483993.080929/!/;
SET @@session.pseudo_thread_id=10/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1168113696/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8mb4 //!/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/!/;
SET @@session.time_zone=‘SYSTEM’/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
/!80011 SET @@session.default_collation_for_utf8mb4=255//!/;
ALTER USER ‘root’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS '8DEF6DE13B2D019DC7C22094B4358078991F3CC1’
/!*/;

at 443

#200723 14:03:06 server id 1 end_log_pos 522 CRC32 0x3a5c7904 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes original_committed_timestamp=1595484186564123 immediate_commit_timestamp=1595484186564123 transaction_length=342
/!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED//!/;

original_commit_timestamp=1595484186564123 (2020-07-23 14:03:06.564123 CST)

immediate_commit_timestamp=1595484186564123 (2020-07-23 14:03:06.564123 CST)

/!80001 SET @@session.original_commit_timestamp=1595484186564123//!/;
/!80014 SET @@session.original_server_version=80021//!/;
/!80014 SET @@session.immediate_server_version=80021//!/;
SET @@SESSION.GTID_NEXT= ‘ANONYMOUS’/!/;

at 522

#200723 14:01:07 server id 1 end_log_pos 605 CRC32 0xa1c4a1ca Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1595484067/!/;
BEGIN
/!/;

at 605

#200723 14:01:07 server id 1 end_log_pos 674 CRC32 0xd3b79957 Table_map: lyj.lyj_t1 mapped to number 102

at 674

#200723 14:01:07 server id 1 end_log_pos 754 CRC32 0x7e04e63b Update_rows: table id 102 flags: STMT_END_F

BINLOG ’
oycZXxMBAAAARQAAAKICAAAAAGYAAAAAAAEAA2x5agAGbHlqX3QxAAYDDw8PDw8KFAAUABQAFAAU
AD8BAQACARxXmbfT
oycZXx8BAAAAUAAAAPICAAAAAGYAAAAAAAEAAgAG//8gAQAAAAFBAkJCA0NDQwREREREIAEAAAAH
YWFhYWFhYQJCQgNDQ0MERERERDvmBH4=
'/!/;

UPDATE lyj.lyj_t1

WHERE

@1=1 /* INT meta=0 nullable=1 is_null=0 */

@2=‘A’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@3=‘BB’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@4=‘CCC’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@5=‘DDDD’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@6=NULL /* VARSTRING(20) meta=20 nullable=1 is_null=1 */

SET

@1=1 /* INT meta=0 nullable=1 is_null=0 */

@2=‘aaaaaaa’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@3=‘BB’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@4=‘CCC’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@5=‘DDDD’ /* VARSTRING(20) meta=20 nullable=1 is_null=0 */

@6=NULL /* VARSTRING(20) meta=20 nullable=1 is_null=1 */

at 754

#200723 14:03:06 server id 1 end_log_pos 785 CRC32 0x81863132 Xid = 145
COMMIT/!/;
SET @@SESSION.GTID_NEXT= ‘AUTOMATIC’ /* added by mysqlbinlog / /!*/;
DELIMITER ;

End of log file

/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
[root@node-10-80-0-86 mysql]#