Home > database, mysql, RDS > RDS MySql支持online ddl

RDS MySql支持online ddl

在日常和客户沟通的过程中发现,他们在做mysql ddl变更的时候由于MySql本身的缺陷不支持online ddl,导致他们的业务不得不hang住一会儿,表越大,时间影响越长,所以期待有更好的解决方法;有些用户也想了一些方法,比如通过主备切换的方法,先在备库进行ddl,然后在通过主备切换到原主库进行ddl,但由于RDS对外提供给用户的是一个dns加port,所以后端的主备对用户是透明的,此方法行不通。其实在开源社区中已经有比较成熟的方法,那就是percona的pt-online-schema-change工具是其中之一,下面通过测试主要了解该工具的可靠性以及存在的问题,是否在RDS上支持。

原理:
在线修改表结构的工具,基本处理方式类似,以下对pt-online-schema-change工具的工作原理进行分析:
1、如果存在外键,根据alter-foreign-keys-method参数的值,检测外键相关的表,做相应设置的处理。
2、创建一个新的表,表结构为修改后的数据表,用于从源数据表向新表中导入数据。
3、创建触发器,用于记录从拷贝数据开始之后,对源数据表继续进行数据修改的操作记录下来,用于数据拷贝结束后,执行这些操作,保证数据不会丢失。
4、拷贝数据,从源数据表中拷贝数据到新表中。
5、修改外键相关的子表,根据修改后的数据,修改外键关联的子表。
6、rename源数据表为old表,把新表rename为源表名,并将old表删除。
7、删除触发器。
3.RDS支持:
a.在现有的用户权限基础上开通replication slave权限
[root@testadmin bin]# ./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add column is_sign_1 int(11)” D=test,t=t –execute
DBD::mysql::db selectall_arrayref failed: Access denied; you need the REPLICATION SLAVE privilege for this operation [for Statement “SHOW SLAVE HOSTS”] at ./pt-online-schema-change line 4051.

grant REPLICATION SLAVE ON *.* TO ‘test123’@’%’;

b.表中含有主键或者唯一索引
[root@testadmin bin]# ./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add column is_sign_1 int(11)” D=test,t=t –execute
Cannot chunk the original table `test`.`t`: There is no good index and the table is oversized. at ./pt-online-schema-change line 5365.

4.测试:
在测试的过程中,测试插入数据,删除数据,更新数据,观察是否阻塞,同时对表进行不断的压测:
delimiter ;;
CREATE
PROCEDURE e_test()
BEGIN
WHILE 1 DO
insert into t(name,gmt_create,name2) values(‘xxx’,now(),’xxx’);
END WHILE;
END;
;;

call e_test();

mysql> insert into test(gmt_create) values(now());
Query OK, 1 row affected (0.12 sec)

mysql> delete from test where id=1;
Query OK, 1 row affected (0.01 sec)

mysql> update test set gmt_Create=now() where id=2;
Query OK, 1 row affected (0.30 sec)
Rows matched: 1 Changed: 1 Warnings: 0

添加字段:
./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add column is_sign_2 int(11)” D=qianyi,t=test –execute
添加索引:
./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add index ind_gmt_create(gmt_create)” D=qianyi,t=test –execute
修改字段:
./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”modify column is_sign_2 bigint” D=qianyi,t=test –execute
5.结果:
[root@testadmin bin]# ./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add column is_sign_1 int(11)” D=qianyi,t=t –execute
Altering `qianyi`.`test`…
Creating new table…
Created new table qianyi._test_new OK.
Altering new table…
Altered `qianyi`.`_test_new` OK.
Creating triggers…
Created triggers OK.
Copying approximately 8388968 rows…
Copying `qianyi`.`test`: 52% 00:26 remain
Copied rows OK.
Swapping tables…
Swapped original and new tables OK.
Dropping old table…
Dropped old table `qianyi`.`_test_old` OK.
Dropping triggers…
Dropped triggers OK.
Successfully altered `qianyi`.`test`.
[root@testadmin bin]#
[root@testadmin bin]# ./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”modify column is_sign_2 bigint” D=qianyi,t=t –execute
Altering `qianyi`.`test`…
Creating new table…
Created new table qianyi._test_new OK.
Altering new table…
Altered `qianyi`.`_test_new` OK.
Creating triggers…
Created triggers OK.
Copying approximately 8388885 rows…
Copying `qianyi`.`t`: 53% 00:25 remain
Copied rows OK.
Swapping tables…
Swapped original and new tables OK.
Dropping old table…
Dropped old table `qianyi`.`_test_old` OK.
Dropping triggers…
Dropped triggers OK.
Successfully altered `qianyi`.`test`.

[root@testadmin bin]# ./pt-online-schema-change –u=test123 –host=test.mysql.rds.aliyuncs.com –port=3306 –password=hell05a –alter=”add index ind_gmt_create(gmt_create)” D=qianyi,t=t –execute
Altering `qianyi`.`test`…
Creating new table…
Created new table qianyi._test_new OK.
Altering new table…
Altered `qianyi`.`_test_new` OK.
Creating triggers…
Created triggers OK.
Copying approximately 8388785 rows…
Copying `qianyi`.`test`: 41% 00:42 remain
Copying `qianyi`.`test`: 83% 00:12 remain
Copied rows OK.
Swapping tables…
Swapped original and new tables OK.
Dropping old table…
Dropped old table `qianyi`.`_test_old` OK.
Dropping triggers…
Dropped triggers OK.
Successfully altered `qianyi`.`test`.

6结论:
1.RDS开通用户帐号replication slave权限支持pt-online-ddl,用户的表必须要有主键或者唯一索引;
2.当业务量较大时,修改操作会等待没有数据修改后,执行最后的rename操作。因此,在修改表结构时,应该尽量选择在业务相对空闲时,至少修改表上的数据操作较低时,执行较为妥当。

附:pt-online-schema-change

下载地址:http://www.percona.com/redir/downloads/percona-toolkit/2.2.1/percona-toolkit-2.2.1.tar.gz

 

Categories: database, mysql, RDS Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.