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 |