荐 MySQL三种DDL方式的binlog区别

• 生活家

MySQL三种DDL方式的binlog记录形式

  • 原生ddl
  • pt-online-schema-change执行ddl
  • 某工具无锁表结构变更

测试环境： 

MySQL [ddltest]> show create table sbtest1\G
*************************** 1. row ***************************
       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

MySQL [ddltest]> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.41 sec)

MySQL [(none)]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

注：在执行DDL的同时会使用sysbench对sbtest1执行DML语句。

原生ddl

PS：不考虑原生的inplace、copy、instant方式，直接执行ddl语句。

sysbench开始执行DML语句...
[root@fcyecs bin]# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=xxxxxx --mysql-port=3306 --mysql-user=root --mysql-password=123456 --mysql-db=ddltest --tables=2 --table_size=1000000 --mysql_storage_engine=innodb --threads=2 --time=6000 --report-interval=10 --rand-type=uniform run
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 2
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!

执行ddl语句...
MySQL [ddltest]> alter table sbtest1 add index idx_sbtest1_c(c);
Query OK, 0 rows affected (53.67 sec)
Records: 0  Duplicates: 0  Warnings: 0

分析binlog：

SET @@SESSION.GTID_NEXT= '56f40a96-bc64-11ea-b700-00163e0b98f1:1102372'/*!*/;
#at 52718151
#200706 1:05:46 server id 3306 end_log_pos 52718273 Query thread_id=423 exec_time=54error_code=0
SET TIMESTAMP=1593968746/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
alter table sbtest1 add index idx_sbtest1_c(c)
/*!*/;
#at 52718273
#200706 1:06:40 server id 3306 end_log_pos 52718334 GTID last_committed=24216 sequence_number=24217 rbr_only=yes

测试结果：直接以原语句的形式记录binlog

pt-online-schema-change执行ddl

同样，先使用sysbench执行DML语句…
使用pt-osc执行ddl语句

[root@fcyecs ~]# pt-online-schema-change h=127.0.0.1,P=3306,u=root,D=ddltest,t=sbtest1 --alter 'add index idx_sbtest1_c(c)' --ask-pass --print --execute
Enter MySQL password:
No slaves found.  See --recursion-method if host fcyecs has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  。。。。。。

分析binlog
ps:(ddl执行过程中的DML事务省略…）
只截取有用信息

**创建一张相同表结构的表**
#200706 1:39:06 server id 3306 end_log_pos 349764171 Query thread_id=445 exec_time=0 error_code=0
use `ddltest`/*!*/;
SET TIMESTAMP=1593970746/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
CREATE TABLE `ddltest`.`_sbtest1_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4
/*!*/;

…DML事务略

**在新的表上添加索引**
SET TIMESTAMP=1593970746/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
ALTER TABLE `ddltest`.`_sbtest1_new` add index idx_sbtest1_c(c)
/*!*/;

…DML事务略

**创建三个TRIGGER对应delete/update/insert，用于同步新数据。**
SET TIMESTAMP=1593970746.455339/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_ddltest_sbtest1_del` AFTER DELETE ON `ddltest`.`sbtest1` FOR EACH ROW DELETE IGNORE FROM `ddltest`.`_sbtest1_new` WHERE `ddltest`.`_sbtest1_new`.`id` <=> OLD.`id`

SET TIMESTAMP=1593970746.467497/*!*/;
CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_ddltest_sbtest1_upd` AFTER UPDATE ON `ddltest`.`sbtest1` FOR EACH ROW BEGIN DELETE IGNORE FROM `ddltest`.`_sbtest1_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `ddltest`.`_sbtest1_new`.`id` <=> OLD.`id`;REPLACE INTO `ddltest`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END

SET TIMESTAMP=1593970746.475594/*!*/;
SET @@session.sql_mode=1437073440/*!*/;
CREATE DEFINER=`root`@`%` TRIGGER `pt_osc_ddltest_sbtest1_ins` AFTER INSERT ON `ddltest`.`sbtest1` FOR EACH ROW REPLACE INTO `ddltest`.`_sbtest1_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`)
/*!*/;

