接到一个需求，生产环境需要给某表加个字段 ADD COLUMN isYearVip tinyint(4) Default 0

首先上去查看了一下该表的信息，已有30个字段，约2400万行数据，数据量约4.6G，很显然不能简单的用alter table add col 去处理，锁表时间太长，业务不允许。正好就实践一下percona的pt-online-schema-change工具。当然还是要先在测试环境先跑一下pt-online-schema-change的常用参数，基本没问题了再到生产环境。

 

说一下遇到的两个问题：

1. 第一次使用一般都会遇到

Exiting without altering ss.tablexxxxxx because you did not specify --execute.  Please read the tool's documentation carefully before using this tool.

没加--execute 参数情况下工具不会修改表，另外pt-online-schema-change 修改的表必须要有主键，无主键表执行不成功。

2.slave配置了Replicate_Do_Table: xxx.tablexxx  ，pt-online-schema-change 这个工具在进行alter时用的是database.table的方式访问表，所以这个表的修改不会同步到slave，这是Replicate_Do_Table的规则所致。

 

以下是具体的执行环境和执行记录

--耗时16m37.107s

--主机 memory 72G

--innodb_buffer_pool_size=40G

--innodb_flush_log_at_trx_commit = 0

--innodb_flush_method = O_DIRECT

--table data size 4.6G

--data count  2400w

--table structure

+---------------------+--------------+------+-----+---------+-------+
| Field                          | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| xxxxxxxxxxxxxxxxxxx | int(11)      | NO   | PRI | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | char(2)      | YES  |     | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | tinyint(4)   | YES  |     | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | tinyint(1)   | YES  |     | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | decimal(9,1) | YES  |     | 0.0     |       |
| xxxxxxxxxxxxxxxxxxx | datetime     | YES  |     | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | datetime     | YES  |     | NULL    |       |
| xxxxxxxxxxxxxxxxxxx | varchar(64)  | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+

 

[root@host bin]# ./pt-online-schema-change --user=admin --password='*******' --socket=/data/socket/mysql3306.sock --alter "ADD COLUMN isYearVip tinyint(4) Default 0" D=xxxxx,t=tablexxxxxx
# 2013-05-08T17:55:11 ./pt-online-schema-change started
# 2013-05-08T17:55:12 USE `xxxxx`
# 2013-05-08T17:55:12 Alter table tablexxxxxx using temporary table __tmp_tablexxxxxx
# 2013-05-08T17:55:12 Checking if table tablexxxxxx can be altered
# 2013-05-08T17:55:12 SHOW TRIGGERS FROM `xxxxx` LIKE 'tablexxxxxx'
# 2013-05-08T17:55:12 Table tablexxxxxx can be altered
# 2013-05-08T17:55:12 Chunk column uid, index PRIMARY
# 2013-05-08T17:55:12 Chunked table tablexxxxxx into 24442 chunks
# 2013-05-08T17:55:12 Exiting without altering ss.tablexxxxxx because you did not specify --execute.  Please read the tool's documentation carefully before using this tool.


[root@host bin]# time ./pt-online-schema-change --user=admin --password='*******'  --socket=/data/socket/mysql3306.sock --alter "ADD COLUMN isYearVip tinyint(4) Default 0" --execute D=xxxx,t=tablexxxxxx
# 2013-05-08T17:55:43 ./pt-online-schema-change started
# 2013-05-08T17:55:43 USE `xxxxx`
# 2013-05-08T17:55:43 Alter table tablexxxxxx using temporary table __tmp_tablexxxxxx
# 2013-05-08T17:55:43 Checking if table tablexxxxxx can be altered
# 2013-05-08T17:55:43 SHOW TRIGGERS FROM `xxxxx` LIKE 'tablexxxxxx'
# 2013-05-08T17:55:43 Table tablexxxxxx can be altered
# 2013-05-08T17:55:43 Chunk column uid, index PRIMARY
# 2013-05-08T17:55:44 Chunked table tablexxxxxx into 24442 chunks
# 2013-05-08T17:55:44 Starting online schema change
# 2013-05-08T17:55:44 CREATE TABLE `xxxxx`.`__tmp_tablexxxxxx` LIKE `xxxxx`.`tablexxxxxx`
# 2013-05-08T17:55:44 ALTER TABLE `xxxxx`.`__tmp_tablexxxxxx` ADD COLUMN isYearVip tinyint(4) Default 0
# 2013-05-08T17:55:44 Shared columns: column1,column 2,column 3...

