MySQL的binlog的ROW模式解析
在mysql5.6以后，对主从数据一致性要求变高了，statement格式逐渐不太适合业务的需求了，所以生产环境大家都采用了row模式，row模式是传输最底层的数据变化的insert的模块来进行主从数据的传输，那么在binlog里面就和普通的statement模式有何差别？能否看到最原始的sql语句呢？
1、准备录入数据
mysql> create table test1(id int,c1 varchar(20),type int,address varchar(20),create_time datetime); Query OK, 0 rows affected (0.00 sec) mysql> insert into test1 select 1,'zhangsan','1','zhangsan road No 870,floor 602',now(); ERROR 1406 (22001): Data too long for column 'address' at row 1 mysql> mysql> show create table test1; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test1 | CREATE TABLE `test1` ( `id` int(11) DEFAULT NULL, `c1` varchar(20) DEFAULT NULL, `type` int(11) DEFAULT NULL, `address` varchar(20) DEFAULT NULL, `create_time` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table test1 modify `address` varchar(200) DEFAULT NULL; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into test1 select 1,'zhangsan','1','zhangsan road No 870,floor 602',now(); Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into test1 select 2,'lisi','1','zhangsan road No 870,floor 602',now(); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql>
|
2、Row模式binlog是乱码
Row模式下面，binlog里面的ddl语句是正常显示的，但是dml是乱码，如下所示：
[root@hch_test_dbm1_121_63 binlog]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000215 …… # 这里前面的省略 #160722 17:02:38 server id 62 end_log_pos 4291 CRC32 0x369e3244 Query thread_id=60 exec_time=4294967271 error_code=0 SET TIMESTAMP=1469178158/*!*/; CREATE DATABASE IF NOT EXISTS `percona` /* pt-table-checksum */ /*!*/; # at 4291 #160722 17:02:38 server id 62 end_log_pos 5079 CRC32 0x8abc6e67 Query thread_id=60 exec_time=4294967271 error_code=0 use `percona`/*!*/; SET TIMESTAMP=1469178158/*!*/; CREATE TABLE IF NOT EXISTS `percona`.`checksums` ( db char(64) NOT NULL, tbl char(64) NOT NULL, chunk int NOT NULL, chunk_time float NULL, chunk_index varchar(200) NULL, lower_boundary text NULL, upper_boundary text NULL, this_crc char(40) NOT NULL, this_cnt int NOT NULL, master_crc char(40) NULL, master_cnt int NULL, ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB /*!*/; # at 5079 #160820 10:21:10 server id 63 end_log_pos 5280 CRC32 0xd147bd8e Query thread_id=16 exec_time=0 error_code=0 SET TIMESTAMP=1471659670/*!*/; SET @@session.sql_mode=1075838976/*!*/; GRANT ALL PRIVILEGES ON *.* TO 'tim'@'192.168%' IDENTIFIED BY PASSWORD '*2976819BD2CCD13612E03F812A2CD297C1A18B23' /*!*/; # at 5280 #160820 10:22:40 server id 63 end_log_pos 5445 CRC32 0x85811be7 Query thread_id=18 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1471659760/*!*/; create table test1(id int,c1 varchar(20),type int,address varchar(20),create_time datetime) /*!*/; # at 5445 #160820 10:24:34 server id 63 end_log_pos 5580 CRC32 0x2626220c Query thread_id=18 exec_time=0 error_code=0 SET TIMESTAMP=1471659874/*!*/; alter table test1 modify `address` varchar(200) DEFAULT NULL /*!*/; # at 5580 #160820 10:24:36 server id 63 end_log_pos 5660 CRC32 0x7b7c645f Query thread_id=18 exec_time=0 error_code=0 SET TIMESTAMP=1471659876/*!*/; SET @@session.time_zone='SYSTEM'/*!*/; BEGIN /*!*/; # at 5660 # at 5764 #160820 10:24:36 server id 63 end_log_pos 5821 CRC32 0x08bc94c3 Table_map: `test`.`test1` mapped to number 74 # at 5821 #160820 10:24:36 server id 63 end_log_pos 5911 CRC32 0x2f577f52 Write_rows: table id 74 flags: STMT_END_F
BINLOG ' ZL+3VxM/AAAAOQAAAL0WAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB/DlLwI ZL+3Vx4/AAAAWgAAABcXAAAAAEoAAAAAAAEAAgAF/+ABAAAACHpoYW5nc2FuAQAAAB4Aemhhbmdz YW4gcm9hZCBObyA4NzAsZmxvb3IgNjAymZoopiRSf1cv '/*!*/; # at 5911 #160820 10:24:36 server id 63 end_log_pos 5942 CRC32 0xb26af81b Xid = 199 COMMIT/*!*/; # at 5942 #160820 10:24:48 server id 63 end_log_pos 6022 CRC32 0x09eab31d Query thread_id=18 exec_time=0 error_code=0 SET TIMESTAMP=1471659888/*!*/; BEGIN /*!*/; # at 6022 # at 6122 #160820 10:24:48 server id 63 end_log_pos 6179 CRC32 0xdc6dc34b Table_map: `test`.`test1` mapped to number 74 # at 6179 #160820 10:24:48 server id 63 end_log_pos 6265 CRC32 0x5f7ad700 Write_rows: table id 74 flags: STMT_END_F
BINLOG ' cL+3VxM/AAAAOQAAACMYAAAAAEoAAAAAAAEABHRlc3QABXRlc3QxAAUDDwMPEgU8AFgCAB9Lw23c cL+3Vx4/AAAAVgAAAHkYAAAAAEoAAAAAAAEAAgAF/+ACAAAABGxpc2kBAAAAHgB6aGFuZ3NhbiBy b2FkIE5vIDg3MCxmbG9vciA2MDKZmiimMADXel8= '/*!*/; # at 6265 #160820 10:24:48 server id 63 end_log_pos 6296 CRC32 0xf6833d28 Xid = 200 COMMIT/*!*/; # at 6296 #16 您可能想查找下面的文章:最近更新的内容
|