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 |
+-------+--------------------------------