Mysql在线修改删除⼤批量数据以及如何在线修改表结构
⼀、⼤量的数据修改最好做分批处理
假如需要对1000万条数据做删除/修改处理，那么可以分批处理，每次只删除/修改1000⾏记录，并且为了减少对主从复制从服务器的压⼒，可以在执⾏完成之后暂停⼏秒，以给主从同步数据有⼀个时间缓冲。
⽐如写⼀个存储过程，来对数据做分批删除/修改/暂停操作，避免主从复制延迟以及阻塞。
⼆、对⼤表的结构如何修改
在线修改⼤表的可能影响
1. 在线修改⼤表的表结构执⾏时间往往不可预估，⼀般时间较长
2. 由于修改表结构是表级锁，因此在修改表结构时，影响表写⼊操作
3. 如果长时间的修改表结构，中途修改失败，由于修改表结构是⼀个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写⼊
4. 修改⼤表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
5. 在线修改⼤表结构容易导致主从延时，从⽽影响业务读取
两种修改⽅法
1. 利⽤主从复制的架构，在从服务器修改，然后再进⾏主从切换，最后在⽼服务器修改。那么这样会有⼀定风险。
2. 在主服务器上建⽴新的表，新表结构就是要修改⽼表之后的结构，然后把⽼表数据导⼊新表，并且在⽼表建⽴⼀系列触发器，把⽼表的数据修改同步到新表，当⽼表和新表
的数据同步后再对⽼表建⽴⼀个排它锁，然后重新命名新表为⽼表的名字，最后删除后命名的⽼表。这样做的好处可以减少主从延迟，好在有⼯具可以帮我们实现过程。
pt-onlinee-schema-change⼯具
⼯具介绍：pt-online-schema-change
【好处】
降低主从延时的风险
可以限速、限资源，避免操作时MySQL负载过⾼
mysql下载完如何使用【需要注意的问题】
1. pt-online-schema-change 在线DDL⼯具，虽然说不会锁表，但是对性能还是有⼀定的影响，执⾏过程中对全表做⼀次select。这个过程会将buffer_cache中活跃数据全部交
换⼀遍，这就导致活跃数据的请求都要从磁盘获取，导致慢SQL增多，file_reads增⼤。所以对于⼤表应在业务低峰期执⾏该操作。
2. 执⾏ RENAME 时，你不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。当业务量较⼤
时，修改操作会等待没有数据修改后，执⾏最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，⾄少修改表上的数据操作较低时，执⾏较为妥当。如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进⾏倒退更名，将每件事物退回到最初状态。
3. 对表的慢查询操作：慢查询还未结束执⾏osc操作，会报错，超时错误，在创建触发器的时候退出。
4. 对于主从复制架构：考虑到主从的⼀致性，应该在主库上执⾏pt-online-schema-change操作。（ps：如果在误在从库上执⾏了pt-online-schema-change操作，未执⾏完成
不要取消，等到执⾏完成了，在修改成原来的状态。如果在误在从库上执⾏了pt-online-schema-change操作，未执⾏完成取消的话，删除有 pt-online-schema-change在从库上创建的临时表和触发器即可）
5. 对于触发器：因为整个过程是在线的，为了将改表过程中对原始表的更新同时更新到新表上，会创建相应的触发器，每当发⽣针对原始表的增删改操作，就会触发对新表的
相应的操作。所以原始表上不能有其他触发器，即如果原始表上存有触发器，OSC会罢⼯的。
6. 对于外键：外键使改表操作变得更加复杂，如果原始表上有外键的话，⾃动rename原始表和新表的操作就不能顺利进⾏，必须要在数据拷贝完成后将外键更新到新表上，
该⼯具有两种⽅法来⽀持这个操作，具体使⽤参数（--alter-foreign-keys-method）实现。
--alter-foreign-keys-method
外键改表前后必须持续的链接正确的表，当该⼯具rename原始表并⽤新表来取代原始表时，外键必须正确更新到新表上，并且原始表中的外键不再⽣效
外键会使该⼯具的操作变得复杂并引⼊额外风险。当有外键引⽤该表时，重命名原表和新表的原⼦操作将⽆法正常运⾏。在表结构修改完成后，该⼯具必须修改外键以指向新表。该⼯具⽀持两种⽅式实现该操作，具体可参考--alter-foreign-keys-method⽂档。外键也会造成⼀些副作⽤。最终的表将会有与原表相同的外键和索引(除⾮你在ALTER语句进⾏分别指定)，但对象的名称可能会被略微修改以避免MySQL和InnoDB中的对象名称冲突。
出于安全考虑，该⼯具并不会真的修改表，除⾮你指定--execute选项，⽽该选项默认并未启⽤。该⼯具⽀持各种各样的其它措施以防⽌⾮预期的负载或其它问题，包括⾃动检测从节点，连接到它们，并使⽤如下安全检查：
在⼤多情况下，除⾮表中有PRIMARY KEY或UNIQUE INDEX，该⼯具才会进⾏操作。详见--alter选项。
如果检测到有replication filter，该⼯具会拒绝操作。详见--[no]check-replication-filters选项。
该⼯具会停⽌数据拷贝操作，如果它观察到主从延迟⼤于--max-lag选项的值，默认为1s。
该⼯具会停⽌或放弃操作，如果它检测到对服务器造成太多负载。见--max-load和--critical-load选项。
该⼯具会设置innodb_lock_wait_timeout=1和(对于MySQL 5.5及更新的版本)lock_wait_timeout=60，
因此它会更容易成为锁竞争的受害者，并更少破坏其它事务。这些值可以通过指定--set-vars来修改。
该⼯具会拒绝修改表，如果外键约束有引⽤它，除⾮你指定了--alter-foreign-keys-method。
【原理】
1. 如果存在外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理。没有使⽤ --alter-foreign-keys-method=rebuild_constraints 指定特定的
值，该⼯具不予执⾏
2. 新建⼀张⼀模⼀样的表，表名⼀般是_new后缀
3. 然后在这个新表执⾏更改字段操作
4. 然后在原表上加三个触发器，DELETE/UPDATE/INSERT，将原表中要执⾏的语句也在新表中执⾏
5. 最后将原表的数据拷贝到新表中，然后替换掉原表
6. 最后删除触发器
【下载安装】
1.去官⽹下载对应的版本，官⽹下载地址:www.percona/downloads/percona-toolkit/LATEST/
wget www.percona/downloads/percona-toolkit/3.1/binary/redhat/7/x86_64/percona-toolkit-3.1-1.el7.x86_64.rpm
2.安装依赖
yum -y install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey
3.安装
rpm -ivh percona-toolkit-3.1-1.el7.x86_64.rpm
或者直接使⽤yum安装
使⽤yum会⾃动安装依赖包，⽐较简单
yum install -y percona-toolkit-3.0.12-1.el6.x86_64.rpm
4.验证
pt-online-schema-change --help
DNS选项常⽤的有：
--user= 连接mysql的⽤户名
--password= 连接mysql的密码
--host= 连接mysql的地址
P=3306连接mysql的端⼝号
D= 连接mysql的库名
t= 连接mysql的表名
--alter 修改表结构的语句
--execute 执⾏修改表结构
--charset=utf8 使⽤utf8编码，避免中⽂乱码
-
-no-version-check 不检查和更新percona toolkit版本，也不检查mysql版本等。
另外的选项使⽤
--dry-run  这个参数不建⽴触发器，不拷贝数据，也不会替换原表。只是创建和更改新表。
--execute  这个参数的作⽤和前⾯⼯作原理的介绍的⼀样，会建⽴触发器，来保证最新变更的数据会影响⾄新表。注意：如果不加这个参数，这个⼯具会在执⾏⼀些检查后退出。
--critical-load  每次chunk操作前后，会根据show global status统计指定的状态量的变化，默认是统计Thread_running。⽬的是为了安全，防⽌原始表上的触发器引起负载过⾼。这也是为了防⽌在线DDL对线上的影响。超过设置的阀值，就会终⽌操作--max-load 选项定义⼀个阀值，在每次chunk操作后，查看show global status状态值是否⾼于指定的阀值。该参数接受⼀个mysql status状态变量以及⼀个阀值，如果没有给定阀值，则定义⼀个阀值为为⾼于当前值的20%。注意这个参数不会像--crit --charset=utf8连接到MySQL后运⾏SET NAMES UTF8
--check-replication-filters 检查复制中是否设置了过滤条件，如果设置了，程序将退出
--nocheck-replication-filters 不检查复制中是否设置了过滤条件
--set-vars 设置mysql的变量值
--check-slave-lag 检查主从延迟
5.执⾏条件
操作的表必须有主键或则唯⼀索引，否则报如下错误。
该表不能定义触发器，否则报如下错误。
6.使⽤⼯具
默认会安装到/usr/bin⽬录下
案例：假如修改nickname字段的属性
pt-online-schema-change  --user=root --password=xxxxxx  --host=127.0.0.1  P=3306,D=loantest_xmbmc,t=admins_bak  --charset=utf8 --no-version-check --execute --alter "modify  column nickname varchar(255) not null default ''"
7.使⽤语法
添加字段
--alter="add column nike_name varchar(100) not null default ''"
删除字段
--alter="drop column nike_name"
修改字段
--alter="modify column id bigint(125)"
添加索引
--alter="add key index_name(id)"
删除索引
--alter="drop key index_name"
本⽂属于转载后⼆次修改，详情请参考其他博⽂

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。