在线更改MYSQL表结构 pt-online-schema-change

背景：由于目前生产环境中大表比较多，这里选择了一个6000M的小表做一次在线测试。

环境：oot@storm-master-01:/root#uname -a
Linux storm-master-01 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

mysql版本 ：

root@storm-master-01:/root#mysql -V
mysql  Ver 14.14 Distrib 5.7.17-11, for Linux (x86_64) using  6.2
root@storm-master-01:/root#

表引擎：Innodb

一、pt-online-schema-change介绍

      percona 公司提供的一款在线更新表的工具，更新过程不会锁表，也就是说操作alter的过程不会阻塞写和读取。即使如此，建议大家操作前还是先做好表备份。（备份工具推荐mydumper，这里不细说）

参数说明：
pt-online-schema-change [OPTIONS] DSN
options 可以自行查看 help，DNS 为你要操作的数据库和表。这里有两个参数需要介绍一下：
–dry-run
这个参数不建立触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
–execute
这个参数的作用和前面工作原理的介绍的一样，会建立触发器，来保证最新变更的数据会影响至新表。注意：如果不加这个参数，这个工具会在执行一些检查后退出。

注：操作的表必须有主键；否则报错：Cannot chunk the original table `houyi`.`ga`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5353.

 

二、pt-online-schema-change安装

1. wget percona.com/get/percona-toolkit.tar.gz 
2. https://www.percona.com/downloads/percona-toolkit/
   
3.  
4. tar -zxvf percona-toolkit-2.2.6.tar.gz  

1. perl Makefile.PL  
2. make  
3. make test  
4. make install

添加一列

root@storm-master-01:/root#pt-online-schema-change --alter "add column c1 int" --execute D=collection,t=orgs -h127.0.0.1 -uroot -proot -P33099  

注:pt-online-schema-change --alter "add column c1 int" --execute D=collection数据库,t=orgs表名 -h127.0.0.1 -uroot -proot -P33099

No slaves found.  See --recursion-method if host storm-master-01 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /root/perl5/bin/pt-online-schema-change line 6896.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /root/perl5/bin/pt-online-schema-change line 6896.
# A software update is available:
#   * The current version for Percona::Toolkit is 3.0.5
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `collection`.`orgs`...
Creating new table...
Created new table collection._orgs_new OK.
Altering new table...
Altered `collection`.`_orgs_new` OK.
2018-01-03T14:38:25 Creating triggers...
2018-01-03T14:38:25 Created triggers OK.
2018-01-03T14:38:25 Copying approximately 59 rows...
2018-01-03T14:38:25 Copied rows OK.
2018-01-03T14:38:25 Analyzing new table...
2018-01-03T14:38:25 Swapping tables...
2018-01-03T14:38:25 Swapped original and new tables OK.
2018-01-03T14:38:25 Dropping old table...
2018-01-03T14:38:25 Dropped old table `collection`.`_orgs_old` OK.
2018-01-03T14:38:25 Dropping triggers...
2018-01-03T14:38:25 Dropped triggers OK.
Successfully altered `collection`.`orgs`.
root@storm-master-01:/root#

好了，安装完成之后，就可以利用该工具进行在线重定义了。主要用到两个参数：

1. --dry-run  
2. Create and alter the new table, but do not create triggers, copy data, or replace the original table.  
3.   
4. --execute  
5. Indicate that you have read the documentation and want to alter the table. You must specify this option to alter the table. If you do not, then the tool will only perform some safety checks and exit. This helps ensure that you have read the documentation and understand how to use this tool. If you have not read the documentation, then do not specify this option.

数据库最昂贵的操作莫过于DDL操作，因为MySQL在修改表期间会阻塞任何读写操作的，基本上你的业务出于瘫痪状态了。对于庞大的可能历时好几个小时才完成，简直就是个恶梦，没法容忍的操作。

Percona开发了一系列工具Percona Toolkit包，其中有一工具pt-online-schema-change可以在线执行DDL操作，不会阻塞读写操作从而影响业务程序。当然啦，MySQL 5.6也增强了一些在线DDL功能。下面主要是说pt-online-schema-change在线更改表结构。

pt-online-schema-change原理

1、如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。

2、创建一个新的表，表结构为修改后的数据表，用于从源数据表向新表中导入数据。

3、创建触发器，用于记录从拷贝数据开始之后，对源数据表继续进行数据修改的操作记录下来，用于数据拷贝结束后，执行这些操作，保证数据不会丢失。

4、拷贝数据，从源数据表中拷贝数据到新表中。

5、修改外键相关的子表，根据修改后的数据，修改外键关联的子表。

6、rename源数据表为old表，把新表rename为源表名，并将old表删除。

7、删除触发器。

使用方法

[sql]  view plain  copy

 print ?

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

参数列表

[sql]  view plain  copy

 print ?

