• pt-osc原理


    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
  • 相关阅读:
    【Android
    梦想责任与团队
    在MySQL字段中使用逗号分隔符
    session_write_close() 用法
    课程-问题分析与解决
    团队管理:新业务团队如何结合绩效来度量开发目标
    Linux sort 排序 去重 统计
    nginx-404与fastcgi_intercept_errors指令
    nginx fastcgi_buffers to an upstream response is buffered to a temporary file
    10年软件开发中获得的最宝贵的经验!非常值得你一读
  • 原文地址:https://www.cnblogs.com/mysql-dba/p/9901632.html
Copyright © 2020-2023  润新知