Percona-Tookit工具包之pt-online-schema-change

2018-06-22 00:49:58来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 
Preface
 
    As we all know,it's really a troublesome thing to DBA in scenario of changing table structure online.Since MySQL 5.6,it support online ddl operations.On the other hand,we also use third party tool such as gh-ost.Let's learn another famous tool of Percona-Toolkit,that is pt-online-schema-change.
 
Introduce
 
    pt-osc is a tool which is commonly used in Percona-Toolkit suits which .It bases on triggers,by creating temporary middle table to dipose the table structure changing issue.MySQL 8.0.13 will support a new feature of instant adding columns(but only add,not change).Therefore,ps-osc still has its value in the "online ddl change" field.
 
Procedure
 
  1 ###Commonly used parameter introduce.###
  2 connect parameter:
  3 -h hostname -P port -u username -p password -S socket 
  4 
  5 output parameter:
  6 --print -- Show whole procedure of ddl operation on the screen.
  7 
  8 main parameter:
  9 --alter -- Specify the ddl statement using quotation mark behind.
 10 
 11 alternative parameter:
 12 --dry-run -- Only operate the new table instead of original one.
 13 --execute -- Directly do all opertion both new and original table.
 14 
 15 other parameter:
 16 --no-check-alter -- Don't parse your ddl statment which follows behind.
 17 --ask-pass -- You cannot use -p without a string to input password,so you need this parameter.
 18 
 19 ###Create test table.###
 20 (root@localhost mysql3306.sock)[zlm]09:38:11>create table test_ddl(
 21     -> id int unsigned auto_increment,
 22     -> name varchar(20) not null default '',
 23     -> tel tinyint unsigned not null default 0,
 24     -> primary key(id)
 25     -> ) engine=innodb charset=utf8mb4;
 26 Query OK, 0 rows affected (0.01 sec)
 27 
 28 ###Insert records into table.###
 29 (root@localhost mysql3306.sock)[zlm]09:39:28>insert into test_ddl(name,tel) values('zlm',13866668888);
 30 ERROR 1264 (22003): Out of range value for column 'tel' at row 1
 31 (root@localhost mysql3306.sock)[zlm]09:41:07>insert into test_ddl(name,tel) values('zlm',256);
 32 ERROR 1264 (22003): Out of range value for column 'tel' at row 1
 33 (root@localhost mysql3306.sock)[zlm]09:41:18>insert into test_ddl(name,tel) values('zlm',255); -- Scope of unsigned tinyint is 0~255.
 34 Query OK, 1 row affected (0.00 sec)
 35 
 36 (root@localhost mysql3306.sock)[zlm]09:41:22>select * from test_ddl;
 37 +----+------+-----+
 38 | id | name | tel |
 39 +----+------+-----+
 40 | 11 | zlm  | 255 |
 41 +----+------+-----+
 42 1 row in set (0.00 sec)
 43 
 44 ###Change column "tel" from tinyint to bigint.###
 45 [root@zlm2 09:52:25 ~]
 46 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl
 47 Enter MySQL password: 
 48 
 49 ###Check slave information.###
 50 No slaves found.  See --recursion-method if host zlm2 has slaves.
 51 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
 52 
 53 ###Summary of operations.###
 54 Operation, tries, wait:
 55   analyze_table, 10, 1
 56   copy_rows, 10, 0.25
 57   create_triggers, 10, 1
 58   drop_triggers, 10, 1
 59   swap_tables, 10, 1
 60   update_foreign_keys, 10, 1
 61 Altering `zlm`.`test_ddl`...
 62 
 63 ###Create new table.###
 64 Creating new table...
 65 CREATE TABLE `zlm`.`_test_ddl_new` (
 66   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 67   `name` varchar(20) NOT NULL DEFAULT '',
 68   `tel` tinyint(3) unsigned NOT NULL DEFAULT '0',
 69   PRIMARY KEY (`id`)
 70 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4
 71 Created new table zlm._test_ddl_new OK.
 72 
 73 ###Alter column in new table.###
 74 Altering new table...
 75 ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default 0
 76 Altered `zlm`.`_test_ddl_new` OK. -- If you specify "dry-run",it will end here.
 77 
 78 ###Create Triggers.###  
 79 2018-06-20T10:38:43 Creating triggers...
 80 2018-06-20T10:38:43 Created triggers OK.
 81 
 82 ###Copy rows into new table.###
 83 2018-06-20T10:38:43 Copying approximately 1 rows...
 84 INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/
 85 2018-06-20T10:38:43 Copied rows OK.
 86 
 87 ###Swap oraiginal table with new table.###
 88 2018-06-20T10:38:43 Analyzing new table...
 89 2018-06-20T10:38:43 Swapping tables...
 90 RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl`
 91 2018-06-20T10:38:43 Swapped original and new tables OK.
 92 
 93 ###Drop original table.###
 94 2018-06-20T10:38:43 Dropping old table...
 95 DROP TABLE IF EXISTS `zlm`.`_test_ddl_old`
 96 2018-06-20T10:38:43 Dropped old table `zlm`.`_test_ddl_old` OK.
 97 
 98 ###Drop triggers.###
 99 2018-06-20T10:38:43 Dropping triggers...
100 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del`
101 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd`
102 DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins`
103 2018-06-20T10:38:43 Dropped triggers OK.
104 Successfully altered `zlm`.`test_ddl`.
105 
106 ###Check table ddl.###
107 (root@localhost mysql3306.sock)[zlm]09:58:32>show create table test_ddl;
108 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
109 | Table    | Create Table                                                                                                                                                                                                                        |
110 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
111 | test_ddl | CREATE TABLE `test_ddl` (
112   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
113   `name` varchar(20) NOT NULL DEFAULT '',
114   `tel` bigint(20) DEFAULT '0', -- The column "tel" has be changed into bigint type now.
115   PRIMARY KEY (`id`)
116 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 |
117 +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
118 1 row in set (0.01 sec)
119 
120 ###Check general log file for detail.###
121 [root@zlm2 10:38:54 /data/mysql/mysql3306/data]
122 #cat zlm2.log
123 
124 mysqld, Version: 5.7.21-log (MySQL Community Server (GPL)). started with:
125 Tcp port: 3306  Unix socket: /tmp/mysql3306.sock
126 Time                 Id Command    Argument
127 2018-06-20T08:38:37.476403Z       16 Query    show global variables like '%general_log%'
128 2018-06-20T08:38:43.828985Z       19 Connect    root@localhost on zlm using TCP/IP
129 2018-06-20T08:38:43.829297Z       19 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
130 2018-06-20T08:38:43.832850Z       19 Query    SET SESSION innodb_lock_wait_timeout=1
131 2018-06-20T08:38:43.833041Z       19 Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'
132 2018-06-20T08:38:43.836907Z       19 Query    SET SESSION lock_wait_timeout=60
133 2018-06-20T08:38:43.837752Z       19 Query    SHOW VARIABLES LIKE 'wait\_timeout'
134 2018-06-20T08:38:43.842090Z       19 Query    SET SESSION wait_timeout=10000
135 2018-06-20T08:38:43.842222Z       19 Query    SELECT @@SQL_MODE
136 2018-06-20T08:38:43.842350Z       19 Query    SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
137 2018-06-20T08:38:43.842453Z       19 Query    SELECT @@server_id /*!50038 , @@hostname*/
138 2018-06-20T08:38:43.843311Z       20 Connect    root@localhost on zlm using TCP/IP
139 2018-06-20T08:38:43.843620Z       20 Query    SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
140 2018-06-20T08:38:43.847069Z       20 Query    SET SESSION innodb_lock_wait_timeout=1
141 2018-06-20T08:38:43.847442Z       20 Query    SHOW VARIABLES LIKE 'lock\_wait_timeout'
142 2018-06-20T08:38:43.851645Z       20 Query    SET SESSION lock_wait_timeout=60
143 2018-06-20T08:38:43.851904Z       20 Query    SHOW VARIABLES LIKE 'wait\_timeout'
144 2018-06-20T08:38:43.853942Z       20 Query    SET SESSION wait_timeout=10000
145 2018-06-20T08:38:43.854029Z       20 Query    SELECT @@SQL_MODE
146 2018-06-20T08:38:43.854055Z       20 Query    SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
147 2018-06-20T08:38:43.854152Z       20 Query    SELECT @@server_id /*!50038 , @@hostname*/
148 2018-06-20T08:38:43.854356Z       19 Query    SHOW VARIABLES LIKE 'wsrep_on'
149 2018-06-20T08:38:43.857148Z       19 Query    SHOW VARIABLES LIKE 'version%'
150 2018-06-20T08:38:43.860325Z       19 Query    SHOW ENGINES
151 2018-06-20T08:38:43.861002Z       19 Query    SHOW VARIABLES LIKE 'innodb_version'
152 2018-06-20T08:38:43.864957Z       19 Query    SHOW VARIABLES LIKE 'innodb_stats_persistent'
153 2018-06-20T08:38:43.867873Z       19 Query    SELECT @@SERVER_ID
154 2018-06-20T08:38:43.868114Z       19 Query    SHOW GRANTS FOR CURRENT_USER()
155 2018-06-20T08:38:43.868774Z       19 Query    SHOW FULL PROCESSLIST
156 2018-06-20T08:38:43.869245Z       19 Query    SHOW SLAVE HOSTS
157 2018-06-20T08:38:43.869849Z       19 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
158 2018-06-20T08:38:43.874152Z       19 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
159 2018-06-20T08:38:43.877509Z       19 Query    SELECT CONCAT(@@hostname, @@port)
160 2018-06-20T08:38:43.877973Z       19 Query    SHOW TABLES FROM `zlm` LIKE 'test\_ddl'
161 2018-06-20T08:38:43.878229Z       19 Query    SELECT VERSION()
162 2018-06-20T08:38:43.879011Z       19 Query    SHOW TRIGGERS FROM `zlm` LIKE 'test\_ddl'
163 2018-06-20T08:38:43.879506Z       19 Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
164 2018-06-20T08:38:43.879602Z       19 Query    USE `zlm`
165 2018-06-20T08:38:43.879706Z       19 Query    SHOW CREATE TABLE `zlm`.`test_ddl`
166 2018-06-20T08:38:43.880123Z       19 Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
167 2018-06-20T08:38:43.880979Z       19 Query    EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1
168 2018-06-20T08:38:43.881846Z       19 Query    SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE referenced_table_schema='zlm' AND referenced_table_name='test_ddl'
169 2018-06-20T08:38:43.893011Z       19 Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
170 2018-06-20T08:38:43.893622Z       19 Query    USE `zlm`
171 2018-06-20T08:38:43.893798Z       19 Query    SHOW CREATE TABLE `zlm`.`test_ddl`
172 2018-06-20T08:38:43.893954Z       19 Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
173 2018-06-20T08:38:43.894730Z       19 Query    CREATE TABLE `zlm`.`_test_ddl_new` (
174   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
175   `name` varchar(20) NOT NULL DEFAULT '',
176   `tel` int(11) DEFAULT '0',
177   PRIMARY KEY (`id`)
178 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4
179 2018-06-20T08:38:43.914358Z       19 Query    ALTER TABLE `zlm`.`_test_ddl_new` modify tel bigint default 0
180 2018-06-20T08:38:43.934771Z       19 Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
181 2018-06-20T08:38:43.934930Z       19 Query    USE `zlm`
182 2018-06-20T08:38:43.935044Z       19 Query    SHOW CREATE TABLE `zlm`.`_test_ddl_new`
183 2018-06-20T08:38:43.936057Z       19 Query    /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
184 2018-06-20T08:38:43.936926Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'DELETE'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
185 2018-06-20T08:38:43.937687Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'UPDATE'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
186 2018-06-20T08:38:43.939328Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'INSERT'    AND ACTION_TIMING = 'AFTER'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
187 2018-06-20T08:38:43.940991Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'DELETE'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
188 2018-06-20T08:38:43.942390Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'UPDATE'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
189 2018-06-20T08:38:43.943844Z       19 Query    SELECT TRIGGER_SCHEMA, TRIGGER_NAME, DEFINER, ACTION_STATEMENT, SQL_MODE,        CHARACTER_SET_CLIENT, COLLATION_CONNECTION, EVENT_MANIPULATION, ACTION_TIMING   FROM INFORMATION_SCHEMA.TRIGGERS  WHERE EVENT_MANIPULATION = 'INSERT'    AND ACTION_TIMING = 'BEFORE'    AND TRIGGER_SCHEMA = 'zlm'    AND EVENT_OBJECT_TABLE = 'test_ddl'
190 2018-06-20T08:38:43.944624Z       19 Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_del` AFTER DELETE ON `zlm`.`test_ddl` FOR EACH ROW DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE `zlm`.`_test_ddl_new`.`id` <=> OLD.`id`
191 2018-06-20T08:38:43.949413Z       19 Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_upd` AFTER UPDATE ON `zlm`.`test_ddl` FOR EACH ROW BEGIN DELETE IGNORE FROM `zlm`.`_test_ddl_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `zlm`.`_test_ddl_new`.`id` <=> OLD.`id`;REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`);END
192 2018-06-20T08:38:43.954329Z       19 Query    CREATE TRIGGER `pt_osc_zlm_test_ddl_ins` AFTER INSERT ON `zlm`.`test_ddl` FOR EACH ROW REPLACE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) VALUES (NEW.`id`, NEW.`name`, NEW.`tel`)
193 2018-06-20T08:38:43.959241Z       19 Query    EXPLAIN SELECT * FROM `zlm`.`test_ddl` WHERE 1=1
194 2018-06-20T08:38:43.961358Z       19 Query    EXPLAIN SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*explain pt-online-schema-change 4190 copy table*/
195 2018-06-20T08:38:43.962004Z       19 Query    INSERT LOW_PRIORITY IGNORE INTO `zlm`.`_test_ddl_new` (`id`, `name`, `tel`) SELECT `id`, `name`, `tel` FROM `zlm`.`test_ddl` LOCK IN SHARE MODE /*pt-online-schema-change 4190 copy table*/
196 2018-06-20T08:38:43.962769Z       19 Query    SHOW WARNINGS
197 2018-06-20T08:38:43.963561Z       19 Query    SHOW GLOBAL STATUS LIKE 'Threads_running'
198 2018-06-20T08:38:43.966643Z       19 Query    ANALYZE TABLE `zlm`.`_test_ddl_new` /* pt-online-schema-change */
199 2018-06-20T08:38:43.968221Z       19 Query    RENAME TABLE `zlm`.`test_ddl` TO `zlm`.`_test_ddl_old`, `zlm`.`_test_ddl_new` TO `zlm`.`test_ddl`
200 2018-06-20T08:38:43.983005Z       19 Query    DROP TABLE IF EXISTS `zlm`.`_test_ddl_old`
201 2018-06-20T08:38:43.990393Z       19 Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_del`
202 2018-06-20T08:38:43.991005Z       19 Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_upd`
203 2018-06-20T08:38:43.991346Z       19 Query    DROP TRIGGER IF EXISTS `zlm`.`pt_osc_zlm_test_ddl_ins`
204 2018-06-20T08:38:43.991966Z       19 Query    SHOW TABLES FROM `zlm` LIKE '\_test\_ddl\_new'
205 2018-06-20T08:38:43.999016Z       20 Quit    
206 2018-06-20T08:38:44.000302Z       19 Quit

 

Supplement

 

 1 ###Create a test table "test_ddl_no_pk" without primary key.###
 2 (root@localhost mysql3306.sock)[zlm]04:17:53>create table test_ddl_no_pk(
 3     -> id int unsigned,
 4     -> name varchar(20) not null default '',
 5     -> tel tinyint unsigned not null default 0
 6     -> ) engine=innodb charset=utf8mb4;
 7 Query OK, 0 rows affected (0.12 sec)
 8 
 9 (root@localhost mysql3306.sock)[zlm]04:18:11>insert into test_ddl_no_pk(id,name,tel) values(1,'zlm','110'),(2,'aaron8219','119');
10 Query OK, 2 rows affected (0.00 sec)
11 Records: 2  Duplicates: 0  Warnings: 0
12 
13 ###Insert two records into above table.###
14 (root@localhost mysql3306.sock)[zlm]04:18:26>select * from test_ddl_no_pk;
15 +------+-----------+-----+
16 | id   | name      | tel |
17 +------+-----------+-----+
18 |    1 | zlm       | 110 |
19 |    2 | aaron8219 | 119 |
20 +------+-----------+-----+
21 2 rows in set (0.00 sec)
22 
23 ###Execute pt-osc again.###
24 [root@zlm2 04:14:14 ~]
25 #pt-online-schema-change -hzlm2 -P3306 -uroot --ask-pass --alter "modify tel bigint default 0" --print --execute --no-check-alter D=zlm,t=test_ddl_no_pk
26 Enter MySQL password: 
27 No slaves found.  See --recursion-method if host zlm2 has slaves.
28 Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
29 Operation, tries, wait:
30   analyze_table, 10, 1
31   copy_rows, 10, 0.25
32   create_triggers, 10, 1
33   drop_triggers, 10, 1
34   swap_tables, 10, 1
35   update_foreign_keys, 10, 1
36 Altering `zlm`.`test_ddl_no_pk`...
37 Creating new table...
38 CREATE TABLE `zlm`.`_test_ddl_no_pk_new` (
39   `id` int(10) unsigned DEFAULT NULL,
40   `name` varchar(20) NOT NULL DEFAULT '',
41   `tel` tinyint(3) unsigned NOT NULL DEFAULT '0'
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
43 Created new table zlm._test_ddl_no_pk_new OK.
44 Altering new table...
45 ALTER TABLE `zlm`.`_test_ddl_no_pk_new` modify tel bigint default 0
46 Altered `zlm`.`_test_ddl_no_pk_new` OK.
47 2018-06-21T04:19:42 Dropping new table...
48 DROP TABLE IF EXISTS `zlm`.`_test_ddl_no_pk_new`; -- There're no create triggers,copy rows and swap table operations any more but directly drop new table operation.
49 2018-06-21T04:19:42 Dropped new table OK.
50 `zlm`.`test_ddl_no_pk` was not altered.
51 The new table `zlm`.`_test_ddl_no_pk_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger. -- Indicate the reason why it stop working.

 

Summary
  • pt-ocs depends on tirggers,there mustn't be triggers on original table.
  • Primary key or unique key is indispensable when use it.Otherwise,it won't work normally.
  • Although pt-ocs won't block online transactions but still recommend do online ddl operations in off-peak period.

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:Percona-Tookit工具包之pt-table-sync

下一篇:数据库之函数