本文主要描述某企业MySQL数据库使用pt-online-change-schema进行在线加字段的操作。
欢迎转载，请注明作者、出处。
作者：张正
blog：http://space.itpub.net/26355921
QQ：176036317
如有疑问，欢迎联系。
具体为：151主库有两个备库153和154。
先需要对dolcur库中的account表增加uuid字段。该表为用户表，属比较核心数据，数据量约为260W，存储引擎为innodb。
account表结构为：
mysql>
desc account
;
+----------------------------+--------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+--------------+------+-----+-------------------+-----------------------------+
| AccountId | int(11) | NO | PRI | NULL | auto_increment |
| SWID | varchar(64) | NO | MUL | NULL | |
| Username | varchar(200) | NO | UNI | NULL | |
| Password | varchar(64) | YES | | NULL | |
| PasswordHint | char(15) | YES | | NULL | |
| PasswordHintValue | varchar(100) | YES | | NULL | |
| Prefix | varchar(20) | YES | | NULL | |
| FirstName | varchar(64) | YES | | NULL | |
| Initial | varchar(20) | YES | | NULL | |
| LastName | varchar(64) | YES | | NULL | |
| Suffix | varchar(20) | YES | | NULL | |
| Gender | varchar(1) | YES | | NULL | |
| Birthday | datetime | YES | | NULL | |
| EmailAddress | varchar(200) | YES | MUL | NULL | |
| ParentEmailAddress | varchar(200) | YES | | NULL | |
| PostalCode | varchar(20) | YES | | NULL | |
| IsoCountryCode | char(2) | YES | | CN | |
| IsoLanguageCode | char(3) | YES | | zh | |
| RegionCode | varchar(30) | YES | | NULL | |
| AffiliateName | char(35) | YES | | CN_DOL | |
| SiteCountryCode | char(5) | YES | | NULL | |
| TimeZone | varchar(5) | YES | | 8 | |
| Occupation | varchar(64) | YES | | NULL | |
| DateRegistered | datetime | NO | | NULL | |
| SourceName | char(30) | YES | | CN_1007 | |
| Status | varchar(20) | YES | | NULL | |
| Validated | char(1) | YES | | Y | |
| DateValidated | datetime | YES | | NULL | |
| AutoLogin | int(11) | YES | | NULL | |
| TemplateName | char(35) | YES | | NULL | |
| SystemId | int(11) | YES | | NULL | |
| LegacyAffiliateId | int(11) | YES | | NULL | |
| LegacySourceId | int(11) | YES | | NULL | |
| TestAccount | char(1) | YES | | NULL | |
| DateCreated | datetime | NO | | NULL | |
| DateModified | datetime | NO | | NULL | |
| ModifiedBy | int(11) | YES | | NULL | |
| SiteId | int(11) | YES | | 12 | |
| RowVersion | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| AuthenticationSecondaryId | varchar(64) | YES | | NULL | |
| AuthenticationEmailAddress | varchar(200) | YES | | NULL | |
| NickName | varchar(40) | YES | MUL | NULL | |
| NumberOfKids | int(3) | YES | | NULL | |
| C_EmailOptin | varchar(1) | YES | | NULL | |
| C_ExtEmailOptin | varchar(1) | YES | | NULL | |
| C_PostOptin | varchar(1) | YES | | Y | |
| C_ExtPostOptin | varchar(1) | YES | | NULL | |
| C_SmsOptin | varchar(1) | YES | | NULL | |
| C_ExtSmsOptin | varchar(1) | YES | | NULL | |
| C_TelOptin | varchar(1) | YES | | NULL | |
| C_ExtTelOptin | varchar(1) | YES | | NULL | |
| C_GuardianOptin | varchar(1) | YES | | NULL | |
| C_SocialSecurityNumber | varchar(20) | YES | | NULL | |
| UserType | varchar(10) | YES | | NULL | |
| IpAddress | varchar(40) | YES | | NULL | |
| HeadImage1 | varchar(200) | YES | | NULL | |
| HeadImage2 | varchar(200) | YES | | NULL | |
| HeadImage3 | varchar(200) | YES | | NULL | |
| NameOptin | varchar(1) | YES | | NULL | |
| BirthdayOptin | varchar(1) | YES | | NULL | |
| GenderOptin | varchar(1) | YES | | NULL | |
| TjAccountId | int(11) | YES | | NULL | |
| DateMoNickName | datetime | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| newHeadImage1 | varchar(200) | YES | | NULL | |
| newNickName | varchar(40) | YES | | NULL | |
| telephone | varchar(21) | YES | | NULL | |
| fromType | int(11) | YES | | NULL | |
| childrenInHS | bit(1) | YES | | NULL | |
+----------------------------+--------------+------+-----+-------------------+-----------------------------+
69 rows in set (0.01 sec)
pt-online-change-schema原理为：
1. 创建需要执行alter操作的原表的一个临时表，然后在临时表中更改表结构。
2. 在原表中创建触发器（3个）三个触发器分别对应insert,update,delete操作
3. 从原表拷贝数据到临时表，拷贝过程中在原表进行的写操作都会更新到新建的临时表。
4. Rename 原表到old表中，在把临时表Rename为原表，最后将原表删除(可能不删除)，将原表上所创建的触发器删除。
首先安装percona-toolkit-2.2.4-1.noarch.rpm包，可能会报错：
perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-2.2.4-1.noarch
perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.4-1.noarch
即，需要安装所提示的两个包：
[root@dcnmysql-01 geygle]#
yum list|grep DBD
perl-DBD-SQLite.x86_64 1.27-3.el6 @anaconda-CentOS-201207061011.x86_64/6.3
perl-DBD-MySQL.x86_64 4.013-3.el6 base
perl-DBD-Pg.x86_64 2.15.1-4.el6_3 base
[root@dcnmysql-01 geygle]#
yum list|grep Socket|grep SSL
perl-IO-Socket-SSL.noarch 1.31-2.el6 base
安装包：
[root@dcnmysql-01 geygle]#
yum -y install perl-DBD-MySQL.x86_64
[root@dcnmysql-01 geygle]#
yum -y install perl-IO-Socket-SSL.noarch
如果自己的mysql是通过rpm包安装的比较新的版本，而本系统中这两个包自带的mysql-libs包版本比较低，可能会发生冲突。
这种情况下，需要上官方网站下载相应数据库版本的mysql-shared.....、mysql-shared-compat、mysql-..bundle...、mysql-embedded...等包，安装即可。
就不会提示mysql-libs包与mysql-server包冲突。
开始进行Online DDL操作，使用pt-online-change-schema加字段：
[mysql@dcnmysql-01 ~]$
mysql -u root -pxxxxxxxxx
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5092440
Server version: 5.5.29-log Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_xhcms |
| dolactivity |
| dolcms |
|
dolcur
|
| dolgame |
| dolgame_cms |
| mysql |
| mysqlbak |
| performance_schema |
| questionplatform. |
| test |
+--------------------+
12 rows in set (0.01 sec)
mysql> use dolcur;
Database changed
mysql> show tables;
+---------------------------------+
| Tables_in_dolcur |
+---------------------------------+
|
account
|
| account2 |
| account_modified |
| accountaddress |
| accountaddress2 |
| accountid3 |
| .............................. |
| accountphone |
| ................................... |
| test |
| test_account |
+---------------------------------+
96 rows in set (0.00 sec)
mysql>
show processlist;
+---------+-------------+------------------+------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+-------------+------------------+------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 51 | dolcur | 10.0.0.73:42892 | dolcur | Sleep | 274 | | NULL |
| 1556 | repl | 10.0.6.153:38752 | NULL | Binlog Dump | 18657603 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 3168 | repl | 10.0.6.154:52142 | NULL | Binlog Dump | 18657382 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 20373 | dolcur | 10.0.0.73:38859 | dolcur | Sleep | 105 | | NULL |
| 289193 | dolcur | 10.0.0.73:50866 | dolcur | Sleep | 191 | | NULL |
| 289194 | dolcur | 10.0.0.73:50867 | dolcur | Sleep | 191 | | NULL |
| 3114984 | dolcur | 10.0.0.73:38837 | dolcur | Sleep | 20 | | NULL |
| 4495330 | dolcms | 10.0.6.135:35867 | dolcms | Sleep | 379 | | NULL |
| 4495331 | dolcms | 10.0.6.135:35868 | dolcms | Sleep | 92 | | NULL |
| 4495332 | dolcms | 10.0.6.135:35869 | dolcms | Sleep | 52 | | NULL |
| 4495333 | dolcms | 10.0.6.135:35870 | dolcms | Sleep | 92 | | NULL |
| 4495334 | dolcms | 10.0.6.135:35871 | dolcms | Sleep | 423 | | NULL |
| 4495335 | dolcms | 10.0.6.135:35872 | dolcms | Sleep | 92 | | NULL |
| 4495336 | dolcms | 10.0.6.135:35873 | dolcms | Sleep | 156 | | NULL |
| 4495337 | dolcms | 10.0.6.135:35874 | dolcms | Sleep | 53 | | NULL |
| 4495338 | dolcms | 10.0.6.135:35875 | dolcms | Sleep | 92 | | NULL |
| 4495339 | dolcms | 10.0.6.135:35876 | dolcms | Sleep | 269 | | NULL |
| 4495340 | dolcms | 10.0.6.135:35877 | dolcms | Sleep | 248 | | NULL |
| 4495341 | dolcms | 10.0.6.135:35878 | dolcms | Sleep | 76 | | NULL |
| 4495342 | dolcms | 10.0.6.135:35879 | dolcms | Sleep | 379 | | NULL |
| 4495343 | dolcms | 10.0.6.135:35880 | dolcms | Sleep | 27 | | NULL |
| 4495344 | dolcms | 10.0.6.135:35881 | dolcms | Sleep | 51 | | NULL |
| 4495345 | dolcms | 10.0.6.135:35882 | dolcms | Sleep | 423 | | NULL |
| 4495346 | dolcms | 10.0.6.135:35883 | dolcms | Sleep | 51 | | NULL |
| 4495347 | dolcms | 10.0.6.135:35884 | dolcms | Sleep | 379 | | NULL |
| 4495348 | dolcms | 10.0.6.135:35885 | dolcms | Sleep | 51 | | NULL |
| 4495349 | dolcms | 10.0.6.135:35886 | dolcms | Sleep | 379 | | NULL |
| 4495350 | dolcms | 10.0.6.135:35887 | dolcms | Sleep | 279 | | NULL |
| 4495351 | dolcms | 10.0.6.135:35888 | dolcms | Sleep | 422 | | NULL |
| 4495352 | dolcms | 10.0.6.135:35889 | dolcms | Sleep | 156 | | NULL |
| 4495353 | dolcms | 10.0.6.135:35890 | dolcms | Sleep | 27 | | NULL |
| 4495354 | dolcms | 10.0.6.135:35891 | dolcms | Sleep | 27 | | NULL |
| 4495355 | dolcms | 10.0.6.135:35892 | dolcms | Sleep | 423 | | NULL |
| 4495356 | dolcms | 10.0.6.135:35893 | dolcms | Sleep | 27 | | NULL |
| 4495357 | dolcms | 10.0.6.135:35894 | dolcms | Sleep | 468 | | NULL |
| 4495358 | dolcms | 10.0.6.135:35895 | dolcms | Sleep | 11 | | NULL |
| 4495359 | dolcms | 10.0.6.135:35896 | dolcms | Sleep | 156 | | NULL |
| 4495393 | dolcms | 10.0.6.137:45998 | dolcms | Sleep | 21 | | NULL |
| 4495394 | dolcms | 10.0.6.137:45999 | dolcms | Sleep | 2454 | | NULL |
| 4495395 | dolcms | 10.0.6.137:46000 | dolcms | Sleep | 2851 | | NULL |
| 4495396 | dolcms | 10.0.6.137:46001 | dolcms | Sleep | 802 | | NULL |
| 4495397 | dolcms | 10.0.6.137:46002 | dolcms | Sleep | 410 | | NULL |
| 4495398 | dolcms | 10.0.6.137:46003 | dolcms | Sleep | 801 | | NULL |
| 4495399 | dolcms | 10.0.6.137:46004 | dolcms | Sleep | 2577 | | NULL |
| 4495400 | dolcms | 10.0.6.137:46005 | dolcms | Sleep | 410 | | NULL |
| 4495401 | dolcms | 10.0.6.137:46006 | dolcms | Sleep | 2456 | | NULL |
| 4495402 | dolcms | 10.0.6.137:46007 | dolcms | Sleep | 582 | | NULL |
| 4495403 | dolcms | 10.0.6.137:46008 | dolcms | Sleep | 2729 | | NULL |
| 4495404 | dolcms | 10.0.6.137:46009 | dolcms | Sleep | 2576 | | NULL |
| 4495405 | dolcms | 10.0.6.137:46010 | dolcms | Sleep | 2847 | | NULL |
| 4495406 | dolcms | 10.0.6.137:46011 | dolcms | Sleep | 2028 | | NULL |
| 4495407 | dolcms | 10.0.6.137:46012 | dolcms | Sleep | 2665 | | NULL |
| 4495408 | dolcms | 10.0.6.137:46013 | dolcms | Sleep | 3181 | | NULL |
| 4495409 | dolcms | 10.0.6.137:46014 | dolcms | Sleep | 3180 | | NULL |
| 4495410 | dolcms | 10.0.6.137:46015 | dolcms | Sleep | 2743 | | NULL |
| 4495411 | dolcms | 10.0.6.137:46016 | dolcms | Sleep | 3041 | | NULL |
| 4495412 | dolcms | 10.0.6.137:46017 | dolcms | Sleep | 2997 | | NULL |
| 4495413 | dolcms | 10.0.6.137:46018 | dolcms | Sleep | 2281 | | NULL |
| 4495414 | dolcms | 10.0.6.137:46019 | dolcms | Sleep | 2273 | | NULL |
| 4495415 | dolcms | 10.0.6.137:46020 | dolcms | Sleep | 517 | | NULL |
| 4495416 | dolcms | 10.0.6.137:46021 | dolcms | Sleep | 2271 | | NULL |
| 4495417 | dolcms | 10.0.6.137:46022 | dolcms | Sleep | 688 | | NULL |
| 4495418 | dolcms | 10.0.6.137:46023 | dolcms | Sleep | 689 | | NULL |
| 4495419 | dolcms | 10.0.6.137:46024 | dolcms | Sleep | 2485 | | NULL |
| 4495420 | dolcms | 10.0.6.137:46025 | dolcms | Sleep | 3448 | | NULL |
| 4495421 | dolcms | 10.0.6.137:46026 | dolcms | Sleep | 3449 | | NULL |
| 4495422 | dolcms | 10.0.6.137:46027 | dolcms | Sleep | 516 | | NULL |
| 4672861 | dolcms | 10.0.6.136:48541 | dolcms | Sleep | 279 | | NULL |
| 4672862 | dolcms | 10.0.6.136:48542 | dolcms | Sleep | 54 | | NULL |
| 4672863 | dolcms | 10.0.6.136:48543 | dolcms | Sleep | 112 | | NULL |
| 4672864 | dolcms | 10.0.6.136:48544 | dolcms | Sleep | 461 | | NULL |
| 4672865 | dolcms | 10.0.6.136:48545 | dolcms | Sleep | 112 | | NULL |
| 4672866 | dolcms | 10.0.6.136:48546 | dolcms | Sleep | 332 | | NULL |
| 4672867 | dolcms | 10.0.6.136:48547 | dolcms | Sleep | 73 | | NULL |
| 4672868 | dolcms | 10.0.6.136:48548 | dolcms | Sleep | 297 | | NULL |
| 4672869 | dolcms | 10.0.6.136:48549 | dolcms | Sleep | 375 | | NULL |
| 4672870 | dolcms | 10.0.6.136:48550 | dolcms | Sleep | 402 | | NULL |
| 4672871 | dolcms | 10.0.6.136:48551 | dolcms | Sleep | 54 | | NULL |
| 4672872 | dolcms | 10.0.6.136:48552 | dolcms | Sleep | 265 | | NULL |
| 4672873 | dolcms | 10.0.6.136:48553 | dolcms | Sleep | 137 | | NULL |
| 4672874 | dolcms | 10.0.6.136:48554 | dolcms | Sleep | 208 | | NULL |
| 4672875 | dolcms | 10.0.6.136:48555 | dolcms | Sleep | 208 | | NULL |
| 4672876 | dolcms | 10.0.6.136:48556 | dolcms | Sleep | 72 | | NULL |
| 4672877 | dolcms | 10.0.6.136:48557 | dolcms | Sleep | 63 | | NULL |
| 4672878 | dolcms | 10.0.6.136:48558 | dolcms | Sleep | 137 | | NULL |
| 4672879 | dolcms | 10.0.6.136:48559 | dolcms | Sleep | 63 | | NULL |
| 4672880 | dolcms | 10.0.6.136:48560 | dolcms | Sleep | 735 | | NULL |
| 4672881 | dolcms | 10.0.6.136:48561 | dolcms | Sleep | 736 | | NULL |
| 4672882 | dolcms | 10.0.6.136:48562 | dolcms | Sleep | 63 | | NULL |
| 4672883 | dolcms | 10.0.6.136:48563 | dolcms | Sleep | 756 | | NULL |
| 4672884 | dolcms | 10.0.6.136:48564 | dolcms | Sleep | 392 | | NULL |
| 4672885 | dolcms | 10.0.6.136:48565 | dolcms | Sleep | 640 | | NULL |
| 4672886 | dolcms | 10.0.6.136:48566 | dolcms | Sleep | 392 | | NULL |
| 4672887 | dolcms | 10.0.6.136:48567 | dolcms | Sleep | 964 | | NULL |
| 4672888 | dolcms | 10.0.6.136:48568 | dolcms | Sleep | 769 | | NULL |
| 4672889 | dolcms | 10.0.6.136:48569 | dolcms | Sleep | 1051 | | NULL |
| 4672890 | dolcms | 10.0.6.136:48570 | dolcms | Sleep | 118 | | NULL |
| 4675774 | dolcms | 10.0.6.136:49442 | dolcms | Sleep | 208 | | NULL |
| 4675775 | dolcms | 10.0.6.136:49443 | dolcms | Sleep | 208 | | NULL |
| 4675776 | dolcms | 10.0.6.136:49444 | dolcms | Sleep | 108 | | NULL |
| 4675777 | dolcms | 10.0.6.136:49445 | dolcms | Sleep | 208 | | NULL |
| 4675778 | dolcms | 10.0.6.136:49446 | dolcms | Sleep | 265 | | NULL |
| 4677299 | dolcms | 10.0.6.136:50279 | dolcms | Sleep | 108 | | NULL |
| 4677300 | dolcms | 10.0.6.136:50280 | dolcms | Sleep | 73 | | NULL |
| 4677301 | dolcms | 10.0.6.136:50281 | dolcms | Sleep | 108 | | NULL |
| 4677302 | dolcms | 10.0.6.136:50282 | dolcms | Sleep | 563 | | NULL |
| 4677303 | dolcms | 10.0.6.136:50283 | dolcms | Sleep | 392 | | NULL |
| 4680685 | dolcms | 10.0.6.135:37582 | dolcms | Sleep | 312 | | NULL |
| 4680686 | dolcms | 10.0.6.135:37583 | dolcms | Sleep | 312 | | NULL |
| 4680687 | dolcms | 10.0.6.135:37584 | dolcms | Sleep | 312 | | NULL |
| 4680688 | dolcms | 10.0.6.135:37585 | dolcms | Sleep | 312 | | NULL |
| 4680689 | dolcms | 10.0.6.135:37586 | dolcms | Sleep | 11 | | NULL |
| 4680772 | dolcms | 10.0.6.135:37599 | dolcms | Sleep | 132 | | NULL |
| 4680773 | dolcms | 10.0.6.135:37600 | dolcms | Sleep | 156 | | NULL |
| 4680774 | dolcms | 10.0.6.135:37601 | dolcms | Sleep | 848 | | NULL |
| 4680775 | dolcms | 10.0.6.135:37602 | dolcms | Sleep | 137 | | NULL |
| 4680776 | dolcms | 10.0.6.135:37603 | dolcms | Sleep | 52 | | NULL |
| 4697665 | xhcms | 10.0.6.178:58640 | db_xhcms | Sleep | 809 | | NULL |
| 4697666 | xhcms | 10.0.6.178:58641 | db_xhcms | Sleep | 809 | | NULL |
| 4697667 | xhcms | 10.0.6.178:58642 | db_xhcms | Sleep | 809 | | NULL |
| 4991291 | dolcms | 10.0.6.136:52402 | dolcms | Sleep | 54 | | NULL |
| 4991292 | dolcms | 10.0.6.136:52403 | dolcms | Sleep | 137 | | NULL |
| 4991293 | dolcms | 10.0.6.136:52404 | dolcms | Sleep | 265 | | NULL |
| 4991294 | dolcms | 10.0.6.136:52405 | dolcms | Sleep | 389 | | NULL |
| 4991295 | dolcms | 10.0.6.136:52406 | dolcms | Sleep | 208 | | NULL |
| 4996691 | dolcms | 10.0.6.136:53523 | dolcms | Sleep | 1 | | NULL |
| 4996692 | dolcms | 10.0.6.136:53524 | dolcms | Sleep | 1 | | NULL |
| 4996693 | dolcms | 10.0.6.136:53525 | dolcms | Sleep | 0 | | NULL |
| 4996694 | dolcms | 10.0.6.136:53526 | dolcms | Sleep | 0 | | NULL |
| 4996696 | dolcms | 10.0.6.136:53527 | dolcms | Sleep | 0 | | NULL |
| 5010748 | dolcms | 10.0.6.135:48709 | dolcms | Sleep | 51 | | NULL |
| 5010749 | dolcms | 10.0.6.135:48710 | dolcms | Sleep | 27 | | NULL |
| 5010750 | dolcms | 10.0.6.135:48711 | dolcms | Sleep | 156 | | NULL |
| 5010751 | dolcms | 10.0.6.135:48712 | dolcms | Sleep | 156 | | NULL |
| 5010752 | dolcms | 10.0.6.135:48713 | dolcms | Sleep | 379 | | NULL |
| 5015590 | dolcms | 10.0.6.135:49182 | dolcms | Sleep | 0 | | NULL |
| 5015591 | dolcms | 10.0.6.135:49183 | dolcms | Sleep | 1 | | NULL |
| 5015592 | dolcms | 10.0.6.135:49184 | dolcms | Sleep | 1 | | NULL |
| 5015593 | dolcms | 10.0.6.135:49185 | dolcms | Sleep | 0 | | NULL |
| 5015594 | dolcms | 10.0.6.135:49186 | dolcms | Sleep | 0 | | NULL |
| 5064984 | xhcms | 10.0.6.176:37564 | db_xhcms | Sleep | 337 | | NULL |
| 5064985 | xhcms | 10.0.6.176:37565 | db_xhcms | Sleep | 337 | | NULL |
| 5064986 | xhcms | 10.0.6.176:37567 | db_xhcms | Sleep | 337 | | NULL |
| 5066041 | xhcms | 10.0.6.173:33295 | db_xhcms | Sleep | 493 | | NULL |
| 5066042 | xhcms | 10.0.6.173:33296 | db_xhcms | Sleep | 493 | | NULL |
| 5066043 | xhcms | 10.0.6.173:33297 | db_xhcms | Sleep | 493 | | NULL |
| 5066044 | xhcms | 10.0.6.173:33298 | db_xhcms | Sleep | 493 | | NULL |
| 5066045 | xhcms | 10.0.6.173:33299 | db_xhcms | Sleep | 493 | | NULL |
| 5066046 | xhcms | 10.0.6.173:33300 | db_xhcms | Sleep | 493 | | NULL |
| 5067282 | xhcms | 10.0.6.174:47599 | db_xhcms | Sleep | 157 | | NULL |
| 5067283 | xhcms | 10.0.6.174:47600 | db_xhcms | Sleep | 157 | | NULL |
| 5067284 | xhcms | 10.0.6.174:47601 | db_xhcms | Sleep | 157 | | NULL |
| 5067289 | xhcms | 10.0.6.175:54796 | db_xhcms | Sleep | 155 | | NULL |
| 5067290 | xhcms | 10.0.6.175:54797 | db_xhcms | Sleep | 155 | | NULL |
| 5067291 | xhcms | 10.0.6.175:54798 | db_xhcms | Sleep | 155 | | NULL |
| 5090678 | wxh | 10.0.6.131:57874 | questionplatform. Sleep | 7930 | | NULL |
| 5092080 | dolgame_cms | 10.0.6.142:46448 | dolgame_cms | Sleep | 56 | | NULL |
| 5092081 | dolcur | 10.0.6.142:46449 | dolcur | Sleep | 56 | | NULL |
| 5092082 | dolcur | 10.0.6.142:46450 | dolcur | Sleep | 56 | | NULL |
| 5092154 | dolgame_cms | 10.0.6.142:46452 | dolgame_cms | Sleep | 56 | | NULL |
| 5092259 | dolgame_cms | 10.0.6.142:46455 | dolgame_cms | Sleep | 56 | | NULL |
| 5092341 | dolgame_cms | 10.0.6.142:46457 | dolgame_cms | Sleep | 56 | | NULL |
| 5092342 | dolcur | 10.0.6.142:46458 | dolcur | Sleep | 56 | | NULL |
| 5092343 | dolcur | 10.0.6.142:46459 | dolcur | Sleep | 2 | | NULL |
| 5092344 | dolgame | 10.0.6.142:46460 | dolgame | Sleep | 54 | | NULL |
| 5092345 | dolgame | 10.0.6.142:46461 | dolgame | Sleep | 54 | | NULL |
| 5092386 | dolcur | 10.0.6.121:54325 | dolcur | Sleep | 1 | | NULL |
| 5092391 | dolcur | 10.0.6.126:34512 | dolcur | Sleep | 11 | | NULL |
| 5092397 | dolcur | 10.0.6.121:54330 | dolcur | Sleep | 1 | | NULL |
| 5092410 | dolcur | 10.0.6.121:54334 | dolcur | Sleep | 1 | | NULL |
| 5092409 | dolcur | 10.0.6.121:54333 | dolcur | Sleep | 1 | | NULL |
| 5092421 | dolgame_cms | 10.0.6.142:46464 | dolgame_cms | Sleep | 2 | | NULL |
| 5092422 | dolcur | 10.0.6.142:46465 | dolcur | Query | 0 | Sending data | select count(*) as col_0_0_ from Account account0_ where (account0_.userType in ('publish' , 'no act |
| 5092423 | dolgame | 10.0.6.142:46466 | dolgame | Sleep | 54 | | NULL |
| 5092424 | dolgame | 10.0.6.142:46467 | dolgame | Sleep | 54 | | NULL |
| 5092425 | dolgame | 10.0.6.142:46468 | dolgame | Sleep | 54 | | NULL |
| 5092430 | dolcur | 10.0.6.121:54336 | dolcur | Sleep | 1 | | NULL |
| 5092431 | xhcms | 10.0.6.178:53695 | db_xhcms | Sleep | 52 | | NULL |
| 5092438 | dolcur | 10.0.6.126:34513 | dolcur | Sleep | 11 | | NULL |
| 5092439 | dolcms | 10.0.6.141:43285 | dolcms | Sleep | 22 | | NULL |
| 5092440 | root | localhost | dolcur | Query | 0 | NULL | show processlist |
| 5092441 | dolgame | 10.0.6.122:52151 | dolgame | Sleep | 5 | | NULL |
| 5092442 | dolgame | 10.0.6.122:52152 | dolgame | Sleep | 5 | | NULL |
| 5092443 | dolgame | 10.0.6.122:52153 | dolgame | Sleep | 5 | | NULL |
| 5092444 | dolcur | 10.0.6.122:52154 | dolcur | Sleep | 3 | | NULL |
| 5092445 | dolcur | 10.0.6.122:52155 | dolcur | Sleep | 5 | | NULL |
| 5092446 | dolgame | 10.0.6.122:52156 | dolgame | Sleep | 5 | | NULL |
| 5092447 | dolcur | 10.0.6.122:52157 | dolcur | Sleep | 3 | | NULL |
| 5092448 | dolcur | 10.0.6.122:52158 | dolcur | Sleep | 3 | | NULL |
+---------+-------------+------------------+------------------+-------------+----------+-----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
189 rows in set (0.01 sec)
可以看到数据库有两个备库，整个数据库比较闲，基本没有连接。
查看用户：
mysql>
select user,host,password from mysql.user;
+-------------+-------------+-------------------------------------------+
| user | host | password |
+-------------+-------------+-------------------------------------------+
| root | localhost | *6DE586C5C18B2C52B1E205906DE14E2FCE5DC986 |
| root | 127.0.0.1 | *6DE586C5C18B2C52B1E205906DE14E2FCE5DC986 |
| root | ::1 | *6DE586C5C18B2C52B1E205906DE14E2FCE5DC986 |
|................................................................................................................................................|
+-------------+-------------+-------------------------------------------+
15 rows in set (0.00 sec)
查看端口：
mysql>
show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port |
3306
|
+---------------+-------+
1 row in set (0.00 sec)
查看字符集：
mysql>
show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server |
utf8
|
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)
给dolcur.account表增加uuid varchar(60)字段：
执行pt-online-change-schema：
--nodrop-old-table是为了让操作完成后，不删除旧表：
[mysql@dcnmysql-01 ~]$
/usr/bin/pt-online-schema-change --user=root --password=xxxxxxxxx --host=127.0.0.1 --port=3306 --charset=utf8 --nodrop-old-table --alter="add column uuid varchar(60) null default null after childrenInHS " D=dolcur,t=account --exec
Cannot connect to A=utf8,D=dolcur,P=3306,h=10.0.6.153,p=...,u=root
Cannot connect to A=utf8,D=dolcur,P=3306,h=10.0.6.154,p=...,u=root
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
Child tables:
`dolcur`.`accountaddress` (approx. 2522237 rows)
`dolcur`.`accountphone` (approx. 942129 rows)
You did not specify --alter-foreign-keys-method, but there are foreign keys that reference the table. Please read the tool's documentation carefully.
发现报错，提示有两个表：
accountaddress、accountphone
有外键参照表account，需要使用
--alter-foreign-keys-method
参数指明对外键的操作。
--alter-foreign-keys-method 的值可以为：
auto、rebuild_constraints、drop_swap、none
四种
1、当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。
2、如果对外键表操作时，四种外键操作类型需要根据表的数据量和可靠程度，进行选择。处于可靠性的原因，尽量使用rebuild_constraints类型，如果没有可靠性要求，可以使用auto类型。
3、当--alter-foreign-keys-method=none时，外键索引将失效，需要重建外键索引，方可再次使用
4、由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。
加上--alter-foreign-keys-method 参数，再次执行：
[mysql@dcnmysql-01 ~]$
/usr/bin/pt-online-schema-change --user=root --password=xxxxxxxxx --host=127.0.0.1 --port=3306 --charset=utf8 --nodrop-old-table --alter-foreign-keys-method=rebuild_constraints --alter="add column uuid varchar(60) null default null after childrenInHS " D=dolcur,t=account --exec
Cannot connect to A=utf8,D=dolcur,P=3306,h=10.0.6.153,p=...,u=root
Cannot connect to A=utf8,D=dolcur,P=3306,h=10.0.6.154,p=...,u=root
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
Child tables:
`dolcur`.`accountaddress` (approx. 2442166 rows)
`dolcur`.`accountphone` (approx. 947363 rows)
Will use the rebuild_constraints method to update foreign keys.
Altering `dolcur`.`account`...
Creating new table...
Created new table dolcur._account_new OK.
Altering new table...
Altered `dolcur`.`_account_new` OK.
2013-10-15T23:54:22 Creating triggers...
2013-10-15T23:54:23 Created triggers OK.
2013-10-15T23:54:23 Copying approximately 2646925 rows...
Copying `dolcur`.`account`: 12% 03:36 remain
Copying `dolcur`.`account`: 24% 03:04 remain
Copying `dolcur`.`account`: 35% 02:44 remain
Copying `dolcur`.`account`: 47% 02:13 remain
Copying `dolcur`.`account`: 55% 01:58 remain
Copying `dolcur`.`account`: 65% 01:34 remain
Copying `dolcur`.`account`: 75% 01:08 remain
Copying `dolcur`.`account`: 83% 00:46 remain
Copying `dolcur`.`account`: 92% 00:21 remain
2013-10-15T23:59:07 Copied rows OK.
2013-10-15T23:59:07 Swapping tables...
2013-10-15T23:59:07 Swapped original and new tables OK.
2013-10-15T23:59:07 Rebuilding foreign key constraints...
2013-10-16T00:01:05 Rebuilt foreign key constraints OK.
2013-10-16T00:01:05 Dropping triggers...
2013-10-16T00:01:06 Dropped triggers OK.
Successfully altered `dolcur`.`account`.
操作完成，查看表：
mysql>
show tables;
+---------------------------------+
| Tables_in_dolcur |
+---------------------------------+
| _account_old |
| account |
| account2 |
| accountaddress |
|...................................................................|
|...................................................................|
| accountphone |
| test |
| test_account |
+---------------------------------+
97 rows in set (0.00 sec)
_account_old 为之前的account表，现在被rename了，但是并没有被删除。(因为加了--nodrop-old-table参数)
检查之前有外键参照account表的两张表中外键的情况：
mysql>
show create table accountaddress\G
*************************** 1. row ***************************
Table: accountaddress
Create Table: CREATE TABLE `accountaddress` (
`AccountAddressId` int(11) NOT NULL AUTO_INCREMENT,
`AccountId` int(11) NOT NULL,
`Line1` varchar(100) DEFAULT NULL,
`Line2` varchar(100) DEFAULT NULL,
`Line3` varchar(100) DEFAULT NULL,
`AddressCodeId` varchar(10) DEFAULT NULL,
`City` varchar(100) DEFAULT NULL,
`StateProvince` varchar(100) DEFAULT NULL,
`PostalCode` varchar(10) DEFAULT NULL,
`Country` varchar(100) DEFAULT NULL,
`County` varchar(100) DEFAULT NULL,
`Active` char(1) NOT NULL,
`Preferred` char(1) NOT NULL,
`AddressValidationLevelId` int(11) DEFAULT NULL,
`DateCreated` datetime NOT NULL,
`DateModified` datetime NOT NULL,
`ModifiedBy` int(11) DEFAULT NULL,
`AddressType` char(15) DEFAULT NULL,
PRIMARY KEY (`AccountAddressId`),
UNIQUE KEY `AccountId` (`AccountId`,`AddressType`),
KEY `FK_AccountAddress_AddressCode` (`AddressCodeId`),
KEY `FKAAA919272E64EFFE` (`AccountId`),
CONSTRAINT `
_FKAAA919272E64EFFE
` FOREIGN KEY (`AccountId`) REFERENCES `account` (`AccountId`),
CONSTRAINT `FK_AccountAddress_AddressCode` FOREIGN KEY (`AddressCodeId`) REFERENCES `addresscode` (`AddressCodeId`)
) ENGINE=InnoDB AUTO_INCREMENT=2496882 DEFAULT CHARSET=utf8 COMMENT='The Account Address Table'
1 row in set (0.00 sec)
mysql> show create table dolcur.accountphone\G
*************************** 1. row ***************************
Table: accountphone
Create Table: CREATE TABLE `accountphone` (
`AccountPhoneId` int(11) NOT NULL AUTO_INCREMENT,
`AccountId` int(11) DEFAULT NULL,
`Username` varchar(200) DEFAULT NULL,
`PhoneNumberType` char(16) DEFAULT NULL,
`PhoneNumber` varchar(16) NOT NULL,
`PhoneNumberExtension` varchar(10) DEFAULT NULL,
`InternationalPrefix` varchar(3) DEFAULT NULL,
`FormatMask` varchar(20) DEFAULT NULL,
`Active` char(1) NOT NULL,
`Preferred` char(1) NOT NULL,
`DateCreated` datetime NOT NULL,
`DateModified` datetime NOT NULL,
`ModifiedBy` int(11) DEFAULT NULL,
PRIMARY KEY (`AccountPhoneId`),
UNIQUE KEY `AccountId` (`AccountId`,`PhoneNumberType`),
KEY `FK4A006B612E64EFFE` (`AccountId`),
CONSTRAINT `
_FK4A006B612E64EFFE
` FOREIGN KEY (`AccountId`) REFERENCES `account` (`AccountId`)
) ENGINE=InnoDB AUTO_INCREMENT=954669 DEFAULT CHARSET=utf8 COMMENT='Customer Phone Table'
1 row in set (0.00 sec)
查看mysql.table_constraints中这两张表的外键约束情况：
外键被重建了，而且rename了一个名字：
mysql>
select * from table_constraints where table_name='accountaddress'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME: PRIMARY
TABLE_SCHEMA: dolcur
TABLE_NAME: accountaddress
CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME: AccountId
TABLE_SCHEMA: dolcur
TABLE_NAME: accountaddress
CONSTRAINT_TYPE: UNIQUE
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME:
_FKAAA919272E64EFFE
TABLE_SCHEMA: dolcur
TABLE_NAME: accountaddress
CONSTRAINT_TYPE: FOREIGN KEY
*************************** 4. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME: FK_AccountAddress_AddressCode
TABLE_SCHEMA: dolcur
TABLE_NAME: accountaddress
CONSTRAINT_TYPE: FOREIGN KEY
4 rows in set (0.01 sec)
mysql> select * from table_constraints where table_name='accountphone'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME: PRIMARY
TABLE_SCHEMA: dolcur
TABLE_NAME: accountphone
CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME: AccountId
TABLE_SCHEMA: dolcur
TABLE_NAME: accountphone
CONSTRAINT_TYPE: UNIQUE
*************************** 3. row ***************************
CONSTRAINT_CATALOG: def
CONSTRAINT_SCHEMA: dolcur
CONSTRAINT_NAME:
_FK4A006B612E64EFFE
TABLE_SCHEMA: dolcur
TABLE_NAME: accountphone
CONSTRAINT_TYPE: FOREIGN KEY
3 rows in set (0.00 sec)
发现外键约束没有问题了。
仔细看看表中，有个字段：
| RowVersion | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
可能对行记录做操作后，这个值会发生改变，因此需要检查一下：
mysql> select count(distinct RowVersion) from account;
+----------------------------+
| count(distinct RowVersion) |
+----------------------------+
| 722685 |
+----------------------------+
mysql> select count(distinct RowVersion) from _account_old;
+----------------------------+
| count(distinct RowVersion) |
+----------------------------+
| 722374 |
+----------------------------+
1 row in set (10.78 sec)
发现数值差不多，而没有发生很离谱的变化，然后自己再查几行记录看看，发现没有问题了。
接下来检查153和154两个备库：
153：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.6.150
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000144
Read_Master_Log_Pos: 87638389
Relay_Log_File: relay-log.000337
Relay_Log_Pos: 87638536
Relay_Master_Log_File: master-bin.000144
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dolcms,dolcur,dolgame
Replicate_Ignore_DB: information_schema,performance_schema,mysql,test,dolgam_cms,dolactivity
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 87638389
Relay_Log_Space: 87638448
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql> use dolcur;
Database changed
mysql> show tables;
+---------------------------------+
| Tables_in_dolcur |
+---------------------------------+
| _account_old |
| account |
| account2 |
| accountaddress |
| accountphone |
| ...................... |
| ............................ |
| addresstype |
| test |
| test_account |
+---------------------------------+
97 rows in set (0.00 sec)
154：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.6.150
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000144
Read_Master_Log_Pos: 87638389
Relay_Log_File: relay-log.000337
Relay_Log_Pos: 87638536
Relay_Master_Log_File: master-bin.000144
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: dolcms,dolcur,dolgame
Replicate_Ignore_DB: information_schema,performance_schema,mysql,test,dolgam_cms,dolactivity
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 87638389
Relay_Log_Space: 87638730
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
mysql> show tables;
+---------------------------------+
| Tables_in_dolcur |
+---------------------------------+
| _account_old |
| account |
| account2 |
| accountaddress |
| accountphone |
| ...................... |
| ............................ |
| addresstype |
| test |
| test_account |
+---------------------------------+
97 rows in set (0.00 sec)
至此，发现基本没有问题了。然后自己上应用系统上测试一下，发现应用没有问题。
OK，大功告成。
本文转自ITPUB博客84223932的博客，原文链接：MySQL使用pt-online-change-schema实现在线加字段，如需转载请自行联系原博主。
