保留旧表
如果是涉及外键关联的父表进行变更,则建议选项--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=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-old-table --charset=utf8 --execute
以上语句执行完成后会在数据库中生成名为_dept_emp_old
的表,即变更之前的旧表。
顾名思义,就是先做一次完整的表变更操作,但是不进行旧表与新表的交换,也不删除变更之后的新表,通过指定选项--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=192.168.58.3,P=3306,D=employees,t=dept_emp --user=admin --ask-pass --alter "add comment varchar(50) notnull default 'pt-osc'" --no-drop-new-table --no-swap-tables --new-table-name='dept_emp_bak' --charset=utf8 --execute
以上语句执行完成后会在数据库中生成名为dept_emp_bak
的表,即变更之后的新表,但对旧表不会做任何修改。
如果是InnoDB表没有主键,真的不敢想像啊,但还是要进行测式下。这里测试基于employees表创建employees_ptosc表:
mysql root@localhost:employees> create table employees_ptosc as select * from employees;
Query OK, 300024 rows affected
Time: 2.010s
mysql root@localhost: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,再次执行添加主键
mysql root@localhost: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
大致意思就是工具无法确定需要创建主键基于的字段值是否唯一,一旦有重复值出现,在数据拷贝的时候容易出现数据丢失,并给出了检查的语句。
mysql root@localhost:employees> SELECT IF(COUNT(DISTINCT emp_no) = COUNT(*),
'Yes, the desired unique index currently contains only unique values',
'No, the desired unique in