mysql row binlog内容记录

最新推荐文章于 2023-08-21 16:23:22 发布

aoerqileng

最新推荐文章于 2023-08-21 16:23:22 发布

阅读量500

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/aoerqileng/article/details/78963657

版权

MYSQL 专栏收录该内容

274 篇文章 2 订阅

订阅专栏

按下面的顺序执行sql

mysql> create table bai_test(id int, name varchar(10));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into bai_test values(1,'a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into bai_test values(2,'b'),(3,'c');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> delete from bai_test where id=1;
Query OK, 1 row affected (0.02 sec)

mysql> update bai_test set name='aa' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table bai_test add age int default 0;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into bai_test values(4,'d');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into bai_test(id,name) values(4,'d');
Query OK, 1 row affected (0.03 sec)

mysql> drop table bai_test;
Query OK, 0 rows affected (0.01 sec)

下面使用binlog解析看下日志中的内容

/*!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
#180103 17:18:18 server id 503333  end_log_pos 120 CRC32 0xc4849c75     Start: binlog v 4, server v 5.6.25-log created 180103 17:18:18
# at 120
#180103 17:18:18 server id 503333  end_log_pos 231 CRC32 0x939fc1aa     Previous-GTIDs
# c5fd12db-51a6-11e7-aca0-5254006b6830:15-752227,
# ea24d53c-5315-11e7-b5fa-5254005bf35b:1-35
# at 231
#180103 17:18:47 server id 503333  end_log_pos 279 CRC32 0x30b89530     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:36'/*!*/;
# at 279
#180103 17:18:47 server id 503333  end_log_pos 400 CRC32 0x1ec5b51f     Query   thread_id=50    exec_time=0 error_code=0
use `test`/*!*/;
SET TIMESTAMP=1514971127/*!*/;
SET @@session.pseudo_thread_id=50/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table bai_test(id int, name varchar(10))
/*!*/;
# at 400
#180103 17:18:58 server id 503333  end_log_pos 448 CRC32 0x19f635b2     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:37'/*!*/;
# at 448
#180103 17:18:58 server id 503333  end_log_pos 520 CRC32 0xe7aa31d0     Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971138/*!*/;
BEGIN
/*!*/;
# at 520
#180103 17:18:58 server id 503333  end_log_pos 574 CRC32 0x362dc271     Table_map: `test`.`bai_test` mapped to number 88
# at 574
#180103 17:18:58 server id 503333  end_log_pos 616 CRC32 0x628eb69d     Write_rows: table id 88 flags: STMT_END_F
### INSERT INTO `test`.`bai_test`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 616
#180103 17:18:58 server id 503333  end_log_pos 647 CRC32 0xdb0901ec     Xid = 436
COMMIT/*!*/;
# at 647
#180103 17:19:06 server id 503333  end_log_pos 695 CRC32 0x97b5e19c     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:38'/*!*/;
# at 695
#180103 17:19:06 server id 503333  end_log_pos 767 CRC32 0xad5594e1     Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971146/*!*/;
BEGIN
/*!*/;
# at 767
#180103 17:19:06 server id 503333  end_log_pos 821 CRC32 0xdf345ef4     Table_map: `test`.`bai_test` mapped to number 88
# at 821
#180103 17:19:06 server id 503333  end_log_pos 870 CRC32 0xd6ed738d     Write_rows: table id 88 flags: STMT_END_F
### INSERT INTO `test`.`bai_test`
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### INSERT INTO `test`.`bai_test`
### SET
###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
###   @2='c' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 870
#180103 17:19:06 server id 503333  end_log_pos 901 CRC32 0xe9d9cad0     Xid = 437
COMMIT/*!*/;
# at 901
#180103 17:19:17 server id 503333  end_log_pos 949 CRC32 0xe9483334     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:39'/*!*/;
# at 949
#180103 17:19:17 server id 503333  end_log_pos 1021 CRC32 0x7254c52f    Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971157/*!*/;
BEGIN
/*!*/;
# at 1021
#180103 17:19:17 server id 503333  end_log_pos 1075 CRC32 0xc9b12a5b    Table_map: `test`.`bai_test` mapped to number 88
# at 1075
#180103 17:19:17 server id 503333  end_log_pos 1117 CRC32 0x8de21a6b    Delete_rows: table id 88 flags: STMT_END_F
### DELETE FROM `test`.`bai_test`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
###   @2='a' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 1117
#180103 17:19:17 server id 503333  end_log_pos 1148 CRC32 0x934c53d5    Xid = 438
COMMIT/*!*/;
# at 1148
#180103 17:19:30 server id 503333  end_log_pos 1196 CRC32 0x4e7491a3    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:40'/*!*/;
# at 1196
#180103 17:19:30 server id 503333  end_log_pos 1268 CRC32 0x8b46dbbd    Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971170/*!*/;
BEGIN
/*!*/;
# at 1268
#180103 17:19:30 server id 503333  end_log_pos 1322 CRC32 0x3aab8fa0    Table_map: `test`.`bai_test` mapped to number 88
# at 1322
#180103 17:19:30 server id 503333  end_log_pos 1373 CRC32 0xe6d359bf    Update_rows: table id 88 flags: STMT_END_F
### UPDATE `test`.`bai_test`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='b' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
###   @2='aa' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
# at 1373
#180103 17:19:30 server id 503333  end_log_pos 1404 CRC32 0xb23831b7    Xid = 439
COMMIT/*!*/;
# at 1404
#180103 17:20:14 server id 503333  end_log_pos 1452 CRC32 0x53de7665    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:41'/*!*/;
# at 1452
#180103 17:20:14 server id 503333  end_log_pos 1568 CRC32 0xb9750074    Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971214/*!*/;
alter table bai_test add age int default 0
/*!*/;
# at 1568
#180103 17:20:36 server id 503333  end_log_pos 1616 CRC32 0xb6bac377    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:42'/*!*/;
# at 1616
#180103 17:20:36 server id 503333  end_log_pos 1688 CRC32 0x1b426b00    Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971236/*!*/;
BEGIN
/*!*/;
# at 1688
#180103 17:20:36 server id 503333  end_log_pos 1743 CRC32 0xb0e95b72    Table_map: `test`.`bai_test` mapped to number 89
# at 1743
#180103 17:20:36 server id 503333  end_log_pos 1789 CRC32 0xe9e848f5    Write_rows: table id 89 flags: STMT_END_F
### INSERT INTO `test`.`bai_test`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2='d' /* VARSTRING(30) meta=30 nullable=1 is_null=0 */
###   @3=0 /* INT meta=0 nullable=1 is_null=0 */
# at 1789
#180103 17:20:36 server id 503333  end_log_pos 1820 CRC32 0xdb46e8d6    Xid = 442
COMMIT/*!*/;
# at 1820
#180103 17:20:41 server id 503333  end_log_pos 1868 CRC32 0xec165486    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'ea24d53c-5315-11e7-b5fa-5254005bf35b:43'/*!*/;
# at 1868
#180103 17:20:41 server id 503333  end_log_pos 1989 CRC32 0x73493b99    Query   thread_id=50    exec_time=0 error_code=0
SET TIMESTAMP=1514971241/*!*/;
DROP TABLE `bai_test` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
# at 1989
#180103 17:22:37 server id 503333  end_log_pos 2036 CRC32 0x29d0a279    Rotate to mysql-bin.000008  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;