1 row in set
Time: 0.012s
mysql root@localhost:employees> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set
Time: 0.342s
mysql root@localhost:employees> select count(*) from dept_emp;
+----------+
| count(*) |
+----------+
| 331603 |
+----------+
1 row in set
Time: 0.306s
mysql root@localhost:employees> select count(*) from departments;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set
Time: 0.050s
# pt-online-schema-change h=192.168.58.3,P=3306,D=employees,t=employees --user=admin --ask-pass --alter "add comment varchar(50) not null default 'pt-osc'" --charset=utf8
因为employees表中的emp_no字段被其他表外建关联,以下命令执行时会报如下错误:
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=192.168.58.3,P=3306,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:
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)
Will automatically choose the method to update foreign keys.
Starting a dry run. `employees`.`employees` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
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 swapping tables because this is a dry run.
Not updating foreign key constraints 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.
2019-03-25T13:30:05 Dropping new table...
2019-03-25T13:30:05 Dropped new table OK.
Dry run complete. `employees`.`employees` was not altered.
-- 确保信息无误之后可以真正执行变更操作
# pt-online-schema-change h=192.168.58.3,P=3306,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.
2019-03-25T13:35:25 Creating triggers...
2019-03-25T13:35:25 Created triggers OK.
2019-03-25T13:35:25 Copying approximately 299512 rows...
2019-03-25T13:35:31 Copied rows OK.
2019-03-25T13:35:31 Max rows for the rebuild_constraints method: 99266
Determining the method to update foreign keys...
2019-03-25T13:35:31 `employees`.`dept_emp`: too many rows: 331143; must use drop_swap
2019-03-25T13:35:31 Drop-swapping tables...
2019-03-25T13:35:31 Analyzing new table...
2019-03-25T13:35:31 Dropped and swapped tables OK.
Not dropp