MYSQL ONLINE DDL--PT-ONLINE-CHANGE-SCHEMA

PT_ONLINE_CHANGE_SCHEMA
原理：
１.　创建需要执行ALTER操作的原表的一个临时表，然后在临时表中更改表结构。
２.　在原表中创建触发器（３个）三个触发器对应INSERT,UPDATE, DELETE操作。
３.　从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。
４.　RENAME原表到OLD表中，　在把临时表RENAME为原表，归后将原表删除（可以不删除），将原表上所创建的触发器删除。

测试：
创建测试表：

create table test(id int);
delimiter $
SET AUTOCOMMIT = 0 $$
 create  procedure test()  
begin  
declare i decimal (10) default 0 ;  
dd:loop  
INSERT INTO `test` (`id`) VALUES(i);  
commit;  
set i = i+1;  
if i= 10000000 then leave dd;  
end if;  
end loop dd ;  
end; $
delimiter ;
call test;

 

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  9240930 |
+----------+
1 row in set (6.15 sec)

增加字段name not null , 默认值A

pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="add column name varchar(60)" D=test,t=test --exec


Cannot chunk the original table `test`.`test`: There is no good index and the table is oversized. at /apps/rpm/percona-toolkit-2.2.2/bin/pt-online-schema-change line 5386.

这玩意限制好多，　没有好的索引也不行。

create index test_idx on test(id);

删除索引：

drop index test_idx on test;

将ID设置为主键。

alter table test add primary key(id);

增加字段可以为NULL。

[apps@gd2s93 bin]$ pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="add column name varchar(60)" D=test,t=test --exec
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 `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 9241629 rows...
Copying `test`.`test`:  18% 02:12 remain
Copying `test`.`test`:  37% 01:39 remain
Copying `test`.`test`:  56% 01:08 remain
Copying `test`.`test`:  75% 00:39 remain
Copying `test`.`test`:  94% 00:09 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`.

删除字段：

pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="drop column name" D=test,t=test --exec
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 `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 9241809 rows...
Copying `test`.`test`:  18% 02:13 remain
Copying `test`.`test`:  37% 01:39 remain
Copying `test`.`test`:  56% 01:08 remain
Copying `test`.`test`:  75% 00:38 remain
Copying `test`.`test`:  94% 00:08 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`.

添加字段：　值为非NULL，　默认值为A

pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="add column name varchar(60) not null default 'a' " D=test,t=test --exec

[apps@gd2s93 bin]$ pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="add column name varchar(60) not null default 'a' " D=test,t=test --exec
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 `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 9241629 rows...
Copying `test`.`test`:  18% 02:13 remain
Copying `test`.`test`:  35% 01:47 remain
Copying `test`.`test`:  54% 01:15 remain
Copying `test`.`test`:  73% 00:43 remain
Copying `test`.`test`:  91% 00:13 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`.

给NAME字段加索引。

pt-online-schema-change --user=root --password=vipshop --socket=/tmp/mysql3306.sock --port=3306 --charset=utf8 --nodrop-old-table --alter="add index name_idx(name)" D=test,t=test --exec
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 `test`.`test`...
Creating new table...
Created new table test._test_new OK.
Altering new table...
Altered `test`.`_test_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 9241755 rows...
Copying `test`.`test`:  12% 03:24 remain
Copying `test`.`test`:  25% 02:51 remain
Copying `test`.`test`:  39% 02:19 remain
Copying `test`.`test`:  52% 01:47 remain
Copying `test`.`test`:  65% 01:17 remain
Copying `test`.`test`:  78% 00:48 remain
Copying `test`.`test`:  92% 00:17 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `test`.`test`

注意：　ONLINE DDL是会锁表的。　另外表里必须有唯一索引或者主键才可以使用这个功能。