mysql之ptschemachange

　　条件：
　　1，表格必须带有主键或者唯一索引
　　1, 原理
　　1.1 检查表是否带有主键或者唯一索引，show create table test;
　　1.2 创建新表，create table _test_new;
　　1.3 变更新表，alter table _test_new;
　　1.4 在原表上创建 insert、update、delete 三种类型的触发器；
　　1.5 将旧表的数据拷贝到新表中，同时通过触发器将旧表中的操作映射到新表；
　　1.6 将新表替换原表，rename 表名，RENAME TABLE `testdb`.`test` TO `testdb`.`_test_old`, `testdb`.`_test_new` TO `testdb`.`test`
　　1.7 删除旧表(可以根据选项来控制是否删除旧表)
　　1.8 删除新表上的删除、更新、插入 触发器
　　2, 使用场景
　　2.1 亿级大表在线不锁表变更字段与索引
　　3，pt-online-schema-change安装
　　yum install perl-DBI
　　yum install perl-DBD-MySQL
　　yum install perl-Time-HiRes
　　yum install perl-IO-Socket-SSL
　　4，pt-online-schema-change使用
　　选项：--alter 修改的内容，不支持 rename表重命名。 --alter-foreign-keys-method ：auto 自动选择 rebuild_constraints 或者 drop_swap，优先选择rebuild_constraints --no-drop-old-table 确认后再手动删除 --ask-pass --execute 创建触发器，保证最新的变更数据及时更新到新表。注意:如果不加这个参数，这个工具在执行会再制造一些检查后退出。 --max-load --max-load=＂Threads_running=100,Threads_connected=500＂ --chunk-time 默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化 --chunk-size 禁止动态调整，如果指定则默认1000行 --max-lag=5 默认为 1s 每个 chunks 拷 完成后，会查看 check-slave-log 参 数所指定的从库的延迟信息，如果超过了max-log的阀值则暂停复制数据，直到复制延迟小于 max-log 的阀值。检查复制延迟信息依赖于 show slave status 返回中 的 seconds_behind_master 列的值 --check-interval=2 当出现复制延迟暂停复制数据后，按照 check-interval 指定时间周期来检查复制延迟，直至延迟时间低于 max-log 阀值然后进行拷 数据。 --check-slave-lag 指定主从复制延迟大于选项＂--max-lag＂指定的值之后暂停检查校验操作。默认情况下，工具会检查所有的从库，但该选项只作用于指定的从库(通过DSN连接方式)。 --recurse=i --recursion-method ;processlist host --no-check-replication-filters --dry-run: 这个参数不建立触发器，不拷 数据，也不会替换原表，只是创建和 更改新表 --print配合最佳 --set-vars 如设置sql_log_bin=0，一般不设置 --no-version-check 阿里云RDS必须添加此参数 D 数据库名 t 表名 --check_size:用于每次控制每次拷 数据的大小，默认 1000 --chunk_size_limit:默认值 4.0;当需要复制的块大于设置的 chunk_size 大小，就不复制 --critical-load:default thread_runing = 50，该参数在 copy 每个块后检查 show global status,如果负载过高则终止，如果不给出，则工具检查启动当前值来确定阀值。 --max-load:default threads_running=25 该参数用于拷 数据每个数据块的时候进行检查 show global status,如果任何状态变量高于阀值会暂停，如果不设置，该工具会在检测到的当前值上再加 20%来 确定阀值(方式 Threads_connected:100 或者 Thread_connected:110)该选项 主要 的是为了防止给服务器添加太多的负载，导致锁等待以及其他干扰行为。 --[no]drop-new-table 默认为 YES，如果复制失败后，删除新表。 --[no]drop-old-table 注意:执行该参数需指定—nodrop-new-table 复制的新表很大，新的数据都会缓存在bufferpool 内。 Mysql 在删除表的过程中 innodb 会将文件在 buffer pool 中对应的 面清 楚。如果这里设置 buffer pool 的值过大的话，或者 buffer pool 里面有很 多需要被 flush 的 面，那么此时要遍历扫描 面就会占用比较 的时间， 导致其他事物在用到 buffer pool 实例时就会阻塞，从而影响到整个数据库 的性能。造成了 MySQL 夯住。 那么此表的删除可以停放一周后进行删除 --charset=utf8	连接到MySQL后运行SET NAMES UTF8 --check-replication-filters	检查复制中是否设置了过滤条件，如果设置了，程序将退出 --no-check-replication-filters	不检查复制中是否设置了过滤条件 --set-vars	设置mysql的变量值 --chunk-size	默认值：1000，为每个复制的块选择的行数。 --[no]drop-old-table	默认值：是；重命名后删除原始表。成功重命名原始表以让新表取代它之后，如果没有错误，该工具将默认删除原始表。如果有任何错误，该工具会将原始表保留在原位。注意：如果异常中断，则需要删除原表上已创建的触发器与创建出的新表＂_XXXXX＂
　　示例：
　　1, 添加列pt-online-schema-change --alter ＂add column c1 int＂ h=localhost,u=root, P=3306,D=test,t=user --ask-pass --no-drop-old-table --execute
　　2, 删除列pt-online-schema-change --alter ＂drop column c1＂ h=localhost,u=root,P=3306,D=test,t=user --ask-pass --no-drop-old-table --execute
　　3, 添加索引pt-online-schema-change --alter ＂add index idx_ename(ename)＂ h=localhost,u=root,P=3306,D=test,t=test --ask-pass --no-drop-old-table --execute
　　4, 删除索引pt-online-schema-change --alter ＂drop index idx_ename＂ h=localhost,u=root,P=3306,D=test,t=test --ask-pass --no-drop-old-table --execute
　　5, 修改字段长度pt-online-schema-change --alter ＂modify column remark text ＂ h=localhost,u=root,P=3306,D=test,t=test --ask-pass --no-drop-old-table --execute
　　6,重建表pt-online-schema-change -hIP -uroot --no-version-check --no-drop-old-table --alter=＂engine=innodb＂ D=test,t=test --ask-pass --critical-load=＂Threads_running=400＂ --execute
　　7, 修改自增ID为bigintpt-online-schema-change -hIP -uroot --no-drop-old-table --alter=＂modify id bigint not null auto_increment＂ D=test,t=test --ask-pass --critical-load=＂Threads_running=400＂ --execute
　　8 添加字段pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂add comment varchar(50)＂ --charset=utf8 --execute 限制活跃连接超出25即停止pt-osc操作，--critical-load=Threads_running=25
　　9 修改字段pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂modify column comment varchar(50) character set utf8mb4＂ --execute
　　10 删除字段pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂drop column comment,drop column comment2＂ --execute
　　11 添加索引pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂add index idx_b(b)＂ --execute
　　12 删除索引pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂drop index idx_b＂ --execute
　　13 修改字段为null/not null
　　# 改为nullpt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂modify column c varchar(255) default null＂ --execute
　　# 改为 not nullpt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂modify column c varchar(255) not null＂ --execute
　　14 添加主键pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂add primary key(id)＂ --execute
　　15 删除主键pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂drop primary key＂ --execute
　　16 重建表pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂engine=InnoDB＂ --execute
　　17 保留变更的新旧表
　　# 保留旧表pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂add comment varchar(50)＂ --no-drop-old-table --execute
　　# 保留新表，pt-online-schema-change h=192.168.66.101,P=3306,D=ytest,t=data_million_small --user=root --ask-pass --alter ＂add comment2 varchar(50)＂ --no-drop-new-table --no-swap-tables --new-table-name=＂data_million_small_newt＂ --execute
　　优势：
　　1，降低主从延时的风险
　　2，可以限速、限资源，避免操作时MySQL负载过高
　　建议:
　　1，在业务低峰期做，将影响降到最低