**copy数据**
#at 349770541
#200706 1:39:06 server id 3306 end_log_pos 349770602 GTID last_committed=88728 sequence_number=88729 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '56f40a96-bc64-11ea-b700-00163e0b98f1:1166885'/*!*/;
#at 349770602
#200706 1:39:06 server id 3306 end_log_pos 349770673 Query thread_id=445 exec_time=0 error_code=0
SET TIMESTAMP=1593970746/*!*/;
BEGIN
/*!*/;
#at 349770673
#200706 1:39:06 server id 3306 end_log_pos 349770734 Table_map: `ddltest`.`_sbtest1_new` mapped to number 445
#at 349770734
#200706 1:39:06 server id 3306 end_log_pos 349778935 Write_rows: table id 445
#at 349778935
#200706 1:39:06 server id 3306 end_log_pos 349787136 Write_rows: table id 445
#at 349787136
#200706 1:39:06 server id 3306 end_log_pos 349795337 Write_rows: table id 445
#at 349795337
#200706 1:39:06 server id 3306 end_log_pos 349803538 Write_rows: table id 445
#at 349803538
省略...
###INSERT INTO `ddltest`.`_sbtest1_new`
###SET
###@1=1 /* INT meta=0 nullable=0 is_null=0 */
###@2=499284 /* INT meta=0 nullable=0 is_null=0 */
###@3='83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330' /* STRING(480) meta=61152 nullable=0 is_null=0 */
###@4='67847967377-48000963322-62604785301-91415491898-96926520291' /* STRING(240) meta=65264 nullable=0 is_null=0 */
###INSERT INTO `ddltest`.`_sbtest1_new`
###SET
###@1=2 /* INT meta=0 nullable=0 is_null=0 */
###@2=501969 /* INT meta=0 nullable=0 is_null=0 */
###@3='38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630' /* STRING(480) meta=61152 nullable=0 is_null=0 */
###@4='23183251411-36241541236-31706421314-92007079971-60663066966' /* STRING(240) meta=65264 nullable=0 is_null=0 */
省略...

**正在同步新数据**
###INSERT INTO `ddltest`.`sbtest1`
###SET
###@1=290377 /* INT meta=0 nullable=0 is_null=0 */
###@2=328802 /* INT meta=0 nullable=0 is_null=0 */
###@3='46927550081-50261911550-22306374036-53442159245-13707922973-93695117126-67482185754-05598448503-77638896641-48903360706' /* STRING(480) meta=61152 nullable=0 is_null=0 */
###@4='48471917516-73000103461-33816391213-28677851269-10819436583' /* STRING(240) meta=65264 nullable=0 is_null=0 */
###INSERT INTO `ddltest`.`_sbtest1_new`
###SET
###@1=290377 /* INT meta=0 nullable=0 is_null=0 */
###@2=328802 /* INT meta=0 nullable=0 is_null=0 */
###@3='46927550081-50261911550-22306374036-53442159245-13707922973-93695117126-67482185754-05598448503-77638896641-48903360706' /* STRING(480) meta=61152 nullable=0 is_null=0 */
###@4='48471917516-73000103461-33816391213-28677851269-10819436583' /* STRING(240) meta=65264 nullable=0 is_null=0 */

**重命名表**
SET TIMESTAMP=1593970846/*!*/;
RENAME TABLE `ddltest`.`sbtest1` TO `ddltest`.`_sbtest1_old`, `ddltest`.`_sbtest1_new` TO `ddltest`.`sbtest1`
/*!*/;

**删除旧表**
SET TIMESTAMP=1593970847/*!*/;
DROP TABLE IF EXISTS `_sbtest1_old` /* generated by server */
/*!*/;

**删除TRIGGER**
SET TIMESTAMP=1593970847/*!*/;
DROP TRIGGER IF EXISTS `ddltest`.`pt_osc_ddltest_sbtest1_del`
/*!*/;
SET TIMESTAMP=1593970847/*!*/;
DROP TRIGGER IF EXISTS `ddltest`.`pt_osc_ddltest_sbtest1_upd`
/*!*/;
SET TIMESTAMP=1593970847/*!*/;
DROP TRIGGER IF EXISTS `ddltest`.`pt_osc_ddltest_sbtest1_ins`
/*!*/;

DDL完成！

