Percona-Toolkit 之 pt-online-schema-change 总结
pt-online-schema-change - ALTER tables without locking them.
pt-online-schema-change alters a table’s structure without blocking reads or writes. Specify the database and table in the DSN. Do not use this tool before reading its documentation and checking your backups carefully.
pt-online-schema-change是Percona-Toolkit工具集中的一个组件，它可以做到在修改表结构的同时（即进行DDL操作）不阻塞数据库表DML的进行，这样降低了对生产环境数据库的影响。
一、Toolkit工具安装
1.1、参考【pt-ioprofile工具包的使用安装部分】
1.2、查看帮助命令
[root@node1 ~] pt-online-schema-change --help
pt-online-schema-change alters a table’s structure without blocking reads or
writes. Specify the database and table in the DSN. Do not use this tool before
reading its documentation and checking your backups carefully. For more
details, please use the --help option, or try ‘perldoc
/root/percona-toolkit-3.6.0/bin/pt-online-schema-change’ for complete
documentation.
Usage: pt-online-schema-change [OPTIONS] DSN
Options:
省略……
二、实验部分
2.1、测试数据准备
本文基于MySQL官方示例数据库employee：Example Databases进行测试。
登录MySQL数据库：
[root@node1 ~] mysql -uadmin -p123456 -h172.17.0.31 -P3307
– employees：
admin@db 13:11: [employees]> show create table employees\G;
Table | employees
Create Table | CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender enum(‘M’,‘F’) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no),
KEY idx_first_last (first_name,last_name),
KEY idx_birth_hire (birth_date,hire_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.008s
– dept_emp：
admin@db 13:11: [employees]> show create table dept_emp\G;
Table | dept_emp
Create Table | CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no),
KEY dept_no (dept_no),
CONSTRAINT dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINT dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.010s
– departments：
admin@db 13:11: [employees]> show create table departments\G;
Table | departments
Create Table | CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (dept_no),
UNIQUE KEY dept_name (dept_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set
Time: 0.012s
admin@db 13:11: [employees]> select count() from employees;
±---------+
| count() |
±---------+
| 300024 |
±---------+
1 row in set
Time: 0.342s
admin@db 13:11: [employees]> select count() from dept_emp;
±---------+
| count() |
±---------+
| 331603 |
±---------+
1 row in set
Time: 0.306s
admin@db 13:11: [employees]> select count() from departments;
±---------+
| count() |
±---------+
| 9 |
±---------+
1 row in set
Time: 0.050s
2.2、添加字段
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “add comment varchar(50) not null default ‘pt-osc’” --
因为employees表中的emp_no字段被其他表外建关联，以下命令执行时会报如下错误：
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, 1
Child tables:
employees.dept_emp (approx. 331143 rows)
employees.dept_manager (approx. 24 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool’s documentation carefully.
根据报错信息的提示，加入选项–alter-foreign-keys-method重新执行并通过选项–dry-run查看执行过程主要信息：
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “add comment varchar(50) not null default ‘pt-osc’” --alter-foreign-keys-method=auto --charset=utf8 --dry-run
Enter MySQL password:
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not determining the method to update foreign keys because this is a dry run.
Not updating foreign key constraints 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.
2024-07-27T12:26:51 Dropping new table…
2024-07-27T12:26:51 Dropped new table OK.
Dry run complete. employees.employees was not altered.
– 确保信息无误之后可以真正执行变更操作
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “add comment varchar(50) not null default ‘pt-osc’” --alter-foreign-keys-method=auto --charset=utf8 --execute
……省略……
Will automatically choose the method to update foreign keys.
Altering employees.employees…
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:27:40 Creating triggers…
2024-07-27T12:27:40 Created triggers OK.
2024-07-27T12:27:40 Copying approximately 291892 rows…
2024-07-27T12:27:43 Copied rows OK.
2024-07-27T12:27:43 Max rows for the rebuild_constraints method: 219396
Determining the method to update foreign keys…
2024-07-27T12:27:43 employees.dept_emp: too many rows: 331143; must use drop_swap
2024-07-27T12:27:43 Drop-swapping tables…
2024-07-27T12:27:43 Analyzing new table…
2024-07-27T12:27:43 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:27:43 Dropping triggers…
2024-07-27T12:27:43 Dropped triggers OK.
Successfully altered employees.employees.
2.3、修改字段
将表employees的comment字段的字符集修改为utf8mb4：
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “modify column comment varchar(50) character set utf8mb4” --alter-foreign-keys-method=auto --charset=utf8 --execute
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:28:29 Creating triggers…
2024-07-27T12:28:29 Created triggers OK.
2024-07-27T12:28:29 Copying approximately 298845 rows…
2024-07-27T12:28:32 Copied rows OK.
2024-07-27T12:28:32 Max rows for the rebuild_constraints method: 181558
Determining the method to update foreign keys…
2024-07-27T12:28:32 employees.dept_emp: too many rows: 331143; must use drop_swap
2024-07-27T12:28:32 Drop-swapping tables…
2024-07-27T12:28:32 Analyzing new table…
2024-07-27T12:28:32 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:28:32 Dropping triggers…
2024-07-27T12:28:32 Dropped triggers OK.
Successfully altered employees.employees.
2.4、删除字段
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “drop column comment” --alter-foreign-keys-method=auto --charset=utf8 --execute
Will automatically choose the method to update foreign keys.
Altering employees.employees…
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:31:19 Creating triggers…
2024-07-27T12:31:19 Created triggers OK.
2024-07-27T12:31:19 Copying approximately 298949 rows…
2024-07-27T12:31:22 Copied rows OK.
2024-07-27T12:31:22 Max rows for the rebuild_constraints method: 221080
Determining the method to update foreign keys…
2024-07-27T12:31:22 employees.dept_emp: too many rows: 331143; must use drop_swap
2024-07-27T12:31:22 Drop-swapping tables…
2024-07-27T12:31:22 Analyzing new table…
2024-07-27T12:31:22 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:31:22 Dropping triggers…
2024-07-27T12:31:22 Dropped triggers OK.
Successfully altered employees.employees.
2.5、添加索引
为表dept_emp的字段from_date和to_date创建复合索引idx_fr_to_date：
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “add index idx_fr_to_date(from_date,to_date)” --alter-foreign-keys-method=auto --charset=utf8 --execute
Altering employees.dept_emp…
Creating new table…
Created new table employees._dept_emp_new OK.
Altering new table…
Altered employees._dept_emp_new OK.
2024-07-27T12:33:26 Creating triggers…
2024-07-27T12:33:26 Created triggers OK.
2024-07-27T12:33:26 Copying approximately 331143 rows…
2024-07-27T12:33:33 Copied rows OK.
2024-07-27T12:33:33 Analyzing new table…
2024-07-27T12:33:33 Swapping tables…
2024-07-27T12:33:33 Swapped original and new tables OK.
2024-07-27T12:33:33 Dropping old table…
2024-07-27T12:33:33 Dropped old table employees._dept_emp_old OK.
2024-07-27T12:33:33 Dropping triggers…
2024-07-27T12:33:33 Dropped triggers OK.
Successfully altered employees.dept_emp.
show create table dept_emp\G
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE dept_emp (
emp_no int NOT NULL,
dept_no char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no),
KEY dept_no (dept_no),
KEY idx_fr_to_date (from_date,to_date),
CONSTRAINT _dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINT _dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.01 sec)
2.6、删除索引
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “drop index idx_fr_to_date” --alter-foreign-keys-method=auto --charset=utf8 --execute
Altering employees.dept_emp…
Creating new table…
Created new table employees._dept_emp_new OK.
Altering new table…
Altered employees._dept_emp_new OK.
2024-07-27T12:34:23 Creating triggers…
2024-07-27T12:34:24 Created triggers OK.
2024-07-27T12:34:24 Copying approximately 331143 rows…
2024-07-27T12:34:29 Copied rows OK.
2024-07-27T12:34:29 Analyzing new table…
2024-07-27T12:34:29 Swapping tables…
2024-07-27T12:34:29 Swapped original and new tables OK.
2024-07-27T12:34:29 Dropping old table…
2024-07-27T12:34:30 Dropped old table employees._dept_emp_old OK.
2024-07-27T12:34:30 Dropping triggers…
2024-07-27T12:34:30 Dropped triggers OK.
Successfully altered employees.dept_emp.
2.7、修改字段允许NULL
将表dept_emp的字段to_date指定为允许NULL：
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “modify column to_date date null” --alter-foreign-keys-method=auto --charset=utf8 --execute
Altering employees.dept_emp…
Creating new table…
Created new table employees._dept_emp_new OK.
Altering new table…
Altered employees._dept_emp_new OK.
2024-07-27T12:35:43 Creating triggers…
2024-07-27T12:35:43 Created triggers OK.
2024-07-27T12:35:43 Copying approximately 331143 rows…
2024-07-27T12:35:48 Copied rows OK.
2024-07-27T12:35:48 Analyzing new table…
2024-07-27T12:35:48 Swapping tables…
2024-07-27T12:35:49 Swapped original and new tables OK.
2024-07-27T12:35:49 Dropping old table…
2024-07-27T12:35:49 Dropped old table employees._dept_emp_old OK.
2024-07-27T12:35:49 Dropping triggers…
2024-07-27T12:35:49 Dropped triggers OK.
Successfully altered employees.dept_emp.
2.8、修改字段不允许NULL(NOT NULL)
为表employees添加字段ptosc_num并允许NULL，字段类型为int，没有指定默认值。
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “add ptosc_num int null” --alter-foreign-keys-method=auto --charset=utf8 --execute
Altering employees.employees…
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:36:57 Creating triggers…
2024-07-27T12:36:57 Created triggers OK.
2024-07-27T12:36:57 Copying approximately 299645 rows…
2024-07-27T12:37:00 Copied rows OK.
2024-07-27T12:37:00 Max rows for the rebuild_constraints method: 224854
Determining the method to update foreign keys…
2024-07-27T12:37:00 employees.dept_emp: too many rows: 331320; must use drop_swap
2024-07-27T12:37:00 Drop-swapping tables…
2024-07-27T12:37:00 Analyzing new table…
2024-07-27T12:37:00 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:37:00 Dropping triggers…
2024-07-27T12:37:00 Dropped triggers OK.
Successfully altered employees.employees.
2.8、修改字段ptosc_num为不允许NULL(NOT NULL)
需要通过指定选项–null-to-not-null，否则会报错。
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “modify column ptosc_num int not null” --alter-foreign-keys-method=auto --null-to-not-null --charset=utf8 --execute
Altering employees.employees…
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:38:45 Creating triggers…
2024-07-27T12:38:45 Created triggers OK.
2024-07-27T12:38:45 Copying approximately 299561 rows…
2024-07-27T12:38:48 Copied rows OK.
2024-07-27T12:38:48 Max rows for the rebuild_constraints method: 208642
Determining the method to update foreign keys…
2024-07-27T12:38:48 employees.dept_emp: too many rows: 331320; must use drop_swap
2024-07-27T12:38:48 Drop-swapping tables…
2024-07-27T12:38:48 Analyzing new table…
2024-07-27T12:38:49 Dropped and swapped tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:38:49 Dropping triggers…
2024-07-27T12:38:49 Dropped triggers OK.
Successfully altered employees.employees.
– 因为字段ptosc_num没有指定默认值，字段类型为int，所以默认值为0
admin@db 12:34: [employees]> select * from employees limit 5;
±-------±-----------±-----------±----------±-------±-----------±----------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | ptosc_num |
±-------±-----------±-----------±----------±-------±-----------±----------+
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 0 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 0 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 0 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 0 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 0 |
±-------±-----------±-----------±----------±-------±-----------±----------+
5 rows in set (0.01 sec)
2.9、删除外键
需要为外键指定名称为_forigen_key，因为在创建新表时候默认为新表上的外键创建这样的名称，如果没这样指定则无法删除。
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “drop foreign key _dept_emp_ibfk_1” --alter-foreign-keys-method=auto --charset=utf8 --execute
Creating new table…
Created new table employees._dept_emp_new OK.
Altering new table…
Altered employees._dept_emp_new OK.
2024-07-27T12:42:36 Creating triggers…
2024-07-27T12:42:36 Created triggers OK.
2024-07-27T12:42:36 Copying approximately 331320 rows…
2024-07-27T12:42:41 Copied rows OK.
2024-07-27T12:42:41 Analyzing new table…
2024-07-27T12:42:41 Swapping tables…
2024-07-27T12:42:41 Swapped original and new tables OK.
2024-07-27T12:42:41 Dropping old table…
2024-07-27T12:42:41 Dropped old table employees._dept_emp_old OK.
2024-07-27T12:42:41 Dropping triggers…
2024-07-27T12:42:41 Dropped triggers OK.
Successfully altered employees.dept_emp.
admin@db 12:41: [employees]> show create table dept_emp\G
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE dept_emp (
emp_no int NOT NULL,
dept_no char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL,
PRIMARY KEY (emp_no,dept_no),
KEY dept_no (dept_no),
CONSTRAINT dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
CONSTRAINT dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
admin@db 12:41: [employees]> show create table dept_emp\G
*************************** 1. row ***************************
Table: dept_emp
Create Table: CREATE TABLE dept_emp (
emp_no int NOT NULL,
dept_no char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL,
PRIMARY KEY (emp_no,dept_no),
KEY dept_no (dept_no),
CONSTRAINT _dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
2.10、重建表
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees --user=admin --ask-pass --alter “engine=InnoDB” --alter-foreign-keys-method=auto --charset=utf8 --execute
Altering employees.employees…
Creating new table…
Created new table employees._employees_new OK.
Altering new table…
Altered employees._employees_new OK.
2024-07-27T12:44:43 Creating triggers…
2024-07-27T12:44:43 Created triggers OK.
2024-07-27T12:44:43 Copying approximately 299487 rows…
2024-07-27T12:44:46 Copied rows OK.
2024-07-27T12:44:46 Max rows for the rebuild_constraints method: 213462
Determining the method to update foreign keys…
2024-07-27T12:44:46 employees.dept_manager: 24 rows; can use rebuild_constraints
2024-07-27T12:44:46 Rebuilding foreign key constraints…
2024-07-27T12:44:46 Rebuilt foreign key constraints OK.
2024-07-27T12:44:46 Analyzing new table…
2024-07-27T12:44:46 Swapping tables…
2024-07-27T12:44:46 Swapped original and new tables OK.
2024-07-27T12:44:46 Dropping old table…
2024-07-27T12:44:46 Dropped old table employees._employees_old OK.
2024-07-27T12:44:46 Dropping triggers…
2024-07-27T12:44:46 Dropped triggers OK.
Successfully altered employees.employees.
2.11、变更后保留旧表
如果是涉及外键关联的父表进行变更，则建议选项–alter-foreign-keys-method=rebuild_constraints，这样在子表中会重命名外键约束名，如果选项–alter-foreign-keys-method有可能取值drop_swap时，则会强制使用选项–no-swap-tables和–no-drop-old-table，其中–no-swap-tables并不会有旧表的产生，就不存在保留之说了。
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “add comment varchar(50) not null default ‘pt-osc’” --no-drop-old-table --charset=utf8 --execute
Altering employees.dept_emp…
Creating new table…
Created new table employees._dept_emp_new OK.
Altering new table…
Altered employees._dept_emp_new OK.
2024-07-27T12:51:56 Creating triggers…
2024-07-27T12:51:56 Created triggers OK.
2024-07-27T12:51:56 Copying approximately 331320 rows…
2024-07-27T12:52:01 Copied rows OK.
2024-07-27T12:52:01 Analyzing new table…
2024-07-27T12:52:01 Swapping tables…
2024-07-27T12:52:01 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2024-07-27T12:52:01 Dropping triggers…
2024-07-27T12:52:01 Dropped triggers OK.
Successfully altered employees.dept_emp.
admin@db 12:53: [employees]> show tables;
±---------------------+
| Tables_in_employees |
±---------------------+
| _dept_emp_old |
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| employees_ptsync |
| salaries |
| titles |
±---------------------+
10 rows in set (0.00 sec)
以上语句执行完成后会在数据库中生成名为_dept_emp_old的表，即变更之前的旧表。
2.12、变更后保留新表
顾名思义，就是先做一次完整的表变更操作，但是不进行旧表与新表的交换，也不删除变更之后的新表，通过指定选项–no-drop-new-table和–no-swap-tables实现，可以通过选项–new-table-name指定新表名，当选项–alter-foreign-keys-method=drop_swap时，–no-drop-new-table不生效，与保留旧表的情形一致。
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=dept_emp --user=admin --ask-pass --alter “add comment varchar(50) not null default ‘pt-osc’” --no-drop-new-table --no-swap-tables --new-table-name=‘dept_emp_bak’ --charset=utf8 --execute
以上语句执行完成后会在数据库中生成名为dept_emp_bak的表，即变更之后的新表，但对旧表不会做任何修改。
2.13、使用选项–no-check-unique-key-change添加主键操作
这里测试基于employees表创建employees_ptosc表：
admin@db 13:11: [employees]> create table employees_ptosc as select * from employees;
Query OK, 300024 rows affected
Time: 2.010s
admin@db 13:11: [employees]> show create table employees_ptosc;
±----------------±-------------------------------------+
| Table | Create Table |
±----------------±-------------------------------------+
| employees_ptosc | CREATE TABLE employees_ptosc ( |
| | emp_no int(11) NOT NULL, |
| | birth_date date NOT NULL, |
| | first_name varchar(14) NOT NULL, |
| | last_name varchar(16) NOT NULL, |
| | gender enum(‘M’,‘F’) NOT NULL, |
| | hire_date date NOT NULL |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±----------------±-------------------------------------+
1 row in set
Time: 0.022s
对employees_ptosc表添加主键：
– 如果employees_ptosc表没有任何索引和约束会报如下信息，工具执行失败
Cannot chunk the original table employees.employees_ptosc: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5882.
– 先为employees_ptosc表创建基于first_name的索引idx_first_name，再次执行添加主键
admin@db 13:11: [employees]> create index idx_first_name on employees_ptosc(first_name);
Query OK, 0 rows affected
Time: 1.175s
– 如果没有加选项–no-check-unique-key-change会报如下信息
……省略……
Altering employees.employees_ptosc…
employees.employees_ptosc was not altered.
You are trying to add an unique key. This can result in data loss if the data is not unique.
Please read the documentation for the --check-unique-key-change parameter.
You can check if the column(s) contain duplicate content by running this/these query/queries:
SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
‘Yes, the desired unique index currently contains only unique values’,
‘No, the desired unique index contains duplicated values. There will be data loss’
) AS IsThereUniqueness FROM employees.employees_ptosc;
Keep in mind that these queries could take a long time and consume a lot of resources
大致意思就是工具无法确定需要创建主键基于的字段值是否唯一，一旦有重复值出现，在数据拷贝的时候容易出现数据丢失，并给出了检查的语句。
admin@db 13:11: [employees]> SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
‘Yes, the desired unique index currently contains only unique values’,
‘No, the desired unique index contains duplicated values. There will be data loss’
) AS IsThereUniqueness FROM employees.employees_ptosc;
±--------------------------------------------------------------------+
| IsThereUniqueness |
±--------------------------------------------------------------------+
| Yes, the desired unique index currently contains only unique values |
±--------------------------------------------------------------------+
1 row in set
Time: 0.274s
使用选项–no-check-unique-key-change再次执行添加主键操作：
pt-online-schema-change h=172.17.0.31,P=3307,D=employees,t=employees_ptosc --user=admin --ask-pass --alter “add primary key(emp_no)” --charset=utf8 --no-check-unique-key-change --charset=utf8 --execute
Altering employees.employees_ptosc…
Creating new table…
Created new table employees._employees_ptosc_new OK.
Altering new table…
Altered employees._employees_ptosc_new OK.
2024-07-27T13:13:00 Creating triggers…
2024-07-27T13:13:00 Created triggers OK.
2024-07-27T13:13:00 Copying approximately 299292 rows…
2024-07-27T13:13:05 Copied rows OK.
2024-07-27T13:13:05 Analyzing new table…
2024-07-27T13:13:06 Swapping tables…
2024-07-27T13:13:06 Swapped original and new tables OK.
2024-07-27T13:13:06 Dropping old table…
2024-07-27T13:13:06 Dropped old table employees._employees_ptosc_old OK.
2024-07-27T13:13:06 Dropping triggers…
2024-07-27T13:13:06 Dropped triggers OK.
Successfully altered employees.employees_ptosc.
三、总结
pt-online-schema-change工具对于任意的DDL语句都是通过创新表拷贝数据来进行，期间都支持DML，而Online DDL根据DDL类型的来区分是否需要对表进行COPY TABLE操作，有点类似于工具的创建临时表进行变更，而不需要COPY TABLE操作的DDL语句在执行期间支持DML。
关于pt-online-schema-change更多的说明可以参考官方文档：https://www.percona.com/downloads/percona-toolkit/LATEST/
