mysql大表修改字段导致锁表（非阻塞）

线上数据库难免会有修改表结构的需求，MySQL 在修改表结构时会锁表，这就会影响读写操作，小表还好，一会儿就修改完成了，但大表会比较麻烦，下面看一个解决方案

一，方式一

解决思路

（1）新建一个表，结构就是要修改后的结构

（2）在旧表上建立触发器，旧表更新数据时同步到新表

（3）把旧表数据复制到新表

（4）数据同步完成后，执行重命名操作，交换新旧表

（5）删除旧表及触发器

实现方式

这个解决思路已经有了很成熟的工具，数据库服务公司 Percona 提供了 MySQL Toolkit 工具集，其中的 pt-online-schema-change 就是用来进行线上的表结构修改，不会阻塞读写

• 使用示例

pt-online-schema-change

--user=root --password=111111

--host=192.168.31.157

--alter "modify name CHAR(50)"

D=sakila,t=test

--execute

从结果信息中可以看出这个工具的执行过程

• 参数说明

--user、--password、--host

数据库的连接信息

--alter

指定要执行的修改操作，例如修改表结构的语句为：

alter table test modify name varchar(60);

这个参数的值就是 “alter table test ”后面的部分

D=sakila,t=test

指定数据库和表名

--execute

确定执行修改操作，有个与其对应的参数 --dry-run，并不真正执行，可以看到生成的执行语句，用来了解其执行步骤与细节

• 安装方法

下载地址

https://www.percona.com/downloads/percona-toolkit/

centos7 安装示例

下载 rpm 版本

安装依赖包

yum install epel-release-7-5.noarch

yum install perl-DBD-MySQL

yum install perl-Digest-MD5

yum install perl-Config-Tiny

yum install perl-Log-Dispatch

yum install perl-Parallel-ForkManager

yum install -y rrdtool perl-rrdtool rrdtool-devel perl-Params-Validate

安装 toolkit

rpm -ivh percona-toolkit.rpm

安装完成后就可以直接执行了

 方式二，

5.6 以后增加了ONLINE DDL，

实现不锁表增加字段和索引非常简单。

解决办法

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONECOPY

ALGORITHM表示算法：default默认（根据具体操作类型自动选择），inplace（不影响DML），copy创建临时表（锁表），INSTANT只修改元数据（8.0新增，在修改名字等极少数情况可用）

LOCK表示是否锁表：default默认，none，shared共享锁，exclusive

参考：如何在修改Mysql表结构时不影响读写？ - 腾讯云开发者社区-腾讯云