mysql在线表结构设计 mysql 在线修改表结构

转载

架构设计师之光 2023-12-03 15:54:07

文章标签 mysql在线表结构设计 github 数据外键 文章分类 MySQL 数据库

注：在线DDL操作工具：1）pt-online-schema-change，2）gh-ost

wget https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm
gh-ost下载地址：https://github.com/github/gh-ost/releases/tag/v1.0.28
wget https://codeload.github.com/github/gh-ost/tar.gz/v1.0.28
到 https://github.com/github/gh-ost/releases直接下载编译好的binary包。

总所周知，数据库最昂贵的操作莫过于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、删除触发器。

安装percona toolkit

[sql] view plain copy

1. # wget https://www.percona.com/redir/downloads/percona-release/redhat/percona-release-0.1-3.noarch.rpm  
2. # rpm -Uvh percona-release-0.1-3.noarch.rpm   
3. # yum install percona-toolkit

使用方法

[sql] view plain copy

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

参数列表

[sql] view plain copy

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
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. ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。  
41.     drop_swap： 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：  
42.     1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。  
43.     2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。  
44. "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
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
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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

1. pt-online-schema-change --user=root --password=123456 --host=IP  --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

2）gh-ost

go源码安装

# 安装go依赖包
sudo apt-get install bison ed gawk gcc libc6-dev make

# 配置go环境变量，GOROOT为go源码目录，GOPATH为gh-ost这个工程的目录
vim ~/.bashrc
export GOROOT=/usr/local/go
export PATH=$PATH:$GOROOT/bin
export GOPATH=/usr/local/go/src/github.com/github/gh-ost

# 使环境变量生效
source ~/.bashrc

# 获取+解压go源码安装包，go下载地址：https://golang.org/dl/
wget https://storage.googleapis.com/golang/go1.5.src.tar.gz
tar -zxvf go1.5.src.tar.gz -C /usr/local/
# 此时是go的安装目录为/usr/local/go

# 验证go安装成功
go env
# 结果展示
GOARCH="amd64"
GOBIN=""
GOEXE=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOOS="linux"
GOPATH="/usr/local/gh-ost/go"
GORACE=""
GOROOT="/usr/local/go"
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"
GO15VENDOREXPERIMENT=""
CC="gcc"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0"
CXX="g++"
CGO_ENABLED="1"

gh-ost源码安装

# 在go安装目录下创建github.com/github/目录
mkdir -p /usr/local/go/src/github.com/github
# 别问我为什么要这样做，我这种go小白，只要安装出来就行，深入原因自己解决吧==

# 获取+解压gh-ost源码安装包，gh-ost下载地址：https://github.com/github/gh-ost/releases/tag/v1.0.28
wget https://codeload.github.com/github/gh-ost/tar.gz/v1.0.28
unzip gh-ost-1.0.28.zip -d /usr/local/go/src/github.com/github
cd /usr/local/go/src/github.com/github
mv gh-ost-1.0.28 gh-ost

# gh-ost源码安装
cd /usr/local/go/src/github.com/github/gh-ost
/bin/bash build.sh
# 结果展示
Building GNU/Linux binary
Binaries found in:
/tmp/gh-ost/gh-ost-binary-linux-20161201195143.tar.gz

tar -zxvf /tmp/gh-ost/gh-ost-binary-linux-20161201195143.tar.gz -C /usr/local
ln -s /usr/local/gh-ost /usr/bin/gh-ost

# 验证gh-ost安装成功
gh-ost -version
1.0.28
gh-ost --help
# 结果会输出一堆参数，gh-ost参数待以后详解

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。