mysql 大表在线DDL操作

发表评论
237 views

mysql 大表在线DDL操作

一.说明

  • 1、主从架构轮询修改

    需要注意：
a、主库会话级别的记录binglog的参数关闭
b、500\502错误异常捕捉
c、检查备库的second behind master是否有延迟
d、varchar有页分裂的情况，尽量减少varchar的长度

  • 2、在线工具online-schema-change需要注意：

    需要注意：
超过1000w行，速度会变慢（半小时左右）
作为DBA，我们也常常会碰到这样的需求：需要在不影响线上业务的情况下给表添加一个字段或索引。如果是一张只有几百或几千条记录的小表，这样的需求是非常容易解决的。但如果所管理的表数据量已经上亿、而且应用与数据库交互非常频繁，不允许停机窗口的出现，这样的需求又该如何满足？
大多数的alter table操作都会涉及lock-->copy to new table-->rename-->unlock的过程，锁表时间会很长,而且alter table 的process不可被kill,一旦执行就不可回退。
在MySQL5.5和之前版本，在运行的生产环境对大表（超过数百万纪录）执行Alter操作是一件很困难的事情。因为将重建表和锁表，影响用户者的使用。
从MySQL5.6开始，Online DDL特性被引进。他增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。
但是即使在MySQL5.6，仍然有一些Alter操作（增加/删除列，增加/删除主键,改变数据类型等）需要重建表。并不建议直接在线上使用alter table。如果线上有DDL的需求我们建议使用以下两种方式
1、主从架构轮询修改
2、使用在线修改工具online-schema-change
对于第一种方式，使用的前提是，你的数据库架构是一个集群，如果不是，也就无所谓的轮询修改。修改的原理就是利用主从服务，在应用无感知的情况下，得到停机窗口，进行修改。今天我们着重讲第二种方式，利用第三方工具实现在线大表的DDL操作。这种方式在alter操作更改表结构的时候不用锁定表，也就是说执行alter的时候不会阻塞写和读取操作。
工作原理：
创建一个和你要执行alter操作的表一样的空表结构，执行表结构修改，然后从原表中copy原始数据到表结构修改后的表，当数据copy完成以后就会将原表移走，用新表代替原表，默认动作是将原表drop掉。在copy数据的过程中，任何在原表的更新操作都会更新到新表，因为这个工具在会在原表上创建触发器，触发器会将在原表上更新的内容更新到新表。如果表中已经定义了触发器这个工具就不能工作了。
注意点：
1、操作的表必须有主键或唯一索引否则报错。
2、如果表有外键，除非使用 --alter-foreign-keys-method 指定特定的值，否则工具不予执行。
3、当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时，应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。
4、由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。
5、当是主从环境，不在乎从的延迟，则需要加--recursion-method=none参数。当需要尽可能的对服务产生小的影响，则需要加上--max-load参数。

二.安装工具

#安装依赖环境
##Install DBI
wget http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.625.tar.gz
tar -zxvf DBI-1.625.tar.gz
cd DBI-1.625
perl Makefile.PL
make
make install
##Install DBD::Mysql
wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.023.tar.gz
tar -zxvf DBD-mysql-4.023.tar.gz
cd DBD-mysql-4.023
perl Makefile.PL
make
make install

#安装percona-toolkit：
##Install percona-toolkitk
wget percona.com/get/percona-toolkit.tar.gz
tar -zxvf percona-toolkit-2.2.16.tar.gz
cd percona-toolkit-2.2.16
perl Makefile.PL
make
make install

三.工具使用

[root@bogon percona-toolkit-3.2.0]# time pt-online-schema-change -uroot  -S /tmp/mysql_3306.sock --alter='add key  idx_gift_get(gift_get)' --execute D=425_pfm,t=cms01_gift_key
Cannot connect to S=/tmp/mysql_3306.sock,h=192.168.28.102,u=root
No slaves found.  See --recursion-method if host localhost has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `425_pfm`.`cms01_gift_key`...
Creating new table...
Created new table 425_pfm._cms01_gift_key_new OK.
Altering new table...
Altered `425_pfm`.`_cms01_gift_key_new` OK.
2020-07-29T09:13:43 Creating triggers...
2020-07-29T09:13:43 Created triggers OK.
2020-07-29T09:13:43 Copying approximately 449374 rows...
2020-07-29T09:13:51 Copied rows OK.
2020-07-29T09:13:51 Analyzing new table...
2020-07-29T09:13:51 Swapping tables...
2020-07-29T09:13:51 Swapped original and new tables OK.
2020-07-29T09:13:51 Dropping old table...
2020-07-29T09:13:51 Dropped old table `425_pfm`.`_cms01_gift_key_old` OK.
2020-07-29T09:13:51 Dropping triggers...
2020-07-29T09:13:51 Dropped triggers OK.
Successfully altered `425_pfm`.`cms01_gift_key`.

real    0m13.026s
user    0m0.199s
sys     0m0.031s

time pt-online-schema-change -uroot  -S /tmp/mysql_3306.sock --alter='drop index  idx_gift_get' --execute D=425_pfm,t=cms01_gift_key

Cannot connect to S=/tmp/mysql_3306.sock,h=192.168.28.102,u=root
No slaves found.  See --recursion-method if host localhost has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/local/bin/pt-online-schema-change line 7077.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/local/bin/pt-online-schema-change line 7077.

# A software update is available:
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1




Altering `425_pfm`.`cms01_gift_key`...
Creating new table...
Created new table 425_pfm._cms01_gift_key_new OK.
Altering new table...
Altered `425_pfm`.`_cms01_gift_key_new` OK.
2020-07-29T09:09:47 Creating triggers...
2020-07-29T09:09:47 Created triggers OK.
2020-07-29T09:09:47 Copying approximately 472512 rows...
2020-07-29T09:09:52 Copied rows OK.
2020-07-29T09:09:52 Analyzing new table...
2020-07-29T09:09:52 Swapping tables...
2020-07-29T09:09:52 Swapped original and new tables OK.
2020-07-29T09:09:52 Dropping old table...
2020-07-29T09:09:52 Dropped old table `425_pfm`.`_cms01_gift_key_old` OK.
2020-07-29T09:09:52 Dropping triggers...
2020-07-29T09:09:52 Dropped triggers OK.
Successfully altered `425_pfm`.`cms01_gift_key`.

real    2m55.029s
user    0m0.208s
sys     0m0.051s
  • 其他使用示例
#常用操作：
# 添加字段
[root@rac1 bin]#  pt-online-schema-change -uroot  -pxxx --alter='add column col1_test int' --execute D=test,t=t_xxx_compensate
# 修改字段
[root@rac1 bin]# pt-online-schema-change  -uroot  -pxxx  --alter='MODIFY COLUMN col1_test TINYINT NOT NULL DEFAULT 0 ' --execute D=test,t=t_xxx_compensate              
# 改字段名
[root@rac1 bin]# pt-online-schema-change  -uroot  -pxxx  --alter='CHANGE COLUMN col1_test address varchar(30)' --execute D=test,t=t_xxx_compensate  
# 删除字段        
[root@rac1 bin]# pt-online-schema-change  -uroot  -pxxx  --alter='drop  column  address ' --execute D=test,t=t_xxx_compensate  
# 添加索引
[root@rac1 bin]# pt-online-schema-change -uroot  -pxxx --alter='add key indx_test(col1_test) ' --execute D=test,t=t_xxx_compensate
# 删除索引
[root@rac1 bin]# pt-online-schema-change -uroot  -pxxx --alter='DROP INDEX indx_test' --execute D=test,t=t_xxx_compensate