千万大表,超100G大表修改表结构在线更改MYSQL表结构--688IT编程网

千万⼤表，超100G⼤表修改表结构在线更改MYSQL表结构pt-online-

schema。。。

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

表引擎：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/get/

2. www.percona/downloads/percona-toolkit/

4. tar -zxvf percona-toolkit-2.2.

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

Created new table collection._orgs_new OK.

Altering

Altered `collection`.`_orgs_new` OK.

2018-01-03T14:38:25

2018-01-03T14:38:25 Created triggers OK.

2018-01-03T14:38:25 Copying approximately

2018-01-03T14:38:25 Copied rows OK.

2018-01-03T14:38:25 Analyzing

2018-01-03T14:38:25

2018-01-03T14:38:25 Swapped original and new tables OK.

2018-01-03T14:38:25 Dropping

2018-01-03T14:38:25 Dropped old table `collection`.`_orgs_old` OK.

2018-01-03T14:38:25

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.

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 saf

数据库最昂贵的操作莫过于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]

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

参数列表

[sql]

1. --user:

2. -u，连接的⽤户名

4. --password：

mysql需要安装documentation5. -p，连接的密码

7. --database：

8. -D，连接的数据库

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]