所以pt-osc执行ddl的流程为：
1.创建一张相同表结构的表
2.在新的表上添加索引
3.创建三个TRIGGER对应delete/update/insert，用于同步新数据。
4.copy数据
5.正在同步新数据
6.重命名表
7.删除旧表
8.删除TRIGGER

某工具无锁表结构变更

XXX工具里面有一个无锁表结构变更的功能，我们来看看它是怎么执行的。
同样，先使用sysbench执行DML语句，同时，XXX工具使用无锁表结构变更功能执行ddl语句。
语句为:

 alter table sbtest1 add index idx_sbtest1_c(c);

接下来分析binlog

创建一张日志记录表，用来记录主要事件以及心跳检测记录
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.explicit_defaults_for_timestamp=0/*!*/;
/* query from dms-toolkit */
 CREATE TABLE `ddltest`.`tp_248836_ogl_sbtest1` (
        id BIGINT auto_increment,
        last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        `key` VARCHAR (64) charset utf8 NOT NULL,
        `value` VARCHAR (4096) charset utf8 NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY key_uidx (`key`)
) auto_increment = 512
/*!*/;

将心跳检测记录到日志记录表
#at 10726974
#200706 19:01:27 server id 2306050241  end_log_pos 10727049 CRC32 0x288339c2    Table_map: `ddltest`.`tp_248836_ogl_sbtest1` mapped to number 140
#at 10727049
#200706 19:01:27 server id 2306050241  end_log_pos 10727135 CRC32 0xfb045e57    Write_rows: table id 140 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogl_sbtest1`
###SET
###@1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033287 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='HEARTBEAT' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='2020-07-06T19:01:27.834+0800' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

更新心跳检测记录
#at 10736373
#200706 19:01:27 server id 2306050241  end_log_pos 10736448 CRC32 0x82a27333    Table_map: `ddltest`.`tp_248836_ogl_sbtest1` mapped to number 140
#at 10736448
#200706 19:01:27 server id 2306050241  end_log_pos 10736607 CRC32 0x67965a48    Update_rows: table id 140 flags: STMT_END_F
###UPDATE `ddltest`.`tp_248836_ogl_sbtest1`
###WHERE
###@1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033287 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='HEARTBEAT' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='2020-07-06T19:01:27.834+0800' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */
###SET
###@1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033287 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='HEARTBEAT' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='2020-07-06T19:01:27.863+0800|146096334982146744' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

创建一张和原表相同表结构的影子表
SET TIMESTAMP=1594033287/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
/* query from dms-toolkit */ CREATE TABLE `ddltest`.`tp_248836_ogt_sbtest1` LIKE `ddltest`.`sbtest1`
/*!*/;

在影子表上执行ddl操作
SET TIMESTAMP=1594033288/*!*/;
SET @@session.sql_mode=2097152/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
ALTER TABLE `ddltest`.tp_248836_ogt_sbtest1
        ADD INDEX idx_sbtest1_c (c)
/*!*/;

更新日志记录表
#at 10822638
#200706 19:01:28 server id 2306050241  end_log_pos 10822712 CRC32 0x3e02acfd    Write_rows: table id 140 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogl_sbtest1`
###SET
###@1=512 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033288 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='STATE' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='GHOST_TABLE_MIGRATED' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

更新日志记录表
#at 10829994
#200706 19:01:28 server id 2306050241  end_log_pos 10830082 CRC32 0x1a35d025    Write_rows: table id 140 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogl_sbtest1`
###SET
###@1=513 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033288 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='STATE@1594033288181' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='GHOST_TABLE_MIGRATED' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

更新心跳检测记录
#at 11029204
#200706 19:01:28 server id 2306050241  end_log_pos 11029382 CRC32 0xb0a0528d    Update_rows: table id 140 flags: STMT_END_F
###UPDATE `ddltest`.`tp_248836_ogl_sbtest1`
###WHERE
###@1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033287 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='HEARTBEAT' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='2020-07-06T19:01:27.863+0800|146096334982146744' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */
###SET
###@1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594033288 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='HEARTBEAT' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='2020-07-06T19:01:28.863+0800|146096335981760040' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

