pt-osc原理

1、检查设置环境

测试db是否可连通,并且验证database是否存在

SET SESSION innodb_lock_wait_timeout=1 //InnoDB事务等待行锁的超时时间

SET SESSION lock_wait_timeout=60  //设置获取元数据琐超时为60s

SET SESSION wait_timeout=10000   //交互超时时间

权限验证 show grants for current_user()

2、创建临时表_tablename_new并修改临时表结构

3、在源表创建三个触发器

--创建delete触发器

CREATE TRIGGER `pt_osc_darren_t_user_del` AFTER DELETE ON `darren`.`t_user` FOR EACH ROW DELETE IGNORE FROM `darren`.`_t_user_new`

WHERE `darren`.`_t_user_new`.`id` <=> OLD.`id`

--创建update触发器

CREATE TRIGGER `pt_osc_darren_t_user_upd` AFTER UPDATE ON `darren`.`t_user` FOR EACH ROW

BEGIN

    DELETE IGNORE FROM `darren`.`_t_user_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `darren`.`_t_user_new`.`id` <=> OLD.`id`;

    REPLACE INTO `darren`.`_t_user_new` (`id`, `name`, `phone`, `gender`, `type`, `birth`, `is_delete`, `c1`, `c2`, `c3`)

    VALUES (NEW.`id`, NEW.`name`, NEW.`phone`, NEW.`gender`, NEW.`type`, NEW.`birth`, NEW.`is_delete`, NEW.`c1`, NEW.`c2`, NEW.`c3`);

END;

--创建insert触发器

CREATE TRIGGER `pt_osc_darren_t_user_ins` AFTER INSERT ON `darren`.`t_user`

  FOR EACH ROW

     REPLACE INTO `darren`.`_t_user_new` (`id`, `name`, `phone`, `gender`, `type`, `birth`, `is_delete`, `c1`, `c2`, `c3`)

     VALUES (NEW.`id`, NEW.`name`, NEW.`phone`, NEW.`gender`, NEW.`type`, NEW.`birth`, NEW.`is_delete`, NEW.`c1`, NEW.`c2`, NEW.`c3`)

4、开始迁移数据：

   1）估算表的大小，仅仅用于计算进度：EXPLAIN SELECT * FROM `darren`.`t_user` WHERE 1=1；

   2）最小值：SELECT `id` FROM `darren`.`t_user` FORCE INDEX(`PRIMARY`) ORDER BY `id` LIMIT 1;

   3) 根据chunk-size，计算本次chunk最大值及下一个chunk开始值：SELECT `id` FROM `darren`.`t_user` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1'))

                     ORDER BY `id` LIMIT 2, 2；

   4) 插入数据：

         INSERT LOW_PRIORITY IGNORE INTO `darren`.`_t_user_new` (`id`, `name`, `phone`, `gender`, `type`, `birth`, `is_delete`, `c1`, `c2`, `c3`)

         SELECT `id`, `name`, `phone`, `gender`, `type`, `birth`, `is_delete`, `c1`, `c2`, `c3` FROM `darren`.`t_user` FORCE INDEX(`PRIMARY`)

         WHERE ((`id` >= '1')) AND ((`id` <= '3')) LOCK IN SHARE MODE /*pt-online-schema-change 4670 copy nibble*/

   5）开始下一轮迭代......

   6) 何时copy结束？

       根据SELECT `id` FROM `darren`.`t_user` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) ORDER BY `id` LIMIT 2, 2；

       如果获取不到下一个chunk开始值，视为表copy完成，相关源码片段如下：

					sub _get_bounds {

					   my ($self) = @_;

					   if ( $self->{one_nibble} ) {

					      if ( $self->{resume} ) {

					         $self->{no_more_boundaries} = 1;

					      }

					      return;

					   }

					   my $dbh = $self->{Cxn}->dbh();

					   $self->{first_lower} = $dbh->selectrow_arrayref($self->{first_lb_sql});

					   PTDEBUG && _d('First lower boundary:', Dumper($self->{first_lower}));

					   if ( my $nibble = $self->{resume} ) {

					      if (    defined $nibble->{lower_boundary}

					           && defined $nibble->{upper_boundary} ) {

					         my $sth = $dbh->prepare($self->{resume_lb_sql});

					         my @ub  = split ',', $nibble->{upper_boundary};

					         PTDEBUG && _d($sth->{Statement}, 'params:', @ub);

					         $sth->execute(@ub);

					         $self->{next_lower} = $sth->fetchrow_arrayref();

					         $sth->finish();

					      }

					   }

					   else {

					      $self->{next_lower}  = $self->{first_lower};

					   }

					   PTDEBUG && _d('Next lower boundary:', Dumper($self->{next_lower}));

					   if ( !$self->{next_lower} ) {

					      PTDEBUG && _d('At end of table, or no more boundaries to resume');

					      $self->{no_more_boundaries} = 1;

					      $self->{last_upper} = $dbh->selectrow_arrayref($self->{last_ub_sql});

					      PTDEBUG && _d('Last upper boundary:', Dumper($self->{last_upper}));

					   }

					   return;

					}

5、rename表

ANALYZE TABLE `darren`.`_t_user_new`;

RENAME TABLE `darren`.`t_user` TO `darren`.`_t_user_old`, `darren`.`_t_user_new` TO `darren`.`t_user`;

6、清理战场

DROP TABLE IF EXISTS `darren`.`_t_user_old`

DROP TRIGGER IF EXISTS `darren`.`pt_osc_darren_t_user_del`

DROP TRIGGER IF EXISTS `darren`.`pt_osc_darren_t_user_upd`

DROP TRIGGER IF EXISTS `darren`.`pt_osc_darren_t_user_ins`

SHOW TABLES FROM `darren` LIKE '_t_user_new'

【copy rows和触发器实现数据迁移和增量写入原理：】

DDL功能	映射执行的SQL语句
insert触发器	replace into
update触发器	delete ignore + replace into
delete触发器	delete ignore
copy rows	insert ignore into