共计 4872 个字符，预计需要花费 13 分钟才能阅读完成。
背景
大家在日常工作中，往往须要对数据库的表构造做变更，个别波及到增删字段，批改字段属性等 ALTER 的操作。然而，在大表场景下，特地是千万级、亿级的大表，如果处理不当。这些操作往往会引发锁表的微小隐患，特地是在生产环境中，一旦在变更表构造过程中，呈现了长时间锁表，会导致用户产生的数据长时间无奈失常变更到表中，进而导致服务性能异样，后果将是灾难性的。
个别执行这种 Alter 类型的变更，咱们可能有以下的想法：
1、停服，在停服期间做表构造的变更，天然就能够避免对用户产生影响。然而，很多场景是不容许停服的。并且如果表的数据量达到上亿，那么须要停服工夫可能须要十几个小时，甚至更长，这是极不事实的；
2、凌晨执行，在用户较少的时间段内，做变更，尽量减少对用户产生影响。然而如果呈现锁表的话，万一有用户应用服务，服务将不可用；
3、应用换表，然而毛病是复制数据到新表期间，如果用户在这期间做了 update 或 delete 操作，且数据产生在曾经复制实现的局部，那么将无奈感知到这部分数据，导致失落掉用户的操作数据，危险太大；
4、应用存储过程，毛病是执行工夫会很久，且有可能影响到用户的 DDL 操作。因为为了避免每次循环批改时，锁住太多数据行，咱们须要管制每次更新数据的行数，粒度不能太大，否则很有可能会锁住用户正在操作的数据行。
那么针对以上理论的需要，就没有一个很好的工具，来解决咱们的痛点吗？其实在业界中，就有一个比拟成熟的工具，针对大表的场景，能够在线进行 Alter 变更，且不会呈现锁表的危险。除此之外，它还有其余的一些长处，让咱们开始摸索吧。
一、pt-osc 是什么
pt-online-schema-change 是 Percona-toolkit 一员，通过改良原生 ddl 的形式，达到不锁表在线批改表构造的成果。在 Percona 的官网中，对于 pt-osc 工具，也特地提到了 ALTER 表不会呈现锁表的个性。
针对下面谈到的防止锁表、感知用户更新删除动作等，ps-osc 工具是怎么解决的呢？
pt-osc 次要执行步骤如下：
1、创立一个跟原表截然不同的新表，命名形式为 ’_正式表名_new’;
2、应用 alter 语句将要变更的内容在新创建的新表上做变更，防止了对原表的 alter 操作；
3、在原表中创立 3 个触发器，别离是 insert、update 和 delete，次要是用于原表在往新表复制数据时，如果用户有 DDL 操作，触发器可能将在这期间呈现的 DDL 站长博客操作数据也写入到新表中，确保新表的数据是最新的，不会失落掉用户的新操作数据；
4、按块拷贝数据到新表，拷贝过程对数据行持有 S 锁；
5、重命名，将原表重命名为老表，命名为“_正式表名_old”，将新表重命名为正式表，可通过配置决定执行实现后是否删除掉老表；
6、删除 3 个触发器；
二、pt-osc 的装置
在 linux 零碎中装置步骤：
– 下载安装包
wget
tar -zxvf percona-toolkit-3.1.0.tar.gz– 装置依赖环境
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
yum -y install perl-Digest-MD5cd percona-toolkit-3.1.0perl Makefile.PL– 编译 makemake install
yum install mariadb– 装置 Mysql
yum install perl-DBD-MySQL
三、pt-osc 的应用
pt-osc 工具应用起来很简略，间接在 linux 命令行输出 pt-osc 格局的命令，即可间接执行。
以 Mysql 数据库减少一个名字是 MARK 的字段为例：
pt-online-schema-change –user=”root” –password=”*” –host=” 数据库 IP” –port=3306 –alter “ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT ‘mark source region is 1’;” D=my_test,t=t_test –no-drop-old-table –execute –print –no-check-replication-filters –charset=utf8 –no-check-unique-key-change –max-load=”Threads_running=100″ –critical-load=”Threads_running=300″ –recursion-method=none;
在下面的语句中：
1、user 和 password 别离为数据库执行变更操作的用户名、明码，须要高权限；
2、host 为数据库的 IP 地址；
3、port 为数据库的端口号；
4、alter 前面跟上具体的 alter 语句；
5、D 为 database 名字；
6、t 为要执行变更的表名；
7、no-drop-old-table 就是不要删除
8、charset，字符集，应用 utf8；
9、max-load，在复制数据时，工具会监控数据库中正在运行的线程数，如果大于配置的 Threads_running 值，那么会暂停复制，直到小于该值。以此避免对数据库造成较大压力，影响现网业务失常应用；
10、critical-load，默认为 50，在每个块之后查看 SHOW GLOBAL STATUS，与 max-load 不同的是，如果负载太高，, 间接停止，而不是暂停。可依据本人数据库状况斟酌配置阈值；
留神：在 –alter 前面跟着的变更语句中，列名不能够加符号，否则会呈现报错。如 –alter “ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT ‘mark source region is 1’;”，MARK 字段加了符号，就会呈现谬误，COMMENT 前面有 ` 符号无影响。
上面是应用 pt-osc 工具，理论执行一个作业时，打印进去的信息。为了平安起见，局部日志信息做了暗藏疏忽。
[root@ttt ~]# pt-online-schema-change --user="root" --password="*****" --host="数据库 IP" --port=3306 --alter "ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT'mark source region is 1';" D=my_test,t=t_test --no-drop-old-table --execute --print --no-check-replication-filters --charset=utf8 --no-check-unique-key-change --max-load="Threads_running=100" --critical-load="Threads_running=300" --recursion-method=none;
No slaves found. See –recursion-method if host EulerOS-BaseTemplate 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 my_test.t_test…
Creating new table…CREATE TABLE my_test._t_test_new (
ID int(11) NOT NULL AUTO_INCREMENT COMMENT ‘ 递增 ID’,
…………. 建表语句数据 …………….
Created new table my_test._t_test_new OK.
Altering new table…ALTER TABLE my_test._t_test_new ADD COLUMN MARK TINYINT NULL DEFAULT 1 COMMENT ‘mark source region is 1’;
Altered my_test._t_test_new OK.
2020-10-14T11:14:48 Creating triggers…2020-10-14T11:14:48 Created triggers OK.2020-10-14T11:14:48 Copying approximately 346697 rows…INSERT LOW_PRIORITY IGNORE INTO my_test._t_test_new (id, .. 建表语句信息 …. FROM my_test._t_test_new FORCE INDEX(PRIMARY) WHERE ((id >= ?)) AND ((id <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 31340 copy nibble/SELECT /!40001 SQL_NO_CACHE / id FROM my_test.t_test FORCE INDEX(PRIMARY) WHERE ((id >= ?)) ORDER BY id LIMIT ?, 2 /next chunk boundary/2020-10-14T11:14:53 Copied rows OK.2020-10-14T11:14:53 Analyzing new table…2020-10-14T11:14:53 Swapping tables…RENAME TABLE my_test.t_test TO my_test._t_test_old, my_test._t_test_new TO my_test.t_test2020-10-14T11:14:53 Swapped original and new tables OK.Not dropping old table because –no-drop-old-table was specified.2020-10-14T11:14:53 Dropping triggers…DROP TRIGGER IF EXISTS my_test.pt_osc_my_test_t_test_delDROP TRIGGER IF EXISTS my_test.pt_osc_my_test_t_test_updDROP TRIGGER IF EXISTS my_test.pt_osc_my_test_t_test_ins2020-10-14T11:14:54 Dropped triggers OK.
Successfully altered my_test.t_test.
四、性能比照
后面介绍了很多 pt-osc 的长处，以及良好的个性。那么理论应用成果到底怎么样呢？在测试环境中，专门做了一个测试，让大家有更加直观的感触。
在测试库中，筹备了一张 1600 万数据的大表，指标为对大表增加一个字段，别离应用存储过程和 pt-osc 工具，进行测试。
4.1 应用存储过程
首先应用存储过程做测试，为避免锁表，每次只更新 200 行。整个变更从开始到实现，须要消耗 90 分钟。其实，存储过程在执行过程中，如果恰好用户也在 DDL 操作存储过程正在变更的数据行，还有可能会锁住用户的数据，导致用户不能变更胜利。
4.2 应用 pt-osc 工具
pt-osc 从开始执行到变更实现，耗时 7 分钟左右，速度十分快。在执行的过程中，测试环境的服务连贯到该数据库，并执行多个会操作该表的工作，整个过程中，工作可能失常执行，未出现异常状况。
5、结语
ps-osc 的上述长处，在现网环境的不停服等要求下，可能优雅地帮忙咱们施行变更，且保障在变更期间，数据库不会受到锁表、过载等的影响，进而保障了业务可能失常运行。
