Percona Toolkit使用之pt-online-schema-change

越狐若

2023-12-01

pt-online-schema-change的功能是不加锁的情况下更改表。

用法如下：

pt-online-schema-change [OPTIONS] DSN

pt-online-schema-change在不堵塞读写的情况下修改表结构。在DSN（MySQL连接访问）里指定库和表。在没有认真阅读文档和检查你的备份前，不要使用该工具喲！

①给`sakila`.`actor`库表加一列：

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor

②修改`sakila`.`actor`库表为InnoDB存储引擎，因为该表已经是一张InnoDB表，所以实际上是以一个不阻塞的方式执行“ OPTIMIZE TABLE ”：

pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

pt-online-schema-change模拟MySQL内部修改表的机制，但是它作用于你想要修改的表的一张拷贝。这意味着原始表不会被加锁，客户端可以继续读取和修改表里面的数据。

pt-online-schema-change通过创建一张将要修改的表的空表拷贝，按照需要修改拷贝表，然后从原始表里面复制记录到新表的方式运行。当数据拷贝完成后，它移走原始表然后以新表替换它。默认情况下，它会删除原始表。

数据复制进程是以小数据块进行的，该数据块的大小是可变的，用以在指定大小的时间内执行（参考--chunk-time选项）。该进程和例如pt-table-checksum的其他工具如何运行非常相似。在复制的过程中，原始表里对数据的任何改动都会映射到新表里，因为该工具会在原始表上创建触发器来更新新表里对应的记录。触发器的使用意味着如果原始表上已经定义了触发器，该工具将无法工作。

拷贝数据到新表完成后，该工具进行一个原子性“ RENAME TABLE ”操作来同时重命名原始表和新表。操作完成后，工具会删除原始表。

外键使该工具的运行复杂化并且引入额外的风险。如果外键关联到该表，原子性重命名原始表和新表的技术将无法工作。模式修改完成后工具必须更新外键去关联新表。该工具支持两种方式来实现，参考--alter-foreign-keys-method选项。

外键也会带来一些副作用。最终的表将和原始表具有相同的外键和索引（除非在你的ALTER语句中指定了不同的），但是这些对象的名称可能会被轻微地改动，以避免在MySQL和InnoDB中的对象名称冲突。

安全起见，除非指定--execute选项，该工具不会改动表，该选项默认不启用。该工具支持各种其他措施来防止额外负载或者其他问题，包括自动检测replicas、连接和进行以下安全检查：

大多数情况下该工具拒绝操作，除非表里面有主键或者唯一索引存在。参考--alter选项。
如果检测到复制筛选存在，该工具拒绝操作。参考--[no]check-replication-filters选项。
如果发现复制中有replicas存在延迟，该工具将中断数据拷贝操作。参考--max-lag选项。
如果检测到服务器上负载过重，该工具会中断或者终止其操作。参考--max-load和--critical-load选项。
该工具会设置innodb_lock_wait_timeout=1（MySQL5.5及以后）、lock_wait_timeout=60，以使它更有可能成为锁连接的牺牲者而不太可能破坏其他事务。这些值可以通过指定--set-vars选项修改。
如果外键约束引用表，该工具拒绝改动该表，除非你指定--alter-foreign-keys-method选项。
该工具不能改动“ Percona XtraDB Cluster ”节点上的MyISAM表。

该工具会打印其活动信息到STDOUT以便你可以观察它在做些啥子。在数据拷贝阶段，它打印--progress报告到STDERR。通过指定--print选项获取更多信息。如果指定了--statistics选项，结尾会打印各种内部事件计数的报告，例如：

# Event  Count
# ====== =====
# INSERT     1

以下为个人本地环境的测试数据。

测试库表数据定义。

mysql> SHOW CREATE TABLE `player`.`player`;
+--------+-----------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                                                                                                
                                                                                                                                                                                                  |+--------+-----------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| player | CREATE TABLE `player` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(10) unsigned NOT NULL COMMENT 'player number',
  `name` varchar(15) NOT NULL COMMENT 'player name',
  `role` varchar(15) NOT NULL COMMENT 'player role',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)

mysql> SELECT * FROM `player`.`player`;
+----+--------+-------+-------------+
| id | number | name  | role        |
+----+--------+-------+-------------+
|  1 |      1 | hachi | GK          |
|  2 |      2 | ni    | left back   |
|  3 |      3 | san   | center back |
|  4 |      4 | yon   | center back |
|  5 |      5 | go    | right back  |
+----+--------+-------+-------------+
5 rows in set (0.00 sec)

干执行（--dry-run打印工具执行过程但不会操作具体表）添加字段。

