MySQL使用pt-online-change-schema實現線上加欄位
本文主要描述某企業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部落格 ” ，連結：http://blog.itpub.net/26355921/viewspace-774506/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL：count(*) count(欄位) 實現上區別MySql
- MySQL簡單實現多欄位模糊查詢MySql
- MySQL 5.7使用pt-online-schema-change對大表加欄位MySql
- MySQL線上新增欄位的幾種方案介紹MySql
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- MySQL8.0大表秒加欄位，是真的嗎？MySql
- redis對hash欄位加鎖Redis
- 巧用欄位對映實現指定欄位的搜尋
- 如何在MySQL中實現替換欄位部分內容MySql
- mysql 5.7 新特性中線上in-place 修改欄位的大小MySql
- SQL字元型欄位按數字型欄位排序實現方法SQL字元排序
- MySQL中JSON欄位的使用技巧MySqlJSON
- MySQL 中 JSON 欄位的使用技巧MySqlJSON
- MySQL 5.6使用pt-online-schema-change線上修改大表欄位長度MySql
- MySQL 欄位約束MySql
- mysql中文欄位排序MySql排序
- MySQL8.0.16秒加欄位(instant add column)功能測試MySql
- pl/sql dev建表加欄位時建的欄位名都帶了“”SQLdev
- Mybatis資料庫欄位加解密2-使用typeAlias實現MyBatis資料庫解密
- 【MySql】mysql 欄位個數的限制MySql
- MySQL-刪除欄位MySql
- MySQL 大欄位問題MySql
- mysql表最多欄位數MySql
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- MySQL多列欄位去重的案例實踐MySql
- MySQL欄位新增註釋，但不改變欄位的型別MySql型別
- 快排實現仿order by多欄位排序排序
- Oracle如何實現多個欄位去重Oracle
- mysql表操作(alter)/mysql欄位型別MySql型別
- MySQL欄位型別最全解析MySql型別
- MySQL-建立計算欄位MySql
- MySQL欄位的取值範圍MySql
- MySQL欄位型別小記MySql型別
- [MYSQL-10]計算欄位MySql
- mysql的text欄位長度MySql
- MySQL修改欄位預設值MySql
- MySQL 按照指定的欄位排序MySql排序
- SQL Server中根據某個欄位，ID欄位自動增長的實現SQLServer