Mysql5.6 借助工具pt-online-schema-change在线DDL（还有加索引）

   MySQL在线ddl会阻塞所有insert，可以使用percona-toolkit中的pt-online-schema-change工具来对表进行online。

D=tpcctest 数据库名

t=customer 表名

[root@localhost bin]# ./pt-online-schema-change -u root -h127.0.0.1 -proot  --alter='add column col int ' --execute D=tpcctest,t=customer 

No slaves found.  See --recursion-method if host localhost.localdomain 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 `tpcctest`.`customer`...
Creating new table...
Created new table tpcctest._customer_new OK.
Altering new table...
Altered `tpcctest`.`_customer_new` OK.
2017-06-12T01:05:03 Creating triggers...
2017-06-12T01:05:03 Created triggers OK.
2017-06-12T01:05:03 Copying approximately 57629 rows...
2017-06-12T01:05:08 Copied rows OK.
2017-06-12T01:05:08 Swapping tables...
2017-06-12T01:05:08 Swapped original and new tables OK.
2017-06-12T01:05:08 Dropping old table...
2017-06-12T01:05:08 Dropped old table `tpcctest`.`_customer_old` OK.
2017-06-12T01:05:08 Dropping triggers...
2017-06-12T01:05:08 Dropped triggers OK.

Successfully altered `tpcctest`.`customer`.

online的操作步骤是：

1、创建一个和原来表结构一样的临时表并ddl;
2、将原表数据写入到临时表中（通过insert ...select方式）,并且在原表上创建触发器，如果原表有数据写入，通过触发器方式将新增的数据写入临时表中(前提该表之前没有触发器);
3、写入完后，重命名临时表和原表名称;
4、删除原表。

注：表一定要有主键。

mysql> create table t2(id int primary key,name int);

mysql> show create table t2;              
+-------+---------------------------------
| Table | Create Table                    
+-------+---------------------------------
| t2    | CREATE TABLE `t2` (             
  `id` int(11) NOT NULL,                  
  `name` int(11) DEFAULT NULL,            
  PRIMARY KEY (`id`)                      
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |    
+-------+------------------------------   

mysql> insert into t2 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)

[root@localhost bin]# ./pt-online-schema-change --user=root -proot  --host=localhost --socket=/opt/mysql/mysqldata/3306/mysql.sock --alter "add INDEX idx_t2_id(id)" D=test,t=t2  --execute
No slaves found.  See --recursion-method if host localhost.localdomain 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 `test`.`t2`...
Creating new table...
Created new table test._t2_new OK.
Altering new table...
Altered `test`.`_t2_new` OK.
2017-06-20T01:39:24 Creating triggers...
2017-06-20T01:39:24 Created triggers OK.
2017-06-20T01:39:24 Copying approximately 2 rows...
2017-06-20T01:39:24 Copied rows OK.
2017-06-20T01:39:24 Swapping tables...
2017-06-20T01:39:24 Swapped original and new tables OK.
2017-06-20T01:39:24 Dropping old table...
2017-06-20T01:39:24 Dropped old table `test`.`_t2_old` OK.
2017-06-20T01:39:24 Dropping triggers...
2017-06-20T01:39:24 Dropped triggers OK.
Successfully altered `test`.`t2`.

mysql> show create table t2;             
+-------+--------------------------------
| Table | Create Table                   
+-------+--------------------------------
| t2    | CREATE TABLE `t2` (            
  `id` int(11) NOT NULL,                 
  `name` int(11) DEFAULT NULL,           
  PRIMARY KEY (`id`),                    
  KEY `idx_t2_id` (`id`)                 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |   
+-------+--------------------------------