MySQL Online DDL 工具之pt-online-schema-change

MySQL DDL:
DDL是一个令所有MySQL dDBA 诟病的一个功能，因为在MySQL中在对表进行dDDL时，会锁表，当表比较小比如小于1W行时，对前端影响较小，当时遇到千万级别的表，就会影响前端应用对表的写操作！InnoDB引擎是通过以下步骤来进行DDL的：
1、按照原始表（original_table）的表结构和DDL语句，新建一个不可见的临时表（tmp_table）
2、在原表上加write lock，阻塞所有更新操作（insert、delete、update等）
3、执行insert into tmp_table select * from original_table
4、rename original_table和tmp_table，最后drop original_table
5、释放 write lock。
可以看见在InnoDB执行DDL的时候，原表是只能读不能写的。为此 perconal 推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。工作原理：
如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。
1 创建一个和你要执行 alter 操作的表一样的空表结构。
2 执行表结构修改，然后从原表中的数据到copy到表结构修改后的表，
3 在原表上创建触发器将 copy 数据的过程中，在原表的更新操作更新到新表.
注意:如果表中已经定义了触发器这个工具就不能工作了。
4 copy 完成以后，用rename table 新表代替原表，默认删除原表。
用法介绍：
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
--dry-run
这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
--execute
这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。
依赖条件:
操作的表必须有主键否则报如下错误:
[root@linuxidc ~]# pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter="add column vname varchar（20）" --execute D=linuxidc,t=linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc`...
Creating new table...
Created new table linuxidc._linuxidc_new OK.
Altering new table...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T17:51:43 Dropping new table...
2016-01-08T17:51:43 Dropped new table OK.
`linuxidc`.`linuxidc` was not altered.
The new table `linuxidc`.`_linuxidc_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
字段vname没有添加成功！
mysql> show create table linuxidcG
*************************** 1. row ***************************
Table: linuxidc
Create Table: CREATE TABLE `linuxidc` （
`actor_id` smallint（8） unsigned NOT NULL DEFAULT "0",
`first_name` varchar（45） NOT NULL,
`last_name` varchar（45） NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
） ENGINE=InnoDB DEFAULT CHARSET=utf8
添加主键：
mysql> alter table linuxidc modify actor_id smallint（8） unsigned primary key;
Query OK, 0 rows affected （0.06 sec）
Records: 0 Duplicates: 0 Warnings: 0
[root@linuxidc ~]# pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter="add column vname varchar（20）" --execute D=linuxidc,t=linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc`...
Creating new table...
Created new table linuxidc._linuxidc_new OK.
Altering new table...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T17:57:09 Creating triggers...
2016-01-08T17:57:09 Created triggers OK.
2016-01-08T17:57:09 Copying approximately 200 rows...
2016-01-08T17:57:09 Copied rows OK.
2016-01-08T17:57:09 Swapping tables...
2016-01-08T17:57:09 Swapped original and new tables OK.
2016-01-08T17:57:09 Dropping old table...
2016-01-08T17:57:09 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T17:57:09 Dropping triggers...
2016-01-08T17:57:09 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.
mysql> show create table linuxidcG
*************************** 1. row ***************************
Table: linuxidc
Create Table: CREATE TABLE `linuxidc` （
`actor_id` smallint（8） unsigned NOT NULL,
`first_name` varchar（45） NOT NULL,
`last_name` varchar（45） NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`vname` varchar（20） DEFAULT NULL,
PRIMARY KEY （`actor_id`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8添加多个字段：
[root@linuxidc ~]# pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter="add column aname varchar（20）,add column bname varchar（30）" --execute D=linuxidc,t=linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc`...
Creating new table...
Created new table linuxidc._linuxidc_new OK.
Altering new table...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18:04:25 Creating triggers...
2016-01-08T18:04:25 Created triggers OK.
2016-01-08T18:04:25 Copying approximately 200 rows...
2016-01-08T18:04:25 Copied rows OK.
2016-01-08T18:04:25 Swapping tables...
2016-01-08T18:04:26 Swapped original and new tables OK.
2016-01-08T18:04:26 Dropping old table...
2016-01-08T18:04:26 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18:04:26 Dropping triggers...
2016-01-08T18:04:26 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.
mysql> show create table linuxidcG
*************************** 1. row ***************************
Table: linuxidc
Create Table: CREATE TABLE `linuxidc` （
`actor_id` smallint（8） unsigned NOT NULL,
`first_name` varchar（45） NOT NULL,
`last_name` varchar（45） NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`vname` varchar（20） DEFAULT NULL,
`aname` varchar（20） DEFAULT NULL,
`bname` varchar（30） DEFAULT NULL,
PRIMARY KEY （`actor_id`）
） ENGINE=InnoDB DEFAULT CHARSET=utf8
删除字段：
[root@linuxidc ~]# pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter="drop column aname,drop column bname" --execute D=linuxidc,t=linuxidc
No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc`...
Creating new table...
Created new table linuxidc._linuxidc_new OK.
Altering new table...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18:05:45 Creating triggers...
2016-01-08T18:05:45 Created triggers OK.
2016-01-08T18:05:45 Copying approximately 200 rows...
2016-01-08T18:05:45 Copied rows OK.
2016-01-08T18:05:45 Swapping tables...
2016-01-08T18:05:45 Swapped original and new tables OK.
2016-01-08T18:05:45 Dropping old table...
2016-01-08T18:05:45 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18:05:45 Dropping triggers...
2016-01-08T18:05:46 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.
添加索引：
[root@linuxidc ~]# pt-online-schema-change -u root -plinuxidc -h127.0.0.1 --alter="add key index_first（first_name）" --execute D=linuxidc,t=linuxidc No slaves found. See --recursion-method if host linuxidc.com has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
Altering `linuxidc`.`linuxidc`...
Creating new table...
Created new table linuxidc._linuxidc_new OK.
Altering new table...
Altered `linuxidc`.`_linuxidc_new` OK.
2016-01-08T18:06:38 Creating triggers...
2016-01-08T18:06:38 Created triggers OK.
2016-01-08T18:06:38 Copying approximately 200 rows...
2016-01-08T18:06:38 Copied rows OK.
2016-01-08T18:06:38 Swapping tables...
2016-01-08T18:06:38 Swapped original and new tables OK.
2016-01-08T18:06:38 Dropping old table...
2016-01-08T18:06:38 Dropped old table `linuxidc`.`_linuxidc_old` OK.
2016-01-08T18:06:38 Dropping triggers...
2016-01-08T18:06:38 Dropped triggers OK.
Successfully altered `linuxidc`.`linuxidc`.本文永久更新链接地址