应用主库增量binlog
#at 12296646
#200706 19:01:33 server id 2306050241  end_log_pos 12296720 CRC32 0xf36b177b    Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
#at 12296720
#200706 19:01:33 server id 2306050241  end_log_pos 12296943 CRC32 0xa1c608ae    Write_rows: table id 142 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
###SET
###@1=274911 /* INT meta=0 nullable=0 is_null=0 */
###@2=115898 /* INT meta=0 nullable=0 is_null=0 */
###@3='40818783686-20279785140-01205620856-14433259598-85683719388-03456857794-30001783422-83180306451-47272109845-17271061059' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###@4='66573657612-99082333769-58455052591-34963190253-44627866414' /* STRING(180) meta=65204 nullable=0 is_null=0 */
#at 12296943

#at 12297166
#200706 19:01:33 server id 2306050241  end_log_pos 12297240 CRC32 0x7b43eca9    Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
#at 12297240
#200706 19:01:33 server id 2306050241  end_log_pos 12297463 CRC32 0x48245b8d    Write_rows: table id 142 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
###SET
###@1=964977 /* INT meta=0 nullable=0 is_null=0 */
###@2=529758 /* INT meta=0 nullable=0 is_null=0 */
###@3='32822290699-06857476119-45045202545-13588229922-87097689684-31912700607-04293802262-64292865024-13806956713-89941612263' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###@4='44818294269-54605386204-97913403172-60863419925-08333908055' /* STRING(180) meta=65204 nullable=0 is_null=0 */


#at 12299766
#200706 19:01:33 server id 2306050241  end_log_pos 12299840 CRC32 0xa44a60db    Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
#at 12299840
#200706 19:01:33 server id 2306050241  end_log_pos 12300063 CRC32 0xd3fd4900    Write_rows: table id 142 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
###SET
###@1=255661 /* INT meta=0 nullable=0 is_null=0 */
###@2=204172 /* INT meta=0 nullable=0 is_null=0 */
###@3='26038250664-11517142087-02308093593-72804739680-40045520323-38337004123-01251830948-26185567475-60354904216-50493803355' /* STRING(360) meta=61032 nullable=0 is_null=0 */
###@4='55811629484-51942669846-65505486834-72775973167-90765937460' /* STRING(180) meta=65204 nullable=0 is_null=0 */
以下省略.....

进行row copy
#at 12395470
#200706 19:01:33 server id 2306050241  end_log_pos 12395544 CRC32 0xa64b9f41    Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
#at 12395544
#200706 19:01:33 server id 2306050241  end_log_pos 12403747 CRC32 0x1088b99b    Write_rows: table id 142
#at 12403747
#200706 19:01:33 server id 2306050241  end_log_pos 12411950 CRC32 0x5d077926    Write_rows: table id 142
#at 12411950
#200706 19:01:33 server id 2306050241  end_log_pos 12420153 CRC32 0xffd5a0a0    Write_rows: table id 142
#at 12420153
#200706 19:01:33 server id 2306050241  end_log_pos 12428356 CRC32 0xa8b9a2f3    Write_rows: table id 142
#at 12428356
#200706 19:01:33 server id 2306050241  end_log_pos 12436559 CRC32 0x25c28efe    Write_rows: table id 142
..............
6294870 ### INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
6294871 ### SET
6294872 ###   @1=894482 /* INT meta=0 nullable=0 is_null=0 */
6294873 ###   @2=498585 /* INT meta=0 nullable=0 is_null=0 */
6294874 ###   @3='49987335065-07151324577-44413537234-31053006718-46963308886-41077713721-13001549626-96643473463-12654892734-99998290041' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6294875 ###   @4='56170656272-46407920391-02515208012-31712559314-62727623773' /* STRING(180) meta=65204 nullable=0 is_null=0 */
6294876 ### INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
6294877 ### SET
6294878 ###   @1=894483 /* INT meta=0 nullable=0 is_null=0 */
6294879 ###   @2=498656 /* INT meta=0 nullable=0 is_null=0 */
6294880 ###   @3='61202698120-54652288901-47026418081-32831339336-09062989361-25837040969-42207679908-50533107894-22000019900-91693187633' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6294881 ###   @4='60403978593-21483035589-06068620880-00743363195-07343324142' /* STRING(180) meta=65204 nullable=0 is_null=0 */
6294882 ### INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
省略..............
6977587 ### INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
6977588 ### SET
6977589 ###   @1=999999 /* INT meta=0 nullable=0 is_null=0 */
6977590 ###   @2=487815 /* INT meta=0 nullable=0 is_null=0 */
6977591 ###   @3='94331687575-78393898598-13346494489-49644554650-34763630251-44848645016-41563526775-35152167869-42972495560-80152856053' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6977592 ###   @4='86175505093-60755257669-78470722542-98850659631-87500608115' /* STRING(180) meta=65204 nullable=0 is_null=0 */
6977593 ### INSERT INTO `ddltest`.`tp_248836_ogt_sbtest1`
6977594 ### SET
6977595 ###   @1=1000000 /* INT meta=0 nullable=0 is_null=0 */
6977596 ###   @2=497681 /* INT meta=0 nullable=0 is_null=0 */
6977597 ###   @3='24370733566-51322813884-74586826122-88962939071-35932193453-18408167444-46946055568-46329009755-48767794996-38200513642' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6977598 ###   @4='55621940731-62771903506-33698028735-11247044148-56383000519' /* STRING(180) meta=65204 nullable=0 is_null=0 */
以下省略....