root@ubuntu:~# pt-online-schema-change -h192.168.112.129 -P3306 -uroot -p123456 --alter "ADD COLUMN remark1 VARCHAR(15) NULL DEFAULT NULL" D=player,t=player --dry-run
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, 1
Starting a dry run.  `player`.`player` will not be altered.  Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table player._player_new OK.
Altering new table...
Altered `player`.`_player_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2018-04-12T21:28:13 Dropping new table...
2018-04-12T21:28:13 Dropped new table OK.
Dry run complete.  `player`.`player` was not altered.

执行（--execute）添加字段。

root@ubuntu:~# pt-online-schema-change -h192.168.112.129 -P3306 -uroot -p123456 --alter "ADD COLUMN remark1 VARCHAR(15) NULL DEFAULT NULL" D=player,t=player --execute
No slaves found.  See --recursion-method if host ubuntu has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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, 1
Altering `player`.`player`...
Creating new table...
Created new table player._player_new OK.
Altering new table...
Altered `player`.`_player_new` OK.
2018-04-12T21:29:16 Creating triggers...
2018-04-12T21:29:16 Created triggers OK.
2018-04-12T21:29:16 Copying approximately 5 rows...
2018-04-12T21:29:16 Copied rows OK.
2018-04-12T21:29:16 Analyzing new table...
2018-04-12T21:29:16 Swapping tables...
2018-04-12T21:29:16 Swapped original and new tables OK.
2018-04-12T21:29:16 Dropping old table...
2018-04-12T21:29:16 Dropped old table `player`.`_player_old` OK.
2018-04-12T21:29:16 Dropping triggers...
2018-04-12T21:29:16 Dropped triggers OK.
Successfully altered `player`.`player`.

“修改”存储引擎（这里相当于“ OPTIMIZE TABLE ”）。

root@ubuntu:~# pt-online-schema-change -h192.168.112.129 -P3306 -uroot -p123456 --alter "ENGINE=InnoDB" D=player,t=player --execute
No slaves found.  See --recursion-method if host ubuntu has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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, 1
Altering `player`.`player`...
Creating new table...
Created new table player._player_new OK.
Altering new table...
Altered `player`.`_player_new` OK.
2018-04-12T21:31:45 Creating triggers...
2018-04-12T21:31:45 Created triggers OK.
2018-04-12T21:31:45 Copying approximately 5 rows...
2018-04-12T21:31:45 Copied rows OK.
2018-04-12T21:31:45 Analyzing new table...
2018-04-12T21:31:45 Swapping tables...
2018-04-12T21:31:45 Swapped original and new tables OK.
2018-04-12T21:31:45 Dropping old table...
2018-04-12T21:31:45 Dropped old table `player`.`_player_old` OK.
2018-04-12T21:31:45 Dropping triggers...
2018-04-12T21:31:45 Dropped triggers OK.
Successfully altered `player`.`player`.

吧啦吧啦多打印一些输出。

root@ubuntu:~# pt-online-schema-change -h192.168.112.129 -P3306 -uroot -p123456 --alter "ADD COLUMN remark0 VARCHAR(15) NULL DEFAULT NULL" D=player,t=player --progress=iterations,1 --print --statistics --execute
No slaves found.  See --recursion-method if host ubuntu has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
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, 1
Altering `player`.`player`...
Creating new table...
CREATE TABLE `player`.`_player_new` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `number` int(10) unsigned NOT NULL COMMENT 'player number',
  `name` varchar(15) NOT NULL COMMENT 'player name',
  `role` varchar(15) NOT NULL COMMENT 'player role',
  `remark1` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
Created new table player._player_new OK.
Altering new table...
ALTER TABLE `player`.`_player_new` ADD COLUMN remark0 VARCHAR(15) NULL DEFAULT NULL
Altered `player`.`_player_new` OK.
2018-04-12T21:40:39 Creating triggers...
2018-04-12T21:40:39 Created triggers OK.
2018-04-12T21:40:39 Copying approximately 5 rows...
INSERT LOW_PRIORITY IGNORE INTO `player`.`_player_new` (`id`, `number`, `name`, `role`, `remark1`) SELECT `id`, `number`, `name`, `role
`, `remark1` FROM `player`.`player` LOCK IN SHARE MODE /*pt-online-schema-change 5635 copy table*/2018-04-12T21:40:39 Copied rows OK.
2018-04-12T21:40:39 Analyzing new table...
2018-04-12T21:40:39 Swapping tables...
RENAME TABLE `player`.`player` TO `player`.`_player_old`, `player`.`_player_new` TO `player`.`player`
2018-04-12T21:40:39 Swapped original and new tables OK.
2018-04-12T21:40:39 Dropping old table...
DROP TABLE IF EXISTS `player`.`_player_old`
2018-04-12T21:40:39 Dropped old table `player`.`_player_old` OK.
2018-04-12T21:40:39 Dropping triggers...
DROP TRIGGER IF EXISTS `player`.`pt_osc_player_player_del`
DROP TRIGGER IF EXISTS `player`.`pt_osc_player_player_upd`
DROP TRIGGER IF EXISTS `player`.`pt_osc_player_player_ins`
2018-04-12T21:40:39 Dropped triggers OK.
# Event  Count
# ====== =====
# INSERT     1
Successfully altered `player`.`player`.

参考：

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

Percona Toolkit使用之pt-online-schema-change

相关阅读

相关文章

相关问答

相关文档