[聚合文章] pt-online-schema-change大表结构修改利器

  1. 首页
  2. 聚合文章
  3. 文章详情
MySQL 2018-01-07 31 阅读
原文链接: https://www.jianshu.com/p/1273568e9593

简介：

pt-online-schema-change是Percona-toolkit一员，通过改进原生ddl的方式，达到不锁表在线修改表结构。

pt-osc工作过程

  1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)
  2. 在新表执行alter table 语句（速度应该很快）
  3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作
  4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表
  5. Rename 原表到old表中，在把临时表Rename为原表
  6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理
  7. 默认最后将旧原表删除

使用之前，请参考安装Percona-toolkit教程参考：

Percona Toolkit初识

下面是pt-online-scheme-change示例：

在test库新建表pt:

CREATE TABLE `pt` ( `id` int(11) NOT NULL DEFAULT '0', `disktype` enum('sas','shannon','memdisk') NOT NULL, `blocksize` enum('4K','64K','256K','1M') NOT NULL, `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL, `thread` enum('1','2','4','8','16','32') NOT NULL, `bandwidth` float(10,2) unsigned NOT NULL, `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00', PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

我们来上面字段resptime改为responsetime

#自己修改ip和账号和密码------------------------------------------------------------~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter  #执行整个流程输出------------------------------------------------------------No slaves found.  See --recursion-method if host localhost.localdomain has slaves.Not checking slave lag because no slaves were found and --check-slave-lag was not specified.******************************************************************* Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER together with SSL_ca_file|SSL_ca_path for verification. If you really don't want to verify the certificate and keep the connection open to Man-In-The-Middle attacks please set SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.*******************************************************************  at /usr/bin/pt-online-schema-change line 6948.******************************************************************* Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER together with SSL_ca_file|SSL_ca_path for verification. If you really don't want to verify the certificate and keep the connection open to Man-In-The-Middle attacks please set SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.*******************************************************************  at /usr/bin/pt-online-schema-change line 6948.# A software update is available:Operation, tries, wait:  analyze_table, 10, 1  copy_rows, 10, 0.25  create_triggers, 10, 1  drop_triggers, 10, 1  swap_tables, 10, 1  update_foreign_keys, 10, 1Altering `test`.`pt`...Renaming columns:  resptime to responsetimeCreating new table...CREATE TABLE `test`.`_pt_new` (  `id` int(11) NOT NULL DEFAULT '0',  `disktype` enum('sas','shannon','memdisk') NOT NULL,  `blocksize` enum('4K','64K','256K','1M') NOT NULL,  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,  `thread` enum('1','2','4','8','16','32') NOT NULL,  `bandwidth` float(10,2) unsigned NOT NULL,  `resptime` float(10,2) unsigned NOT NULL DEFAULT '0.00',  PRIMARY KEY (`id`),  UNIQUE KEY `id_UNIQUE` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8Created new table test._pt_new OK.Altering new table...ALTER TABLE `test`.`_pt_new` CHANGE resptime responsetime float(10,2) unsigned not NULL default '0'Altered `test`.`_pt_new` OK.2018-01-07T16:36:29 Creating triggers...2018-01-07T16:36:29 Created triggers OK.2018-01-07T16:36:29 Copying approximately 1 rows...INSERT LOW_PRIORITY IGNORE INTO `test`.`_pt_new` (`id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `responsetime`) SELECT `id`, `disktype`, `blocksize`, `testmode`, `thread`, `bandwidth`, `resptime` FROM `test`.`pt` LOCK IN SHARE MODE /*pt-online-schema-change 7706 copy table*/2018-01-07T16:36:29 Copied rows OK.2018-01-07T16:36:29 Analyzing new table...2018-01-07T16:36:29 Swapping tables...RENAME TABLE `test`.`pt` TO `test`.`_pt_old`, `test`.`_pt_new` TO `test`.`pt`2018-01-07T16:36:29 Swapped original and new tables OK.2018-01-07T16:36:29 Dropping old table...DROP TABLE IF EXISTS `test`.`_pt_old`2018-01-07T16:36:29 Dropped old table `test`.`_pt_old` OK.2018-01-07T16:36:29 Dropping triggers...DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_del`DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_upd`DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt_ins`2018-01-07T16:36:29 Dropped triggers OK.# Event  Count# ====== =====# INSERT     1Successfully altered `test`.`pt`.

成功看到：

mysql> show create table pt;| pt    | CREATE TABLE `pt` (  `id` int(11) NOT NULL DEFAULT '0',  `disktype` enum('sas','shannon','memdisk') NOT NULL,  `blocksize` enum('4K','64K','256K','1M') NOT NULL,  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,  `thread` enum('1','2','4','8','16','32') NOT NULL,  `bandwidth` float(10,2) unsigned NOT NULL,  `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',#改变了  PRIMARY KEY (`id`),  UNIQUE KEY `id_UNIQUE` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

添加一个字段p1

------------------------------------------------------------~ # pt-online-schema-change --host=127.0.0.1 --user=root --password=123456 --alter "add p1 float(10,2) not NULL default '0'" D=test,t='pt' --execute --print --statistics --no-check-alter

成功看到：

----------------------------------------------------------+| pt    | CREATE TABLE `pt` (  `id` int(11) NOT NULL DEFAULT '0',  `disktype` enum('sas','shannon','memdisk') NOT NULL,  `blocksize` enum('4K','64K','256K','1M') NOT NULL,  `testmode` enum('seqwr','seqrewr','seqrd','rndrd','rndwr','rndrw') NOT NULL,  `thread` enum('1','2','4','8','16','32') NOT NULL,  `bandwidth` float(10,2) unsigned NOT NULL,  `responsetime` float(10,2) unsigned NOT NULL DEFAULT '0.00',  `p1` float(10,2) NOT NULL DEFAULT '0.00',  #新增一列  PRIMARY KEY (`id`),  UNIQUE KEY `id_UNIQUE` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

注：本文内容来自互联网，旨在为开发者提供分享、交流的平台。如有涉及文章版权等事宜，请你联系站长进行处理。