pt-online-schema-change 添加字段过程解析

保持热爱奔赴山海

发布于 2019-09-18 10:08:24

1.2K0

发布于 2019-09-18 10:08:24

文章被收录于专栏：数据库相关

对于大表的 DDL操作，我们一般使用 pt-online-schema-change 来进行。

具体的操作步骤如下：

1、创建一张新表_xxx_new ，对其做DDL操作

2、创建3个触发器(delete\update\insert)，在复制数据开始之后，将对源数据表继续进行数据修改的操作记录下来，以便在数据复制结束后执行这些操作，保证数据不会丢失

3、复制数据，从源数据表复制数据到新表（分成多个chunk，小事务提交）

4、修改外键相关的子表，根据修改后的数据，修改外键关联的子表

5、将源数据表重命名为old表，将新表更改为源表名

6、删除原表

7、删除触发器

下面看一个例子，来亲自验证下这个过程。

如下是一条DDL测试语句：

ALTER TABLE tb_2 ADD COLUMN content text ;

对应的pt-osc写法如下：

pt-online-schema-change --user=root --password=123456 -h localhost --alter "ADD COLUMN content text" D=db1,t=tb_2--no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --charset=utf8 --execute

下面是我在使用pt-osc执行上述DDL时候，generallog里面记录的内容（部分不太重要的地方有删减..）

Connectroot@localhost on db1
 set autocommit=1
 SELECT @@SQL_MODE
 /*!40101 SET NAMES "utf8"*/
 SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
 SET SESSION innodb_lock_wait_timeout=1
 SHOW VARIABLES LIKE 'lock\_wait_timeout'
 SET SESSION lock_wait_timeout=60
 SHOW VARIABLES LIKE 'wait\_timeout'
 SET SESSION wait_timeout=10000
 SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
 SELECT @@server_id /*!50038 , @@hostname*/
Connectroot@localhost on db1
 set autocommit=1
 SELECT @@SQL_MODE
 /*!40101 SET NAMES "utf8"*/
 SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
 SET SESSION innodb_lock_wait_timeout=1
 SHOW VARIABLES LIKE 'lock\_wait_timeout'
 SET SESSION lock_wait_timeout=60
 SHOW VARIABLES LIKE 'wait\_timeout'
 SET SESSION wait_timeout=10000
 SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION'*/
 SELECT @@server_id /*!50038 , @@hostname*/
 SHOW VARIABLES LIKE 'wsrep_on'
 SHOW VARIABLES LIKE 'version%'
 SHOW ENGINES
 SHOW VARIABLES LIKE 'innodb_version'
 SHOW GLOBAL STATUS LIKE 'Threads_running'
 SHOW GLOBAL STATUS LIKE 'Threads_running'
 SELECT CONCAT(@@hostname, @@port)
 SHOW VARIABLES
 SHOW TABLES FROM `db1` LIKE 'tb_2'
### 查看原表是否已存在触发器
 SHOW TRIGGERS FROM `db1` LIKE 'tb_2'
 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
 USE `db1`
 SHOW CREATE TABLE `db1`.`tb_2`
 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
 EXPLAIN SELECT * FROM `db1`.`tb_2` WHERE 1=1
 SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='db1' AND referenced_table_name='tb_2'
 SHOW VARIABLES LIKE 'wsrep_on'
 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
 USE `db1`
 SHOW CREATE TABLE `db1`.`tb_2`
 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
#### 创建新表，并对其做ALTER操作
 CREATE TABLE `db1`.`_tb_2_new` (
  `id` bigint(20) NOT NULL,
  `url` varchar(2048) NOT NULL DEFAULT '',
  `appid` smallint(6) NOT NULL,
  `rand_code` int(11) NOT NULL DEFAULT '0' COMMENT '随机码',
  `create_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
 ALTER TABLE `db1`.`_tb_2_new` ADD COLUMN content text
 /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
 USE `db1`
 SHOW CREATE TABLE `db1`.`_tb_2_new`
 /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
##### 创建3个触发器(delete、update、insert) (在原表上update，新临时表上是replace into整行数据，所以达到有则更新，无则插入。同时配合后面的 insert ignore，保证这条数据不会因为重复而失败)
 CREATE TRIGGER `pt_osc_db1_tb_2_del` AFTER DELETE ON `db1`.`tb_2` FOR EACH ROW DELETE IGNORE FROM `db1`.`_tb_2_new` WHERE `db1`.`_tb_2_new`.`id` <=> OLD.`id`
CREATE TRIGGER `pt_osc_db1_tb_2_upd` AFTER UPDATE ON `db1`.`tb_2` FOR EACH ROW REPLACE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) VALUES (NEW.`id`, NEW.`url`, NEW.`appid`, NEW.`rand_code`, NEW.`create_time`)
CREATE TRIGGER `pt_osc_db1_tb_2_ins` AFTER INSERT ON `db1`.`tb_2` FOR EACH ROW REPLACE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) VALUES (NEW.`id`, NEW.`url`, NEW.`appid`, NEW.`rand_code`, NEW.`create_time`)
 EXPLAIN SELECT * FROM `db1`.`tb_2` WHERE 1=1
 SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1 /*first lower boundary*/
 SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX (`PRIMARY`) WHERE `id` IS NOT NULL ORDER BY `id` LIMIT 1 /*key_len*/
 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ * FROM `db1`.`tb_2` FORCE INDEX (`PRIMARY`) WHERE `id` >= '20000000' /*key_len*/
### 分块查询数据，减小后续操作的持锁范围
 EXPLAIN SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
 SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) ORDER BY `id` LIMIT 999, 2 /*next chunk boundary*/
 EXPLAIN SELECT `id`, `url`, `appid`, `rand_code`, `create_time` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) AND ((`id` <= '20000999')) LOCK IN SHARE MODE /*explain pt-online-schema-change 12296 copy nibble*/
### 开始灌数据操作
 INSERT LOW_PRIORITY IGNORE INTO `db1`.`_tb_2_new` (`id`, `url`, `appid`, `rand_code`, `create_time`) SELECT `id`, `url`, `appid`, `rand_code`, `create_time` FROM `db1`.`tb_2` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '20000000')) AND ((`id` <= '20000999')) LOCK IN SHARE MODE /*pt-online-schema-change 12296 copy nibble*/  
 SHOW WARNINGS
 SHOW GLOBAL STATUS LIKE 'Threads_running'
 。。。 对于操作期间有数据INSERT、UPDATE写入的话，这里还会出现REPLACE INTO 类型的SQL语句 。。。
#### 重命名新、老表名(这个操作期间是锁表的，时间很短暂)
 RENAME TABLE `db1`.`tb_2` TO `db1`.`_tb_2_old`, `db1`.`_tb_2_new` TO `db1`.`tb_2`
#### 删除原表
 DROP TABLE IF EXISTS `db1`.`_tb_2_old`
#### 删除触发器
 DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_del`
 DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_upd`
 DROP TRIGGER IF EXISTS `db1`.`pt_osc_db1_tb_2_ins`
 SHOW TABLES FROM `db1` LIKE '\_tb_2\_new'
Quit

Quit

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2017/10/21 ，如有侵权请联系 cloudcommunity@tencent.com 删除

add