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

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

大家好,又见面了,我是全栈君。

对于大表的 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










本文转自 lirulei90 51CTO博客,原文链接:http://blog.51cto.com/lee90/1974950,如需转载请自行联系原作者
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/107867.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • 面向对象复习笔记(一)

    面向对象复习笔记(一)

    2021年10月25日
  • SPI接口总结「建议收藏」

    SPI接口总结「建议收藏」一、SPI协议【SerialPeripheralInterface】    串行外围设备接口,是一种高速全双工的通信总线。在ADC/LCD等与MCU间通信。1、SPI信号线    SPI包含4条总线,SPI总线包含4条总线,分别为SS、SCK、MOSI、MISO。(1)SS(SlaveSelect):片选信号线,当有多个SPI设备与MCU相连时,每个设备的这…

  • java编程app_如何用java开发app

    java编程app_如何用java开发app前言本人是底层211本科,刚毕业,⽆科研经历,但是有些项⽬经历。在国内监控行业某头部企业做过一段时间的实习。想着投下字节,可以积累⼀下⾯试经验和为金九银十面招做准备。投了简历之后,过了一段时间,HR就打电话跟我约时间,说明一下,我投的是北京office。以下就是一个面试的全部过程,分享出来给感兴趣的朋友们一看。阿里的人才画像其实最近两年自己一直在做面试官,也面试过很多优秀的人,心里大概有一个标准,知道什么样的人才是我们想要的人。但是这个标准我一直都没有仔细的去思考过,刚好最近有时间

  • ConcurrentHashMap

    ConcurrentHashMap

  • 台式计算机和便携式计算机是替代品吗,iPad能否替代笔记本电脑?这结果我一万个不服!…「建议收藏」

    近年来,随着平板电脑的不断发展,有一个长期存在的问题,平板(文中主要指iPad)是否可以替代传统笔记本电脑?平板电脑和笔记本电脑如今可以做很多事情,最好的平板电脑甚至比一些笔记本电脑功能更强大,但它是一个平板电脑真的是传统的便携式计算机合适的替代品吗?我们看一下这两种平台的设备都可以提供什么,以及哪种平台最适合您的需求!笔记本电脑与平板电脑:便携性!首先,是便携性问题。虽然两种类型的设备都可以随身…

  • owasp靶机安装_靶机是什么

    owasp靶机安装_靶机是什么owasp靶机应该是每个渗透入门乃至一些高手的一个试炼地之一,以下是我在靶机上搭建虚拟的方法。1.虚拟机安装在这里我用的是VMware14,靶机自然也是VM的。软件安装基本也是没有什么可讲的,安

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号