1
2
注意：执行这个工具的时候 **必须做好备份** ，操作之前详细读一下官方文档[pt-online-schema-change](https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html "pt-online-schema-change")

1
2
在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。**如果表中已经定义了其它触发器，这个工具就不能工作了！**

1
2
pt-online-schema-change [OPTIONS] DSN

1
2
pt-online-schema-change --user=root --password=wang123 --host=localhost --lock-wait-time=120 --alter="ENGINE=InnoDB" D=test,t=tb1 --execute 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
      Altering `test`.`tb1`... 

# 创建新表，并变更
      Creating new table... 
      Created new table test._tb1_new OK. 

      Altering new table... 
      Altered `test`.`_tb1_new` OK. 

#创建触发器
      Creating triggers... 
      Created triggers OK. 

#copy数据到新表
      Copying approximately 995696 rows... 

      Copied rows OK.

#切换新表和老表
      Swapping tables... 
      Swapped original and new tables OK. 

#删除老表
      Dropping old table... 
      Dropped old table `test`.`_tb1_old` OK. 

#删除触发器
      Dropping triggers... 
      Dropped triggers OK. 

Successfully altered `test`.`tb1`. 

1
2
3
4
5
6
7
8
9
CREATE TABLE if not exists `dsns`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `parent_id` int(11) DEFAULT NULL,
    `dsn` varchar(255) NOT NULL,
    PRIMARY KEY(`id`)  );

INSERT INTO dsns(parent_id , dsn) VALUES (1, 'h=10.10.1.16,u=*,p=*,P=3306');
INSERT INTO dsns( parent_id , dsn) VALUES (1, 'h=10.10.1.17,u=*,p=*,P=3306');

1
2
3
DROP PRIMARY KEY
If –alter contain DROP PRIMARY KEY (case- and space-insensitive), a warning is printed and the tool exits unless –dry-run is specified. Altering the primary key can be dangerous, but the tool can handle it. The tool’s triggers, particularly the DELETE trigger, are most affected by altering the primary key because the tool prefers to use the primary key for its triggers. You should first run the tool with –dry-run and –print and verify that the triggers are correct.

1
2
3
而对于INSERT,UPDATE的触发器，依然是  **REPLACE INTO** 语法，因为它采用的是先插入，如果违反主键或唯一约束，则根据主键或意义约束删除这条数据，再执行插入。
    (**但是注意不能依赖于新表的主键递增，因为如果原表有update，新表就会先插入这一条，导致id与原表记录所在顺序不一样**）

1
2
$ pt-online-schema-change --user=user--password=userpwd --host=10.10.10.34  --alter "DROP PRIMARY KEY,add column pk int auto_increment primary key,add unique key uk_id_k(id,k)"  D=confluence,t=sbtest3--print --dry-run

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--alter contains 'DROP PRIMARY KEY'.  Dropping and altering the primary key can be dangerous,


 ** especially if the original table does not have other unique indexes. **  
 ==>注意 dry-run的输出

ALTER TABLE `confluence`.`_sbtest3_new` DRO PPRIMARY KEY, add column pk int auto_increment primary key, add unique key uk_id_k(id,k)

Altered `confluence`.`_sbtest3_new` OK.

Using original table index PRIMARY for the DELETE trigger instead of new table index PRIMARY because 
==>使用原表主键值判断

the new table index uses column pk which does not exist in the original table.

CREATE TRIGGER `pt_osc_confluence_sbtest3_del` AFTER DELETEON `confluence`.`sbtest3` FOR EACH ROW DELETE IGNORE FROM `confluence`.`_sbtest3_new`
WHERE `confluence`.`_sbtest3_new`.`id` = OLD.`id` AND `confluence`.`_sbtest3_new`.`k` = OLD.`k`

1
2
pt-online-schema-change --alter "add column addr varchar(200)" --print --charset utf8  --chunk-size=50000 --max-load Threads_running=100 --recurse=1 --alter-foreign-keys-method=none --force --execute --statistics --max-lag 3.000000 --noversion-check --recursion-method=processlist --progress percentage,1 D=osc,t=test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
......

Copying `osc`.`test`: 34% 02:05:40 remain

**Exiting on SIGHUP.**

Not dropping triggers because the tool was interrupted.  To drop the triggers, execute:

DROP TRIGGER IF EXISTS `osc`.`pt_osc_osc_test_del`

DROP TRIGGER IF EXISTS `osc`.` pt_osc_osc_test_upd`

DROP TRIGGER IF EXISTS `osc`.` pt_osc_osc_test_ins`

Not dropping the new table `osc`.`_test_new` because the tool was interrupted.  To drop the new table, execute:

DROP TABLE IF EXISTS `osc`.`_test_new`;