同时应用增量binlog，可以看出来row copy和增量binlog应用是同时进行的
6978920 # at 214711853
6978921 #200706 19:02:15 server id 2306050241  end_log_pos 214711927 CRC32 0xa5818ad8   Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
6978922 # at 214711927
6978923 #200706 19:02:15 server id 2306050241  end_log_pos 214712150 CRC32 0x1559441f   Delete_rows: table id 142 flags: STMT_END_F
6978924 ### DELETE FROM `ddltest`.`tp_248836_ogt_sbtest1`
6978925 ### WHERE
6978926 ###   @1=996411 /* INT meta=0 nullable=0 is_null=0 */
6978927 ###   @2=707746 /* INT meta=0 nullable=0 is_null=0 */
6978928 ###   @3='41753795778-79167425329-46812674811-15985206950-42706449745-18378469200-31649402330-19606351852-11823262981-29324952196' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6978929 ###   @4='06768012485-05559016822-44138562320-50399103522-50342792218' /* STRING(180) meta=65204 nullable=0 is_null=0 */
.........
6978945 # at 214712373
6978946 #200706 19:02:15 server id 2306050241  end_log_pos 214712447 CRC32 0xcfe14e25   Table_map: `ddltest`.`tp_248836_ogt_sbtest1` mapped to number 142
6978947 # at 214712447
6978948 #200706 19:02:15 server id 2306050241  end_log_pos 214712861 CRC32 0x92997996   Update_rows: table id 142 flags: STMT_END_F
6978949 ### UPDATE `ddltest`.`tp_248836_ogt_sbtest1`
6978950 ### WHERE
6978951 ###   @1=532501 /* INT meta=0 nullable=0 is_null=0 */
6978952 ###   @2=503184 /* INT meta=0 nullable=0 is_null=0 */
6978953 ###   @3='05598111028-46683598518-95663159093-77706291102-07620574528-75031390891-73158109220-30585011482-76748937417-24807247078' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6978954 ###   @4='34234299232-21255390724-37814052016-91526638529-71461022051' /* STRING(180) meta=65204 nullable=0 is_null=0 */
6978955 ### SET
6978956 ###   @1=532501 /* INT meta=0 nullable=0 is_null=0 */
6978957 ###   @2=503185 /* INT meta=0 nullable=0 is_null=0 */
6978958 ###   @3='05598111028-46683598518-95663159093-77706291102-07620574528-75031390891-73158109220-30585011482-76748937417-24807247078' /* STRING(360) meta=61032 nullab le=0 is_null=0 */
6978959 ###   @4='34234299232-21255390724-37814052016-91526638529-71461022051' /* STRING(180) meta=65204 nullable=0 is_null=0 */
以下省略.....

创建一张和原表相同表结构的del表
6997538 /*!\C utf8 *//*!*/;
6997539 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
6997540 /* query from dms-toolkit */ CREATE TABLE `ddltest`.`tp_248836_del_sbtest1` LIKE `ddltest`.`sbtest1`
6997541 /*!*/;

