Percona Toolkit · 在线修改表结构工具 pt-online-schema-change

发表于 | 更新于 | 分类于 | 阅读次数：

功能

支持不锁表在线修改表结构

原理

以 test.employees 为例：

  • 创建新表，test.employees_new 表结构与 test.employees 一致
  • test.employees_new 执行 SQL
  • test.employees 添加触发器 (DELETE/UPDATE/INSERT)
  • 批量从 test.employees 拷贝数据到 test.employees_new，拷贝过程中通过触发器自动更新
  • 修改表名，test.employees 修改为 test.employees_old，test.employees_new 修改为 test.employees
  • 删除 test.employees_old 和触发器

使用

参数说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--host=xxx 
--user=xxx 
--password=xxx     # 连接实例信息
--user=            # 连接mysql的用户名
--password=        # 连接mysql的密码
--host=            # 连接mysql的地址
P=3306             # 连接mysql的端口号
D=                 # 连接mysql的库名
t=                 # 连接mysql的表名
--alter            # 修改表结构的语句
--dry-run          # 创建和修改新表
--print            # 打印信息
--execute          # 执行修改表结构 ，--dry-run与--execute必须指定一个，二者相互排斥
--charset=utf8     # 使用utf8编码，避免中文乱码
--no-version-check # 不检查版本，在阿里云服务器中一般加入此参数，否则会报错

添加索引

1
$ pt-online-schema-change --user=root --password='xx' --socket=/data/mysql-tdds/mysql.sock --alter "ADD INDEX idx_address(address)" D=test,t=employees --print --execute

删除索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
$ pt-online-schema-change --user=root --password='xx' --socket=/data/mysql-tdds/mysql.sock --alter "DROP INDEX idx_address" D=test,t=employees --print --execute

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 `test`.`employees`...
Creating new table...
CREATE TABLE `test`.`_employees_new` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL DEFAULT '',
  `address` varchar(50) DEFAULT NULL,
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_address` (`address`)
) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
Created new table test._employees_new OK.
Altering new table...
ALTER TABLE `test`.`_employees_new` DROP INDEX idx_address
Altered `test`.`_employees_new` OK.
2019-10-22T15:59:32 Creating triggers...
2019-10-22T15:59:32 Created triggers OK.
2019-10-22T15:59:32 Copying approximately 5 rows...
INSERT LOW_PRIORITY IGNORE INTO `test`.`_employees_new` (`id`, `name`, `address`, `age`, `gmt_create`, `gmt_modified`) SELECT `id`, `name`, `address`, `age`, `gmt_create`, `gmt_modified` FROM `test`.`employees` LOCK IN SHARE MODE /*pt-online-schema-change 112205 copy table*/
2019-10-22T15:59:32 Copied rows OK.
2019-10-22T15:59:32 Analyzing new table...
2019-10-22T15:59:32 Swapping tables...
RENAME TABLE `test`.`employees` TO `test`.`_employees_old`, `test`.`_employees_new` TO `test`.`employees`
2019-10-22T15:59:32 Swapped original and new tables OK.
2019-10-22T15:59:32 Dropping old table...
DROP TABLE IF EXISTS `test`.`_employees_old`
2019-10-22T15:59:32 Dropped old table `test`.`_employees_old` OK.
2019-10-22T15:59:32 Dropping triggers...
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_del`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_upd`
DROP TRIGGER IF EXISTS `test`.`pt_osc_test_employees_ins`
2019-10-22T15:59:32 Dropped triggers OK.
Successfully altered `test`.`employees`.