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