在日志记录表插入新记录，表示锁定之前的所有事件已处理完成
#at 190796233
#200706 16:54:59 server id 2306050241  end_log_pos 190796308 CRC32 0xe80bd1b4   Table_map: `ddltest`.`tp_248756_ogl_sbtest1` mapped to number 130
#at 190796308
#200706 16:54:59 server id 2306050241  end_log_pos 190796407 CRC32 0x95bc6d21   Write_rows: table id 130 flags: STMT_END_F
###INSERT INTO `ddltest`.`tp_248756_ogl_sbtest1`
###SET
###@1=515 /* LONGINT meta=0 nullable=0 is_null=0 */
###@2=1594025699 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
###@3='STATE@1594025699546' /* VARSTRING(192) meta=192 nullable=0 is_null=0 */
###@4='ALL_EVENTS_UP_TO_LOCK_PROCESSED' /* VARSTRING(12288) meta=12288 nullable=0 is_null=0 */

删除del表
6048328 # at 190797046
6048329 #200706 16:55:00 server id 2306050241 end_log_pos 190797199 CRC32 0x0aa11191 Query thread_id=4158 exec_time=0 error_code=0
6048330 SET TIMESTAMP=1594025700/*!*/;
6048331 DROP TABLE IF EXISTS `ddltest`.`tp_248756_del_sbtest1` /* generated by server */
6048332 /*!*/;

把原表名称改为del表的表名，把影子表改为原表名称
6048339 # at 190797323
6048340 #200706 16:54:59 server id 2306050241 end_log_pos 190797577 CRC32 0x97e648a6 Query thread_id=4157 exec_time=1 error_code=0
6048341 SET TIMESTAMP=1594025699/*!*/;
6048342 /* query from dms-toolkit */  /* rename-248756-4157 */RENAME TABLE `ddltest`.`sbtest1` to `ddltest`.`tp_248756_del_sbtest1`, `ddltest`.`tp_248756_ogt_sbtest1` to `ddltest`.`sbtest1`
6048343 /*!*/;

删除日志记录表
6048350 # at 190797701
6048351 #200706 16:55:01 server id 2306050241 end_log_pos 190797844 CRC32 0x9ccc4da0 Query thread_id=4095 exec_time=0 error_code=0
6048352 SET TIMESTAMP=1594025701/*!*/;
6048353 DROP TABLE `ddltest`.`tp_248756_ogl_sbtest1` /* generated by server */
6048354 /*!*/;

删除和del表(即原表)
6048403 # at 190798673
6048404 #200706 16:55:02 server id 2306050241 end_log_pos 190798816 CRC32 0xb395693b Query thread_id=4095 exec_time=1 error_code=0
6048405 SET TIMESTAMP=1594025702/*!*/;
6048406 DROP TABLE `ddltest`.`tp_248756_del_sbtest1` /* generated by server */
6048407 /*!*/;

完成！

XXX无锁执行DDL总结步骤：
1.连接到主库或从库添加binlog监听，用于拉取增量binlog日志
2.创建日志记录表(记录心跳等重要事件)和影子表(和原表结构相同)，同时更新日志记录表
3.在影子表上执行DDL语句
4.开始迁移数据：row copy和binlog apply同时进行
5.交换表名(cut-over)
6.删除原表和日志记录表，关闭binlog监听

个别步骤详细解释：
4：执行第四步时，数据变量有三种，A是原表的row copy，B是原表的DML操作，C是apply的binlog
B操作会记录binlog从而触发C，所以C操作一定是在B之后的。

在进行row copy时，会用select语句对原表进行主键扫描，把select语句转换为新表上的insert ignore into
在进行binlog apply时，会把原表的insert转换为replace into。
如果update/delete的数据还没有通过row copy写入到新表，那后等到数据写入到新表之后再执行。

5：该交换表名的过程利用了Mysql的原子性rename请求，在block时优先级最高，一个连接对原表加锁，另一个连接尝试rename操作，此时会触发MDL锁等待，当MDL锁释放时，rename被优先执行。
而前面一起阻塞的请求，会被放到新表上去执行。

本文地址：https://blog.csdn.net/qq_38114620/article/details/107148479

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