1. --user:  
2. -u，连接的用户名  
3.    
4. --password：  
5. -p，连接的密码  
6.    
7. --database：  
8. -D，连接的数据库  
9.    
10. --port  
11. -P，连接数据库的端口  
12.    
13. --host:  
14. -h，连接的主机地址  
15.    
16. --socket:  
17. -S，连接的套接字文件  
18.    
19. --ask-pass  
20. 隐式输入连接MySQL的密码  
21.    
22. --charset  
23. 指定修改的字符集  
24.    
25. --defaults-file  
26. -F，读取配置文件  
27.    
28. --alter：  
29. 结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：  
30.     不能用RENAME来重命名表。          
31.     列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。  
32.     如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。  
33.     删除外键(drop foreign key constrain_name)时，需要指定名称_constraint_name，而不是原始的constraint_name。  
34.     如：CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)，需要指定：--alter "DROP FOREIGN KEY _fk_foo"  
35.    
36. --alter-foreign-keys-method  
37. 如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。  
38. 该工具有两种方法,可以自动找到子表,并修改约束关系。  
39.     auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。  
40.     rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。  
41.     drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：  
42.     1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。  
43.     2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。  
44.     none： 类似"drop_swap"的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。  
45.    
46. --[no]check-alter  
47. 默认yes，语法解析。配合--dry-run 和 --print 一起运行，来检查是否有问题（change column，drop primary key）。  
48.    
49. --max-lag  
50. 默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠--check-interval指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。  
51.    
52. --check-slave-lag  
53. 指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。  
54.    
55. --recursion-method  
56. 默认是show processlist，发现从的方法，也可以是host，但需要在从上指定report_host，通过show slave hosts来找到，可以指定none来不检查Slave。  
57. METHOD       USES  
58. ===========  ==================  
59. processlist  SHOW PROCESSLIST  
60. hosts        SHOW SLAVE HOSTS  
61. dsn=DSN      DSNs from a table  
62. none         Do not find slaves  
63. 指定none则表示不在乎从的延迟。  
64. --check-interval   
65. 默认是1。--max-lag检查的睡眠时间。   
66.    
67. --[no]check-plan   
68. 默认yes。检查查询执行计划的安全性。  
69.    
70. --[no]check-replication-filters   
71. 默认yes。如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。   
72.    
73. --[no]swap-tables   
74. 默认yes。交换原始表和新表，除非你禁止--[no]drop-old-table。   
75.    
76. --[no]drop-triggers   
77. 默认yes，删除原表上的触发器。 --no-drop-triggers 会强制开启 --no-drop-old-table 即：不删除触发器就会强制不删除原表。   
78.    
79. --new-table-name   
80. 复制创建新表的名称，默认%T_new。   
81.    
82. --[no]drop-new-table   
83. 默认yes。删除新表，如果复制组织表失败。   
84.    
85. --[no]drop-old-table   
86. 默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。   
87.    
88. --max-load   
89. 默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。   
90.    
91. --critical-load   
92. 默认为Threads_running=50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。   
93.    
94. --default-engine   
95. 默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。   
96.    
97. --set-vars   
98. 设置MySQL变量，多个用逗号分割。默认该工具设置的是： wait_timeout=10000 innodb_lock_wait_timeout=1 lock_wait_timeout=60   
99.    
100. --chunk-size-limit  
101. 当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0，一个没有主键或唯一索引的表,块大小就是不确定的。  
102.    
103. --chunk-time  
104. 在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化，该参数设置为0,或者指定chunk-size,都可以禁止动态调整。  
105.    
106. --chunk-size  
107. 指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配，如果明确指定这个选项,那么每个块就会指定行数的大小.   
108.    
109. --[no]check-plan  
110. 默认yes。为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据，这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的。   
111.    
112. --statistics  
113. 打印出内部事件的数目，可以看到复制数据插入的数目。  
114.    
115. --dry-run  
116. 创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。  
117.    
118. --execute  
119. 确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。  
120.    
121. --print  
122. 打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。  
123.    
124. --progress  
125. 复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。  
126.    
127. --quiet  
128. -q，不把信息标准输出。  

测试

1、当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。

2、如果对外键表操作时，四种外键操作类型需要根据表的数据量和可靠程度，进行选择。处于可靠性的原因，尽量使用rebuild_constraints类型，如果没有可靠性要求，可以使用auto类型。
3、由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。

使用该工具的前提是处理的表需要有主键或则唯一索引。当处理有外键的表时，需要加--alter-foreign-keys-method参数，值可以根据情况设置。当是主从环境，不在乎从的延迟，则需要加--recursion-method=none参数。当需要尽可能的对服务产生小的影响，则需要加上--max-load参数。

添加字段

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD COLUMN content text" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute  

删除字段

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "DROP COLUMN content " D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute  

修改字段

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=aaa,t=tmp_test --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute  

字段改名

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "CHANGE COLUMN age address varchar(30)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execut  

增加索引

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "ADD INDEX idx_address(address)" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute  

删除索引

[sql]  view plain  copy

 print ?

1. pt-online-schema-change --user=root --password=123456 --host=192.168.200.25  --alter "DROP INDEX idx_address" D=aaa,t=tmp_test --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute