在线修改表结构

李伟 2017-09-22 10:55:09

1、问题

在MySQL中,使用ALTER TABLE语法会给整张表加锁,会导致两个问题:

  • 改表过程中所有写操作(DML)会被阻塞
  • 容易产生主从延迟

2、解决方案

percona提供了一个工具集,其中pt_online_schema_change能不影响业务的同时修改表结构(DDL),详情参考:https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html pt_online_schema_change的原理是:

  1. 首先按照原表结构复制一个新表
  2. 然后给新表执行改表结构操作
  3. 在原表上添加触发器让原表的所有改动同步到新表
  4. 一段一段地读原表的数据并把数据写入到新表
  5. 检查从库是否延迟,如果有延迟它会暂停写入并等待从库延迟恢复
  6. 最后将修改原表表名为_原表表名_old,并将新表表名改成原表表名
  7. 删掉_old表

3、修改无从库表

修改只有主库没有从库的数据库中的表结构,可以使用本小节中的方法进行。 修改mydb库的user表,仅预演一下,并不实际执行,这时可以确认下是否有问题:

pt-online-schema-change --no-version-check --charset=utf8 --alter "modify userId int(11) unsigned NOT NULL default '0' comment '用户ID'" D=mydb,t=user,P=3306,u=root,p=mypassword,h=192.168.1.1 --dry-run

确认没有问题后,实际执行改表操作:

pt-online-schema-change --no-version-check --charset=utf8 --alter "modify projectId int(11) unsigned NOT NULL default '0' comment 'projectId'" D=mydb,t=user,P=3306,u=root,p=mypassword,h=192.168.1.1 --execute

值得说明的是: 1. –charset=utf8 指定使用UTF8字符,中文注释不会变成乱码 2. –no-version-check针对阿里云的RDS不检查版本号(非阿里云RDS则不需要加此参数) 3. –dry-run和–execute是互斥的,–dry-run只是预演一下而不实际执行,–execute则实际执行

4、修改有从库表

修改一台主库挂载一台或多台从库的数据库中的表结构,可以使用本小节中的方法进行。 比如192.168.1.1有两台从库为192.168.1.2和192.168.1.3,那么我们在192.168.1.1中创建dsns表并添加两条数据:

USE mydb;
CREATE TABLE `dsns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `dsn` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO dsns(dsn) VALUES('h=192.168.1.2,P=3306');
INSERT INTO dsns(dsn) VALUES('h=192.168.1.3,P=3306');

还以刚才修改user表的情况为例,为了防止主从延迟我们用以下的方式进行:

pt-online-schema-change --no-version-check --charset=utf8 --recursion-method=dsn=D=mydb,t=dsns --alter "modify userId int(11) unsigned NOT NULL default '0' comment '用户ID'" D=mydb,t=user,P=3306,u=root,p=mypassword,h=192.168.1.1 --dry-run

确认没有问题后,实际执行改表操作:

pt-online-schema-change --no-version-check --charset=utf8 --recursion-method=dsn=D=mydb,t=dsns --alter "modify userId int(11) unsigned NOT NULL default '0' comment '用户ID'" D=mydb,t=user,P=3306,u=root,p=mypassword,h=192.168.1.1 --execute

值得说明的是: 1. –recursion-method=dsn=D=mydb,t=dsns 指使用mydb库中的dsns表中的链接串连接从库