Mysql在线修改/删除大批量数据以及如何在线修改表结构

一、大量的数据修改最好做分批处理

假如需要对1000万条数据做删除/修改处理，那么可以分批处理，每次只删除/修改1000行记录，并且为了减少对主从复制从服务器的压力，可以在执行完成之后暂停几秒，以给主从同步数据有一个时间缓冲。
比如写一个存储过程，来对数据做分批删除/修改/暂停操作，避免主从复制延迟以及阻塞。

二、对大表的结构如何修改
在线修改大表的可能影响

  1. 在线修改大表的表结构执行时间往往不可预估，一般时间较长
  2. 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
  3. 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入
  4. 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
  5. 在线修改大表结构容易导致主从延时，从而影响业务读取

两种修改方法

  1. 利用主从复制的架构，在从服务器修改，然后再进行主从切换，最后在老服务器修改。那么这样会有一定风险。
  2. 在主服务器上建立新的表，新表结构就是要修改老表之后的结构，然后把老表数据导入新表，并且在老表建立一系列触发器，把老表的数据修改同步到新表，当老表和新表的数据同步后再对老表建立一个排它锁，然后重新命名新表为老表的名字，最后删除后命名的老表。这样做的好处可以减少主从延迟，好在有工具可以帮我们实现过程。pt-onlinee-schema-change工具

工具介绍：pt-online-schema-change 

【好处】

  • 降低主从延时的风险 
  • 可以限速、限资源，避免操作时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.去官网下载对应的版本，官网下载地址:https://www.percona.com/downloads/percona-toolkit/LATEST/

wget https://www.percona.com/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对线上的影响。超过设置的阀值，就会终止操作，在线DDL就会中断。提示的异常如上报错信息。
--max-load 选项定义一个阀值，在每次chunk操作后，查看show global status状态值是否高于指定的阀值。该参数接受一个mysql status状态变量以及一个阀值，如果没有给定阀值，则定义一个阀值为为高于当前值的20%。注意这个参数不会像--critical-load终止操作，而只是暂停操作。当status值低于阀值时，则继续往下操作。是暂停还是终止操作这是--max-load和--critical-load的差别。
--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"

 

本文属于转载后二次修改，详情请参考其他博文

 

posted @ 2018-02-28 22:14  温柔的风  阅读(678)  评论(0)  编辑  收藏  举报