# 2013-05-08T17:55:44 Calling OSCCaptureSync::capture()
# 2013-05-08T17:55:44 CREATE TRIGGER mk_osc_del AFTER DELETE ON `xxxxx`.`tablexxxxxx` FOR EACH ROW DELETE IGNORE FROM `xxxxx`.`__tmp_tablexxxxxx` WHERE `xxxxx`.`__tmp_tablexxxxxx`.`xxid` = OLD.`xxid`
# 2013-05-08T17:55:44 CREATE TRIGGER mk_osc_upd AFTER UPDATE ON `xxxxx`.`tablexxxxxx` .....

# 2013-05-08T17:55:44 CREATE TRIGGER mk_osc_ins AFTER INSERT ON `xxxxx`.`tablexxxxxx` .....

# 2013-05-08T17:55:44 Calling CopyRowsInsertSelect::copy()
Copying rows:   2% 20:02 remain
Copying rows:   4% 20:04 remain
Copying rows:   7% 18:48 remain
Copying rows:  10% 17:56 remain
Copying rows:  13% 16:01 remain
Copying rows:  16% 15:29 remain
Copying rows:  19% 14:44 remain
Copying rows:  21% 14:39 remain
Copying rows:  23% 14:20 remain
Copying rows:  27% 13:22 remain
Copying rows:  29% 13:07 remain
Copying rows:  32% 12:24 remain
Copying rows:  35% 11:52 remain
Copying rows:  38% 11:15 remain
Copying rows:  41% 10:39 remain
Copying rows:  44% 10:09 remain
Copying rows:  46% 09:35 remain
Copying rows:  49% 09:21 remain
Copying rows:  51% 09:02 remain
Copying rows:  53% 08:43 remain
Copying rows:  55% 08:23 remain
Copying rows:  58% 07:50 remain
Copying rows:  61% 07:16 remain
Copying rows:  64% 06:39 remain
Copying rows:  67% 06:07 remain
Copying rows:  69% 05:40 remain
Copying rows:  72% 05:05 remain
Copying rows:  75% 04:30 remain
Copying rows:  78% 03:52 remain
Copying rows:  82% 03:10 remain
Copying rows:  87% 02:09 remain
Copying rows:  92% 01:15 remain
Copying rows:  98% 00:12 remain
# 2013-05-08T18:12:20 Calling OSCCaptureSync::sync()
# 2013-05-08T18:12:20 Renaming tables
# 2013-05-08T18:12:20 RENAME TABLE `xxxxx`.`tablexxxxxx` TO `xxxxx`.`__old_tablexxxxxx`, `xxxxx`.`__tmp_tablexxxxxx` TO `xxxxx`.`tablexxxxxx`
# 2013-05-08T18:12:20 Original table tablexxxxxx renamed to __old_tablexxxxxx
# 2013-05-08T18:12:20 Calling CopyRowsInsertSelect::cleanup()
# 2013-05-08T18:12:20 Calling OSCCaptureSync::cleanup()
# 2013-05-08T18:12:20 DROP TRIGGER IF EXISTS `xxxxx`.`mk_osc_del`
# 2013-05-08T18:12:20 DROP TRIGGER IF EXISTS `xxxxx`.`mk_osc_ins`
# 2013-05-08T18:12:20 DROP TRIGGER IF EXISTS `xxxxx`.`mk_osc_upd`
# 2013-05-08T18:12:20 ./pt-online-schema-change ended, exit status 0

real    16m37.107s
user    0m6.035s
sys     0m1.408s
[root@host bin]#