提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其它

当前栏目

pt-online-schema-change

Change Schema Online pt
2023-09-14 08:56:56 时间

一、背景

MySQL大字段的DDL操作：加减字段、索引、修改字段属性等，在5.1之前都是非常耗时耗力的，特别是会对MySQL服务产生影响。在5.1之后随着Plugin Innodb的出现在线加索引的提高了很多，但是还会影响（时间缩短了），主要是在更改期间，会生成一个互斥锁，阻塞对整个表的所有操作。不过MySQL 5.6可以避免上面的情况，支持在线DDL操作了。但目前大部分在用的版本都是5.6之前的，所以DDL操作一直是运维人员头疼的事。那如何在不锁表的情况下安全快速地更新表结构？现在来说明下percona-toolkit的pt-online-schema-change（简称:OSC）的使用说明，可以很好的解决上述的问题。

在我们的以前做法中，为了不影响线上业务，我们一般采用：先在线下从库更改表结构，然后替换线上从库，这样一台台的修改，最后做一下主库切换，这个过程会耗费很长时间，并且在做主库切换时，风险也非常的大，我们怎样才能让时间更短，且能不阻塞读写情况下在线修改呢？早在2008年Shlomi Noach 就利用触发器的原理，开发了python版本oak-online-alter-table在线更改表结构脚本，最近，Percona公司在自己的percona-toolkit脚本集合中也发布了在线更改表结构的perl版本脚本pt-online-schema-change，Facebook公司也开发自己的在线更改表结构php版本脚本OnlineSchemaChange.php，而它们最底层的实现原理都为触发器。因为oak-online-alter-table不确定是否在被开发与支持，而OnlineSchemaChange.php的使用比较复杂，且有很多功能不支持如：表结构中如果有外键的情况，故在此我就Percona公司的脚本做详细的剖析。

在线修改大表的可能影响

  • 在线修改大表的表结构执行时间往往不可预估，一般时间较长
  • 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作
  • 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入
  • 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低
  • 在线修改大表结构容易导致主从延时，从而影响业务读取

pt-online-schema-change的安装

#安装依赖
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
# 切换目录
cd /usr/local/src
# 下载
wget percona.com/get/percona-toolkit.tar.gz
# 解压
tar -zvxf percona-toolkit.tar.gz
cd percona-toolkit-3.0.13/
# 安装perl依赖
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
perl Makefile.PL
# 编译安装
make
make install
# 验证
pt-online-schema-change

验证后报错

 

 缺少perl-Digest-MD5包， 安装perl-Digest-MD5即可解决

yum -y install perl-Digest-MD5

出现如下结果，成功。

 

创建测试数据：

 

mysql -h localhost -uroot -p123456

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
 
mysql> use test;
Database changed
mysql> CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT COMMENT '用户主键',name varchar(20)  DEFAULT '' COMMENT '用户名',PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';
Query OK, 0 rows affected (0.04 sec)
mysql> insert into users(name) values('test1'),('test2'),('test3');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)
 
mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
mysql> exit;

 

添加一个字段：

sql语句：
alter table users add age varchar(10) NOT NUll DEFAULT '' COMMENT '年龄';

pt-online-schema-change写法：
pt-online-schema-change --alter="add age varchar(10) NOT NUll DEFAULT '' COMMENT '年龄' ;"  --execute --print --max-lag=5 D=test,t=users,u=root,p=123456,S=/tmp/mysql.sock --no-check-replication-filters --max-load="Threads_running=100" --critical-load="Threads_running=120" --charset=utf8   --chunk-size=100

 

使用 pt-osc原生 5.6 online ddl相比，如何选择

online ddl在必须copy table时成本较高，不宜采用 pt-osc工具在存在触发器时，不适用 修改索引、外键、列名时，优先采用online ddl，并指定 ALGORITHM=INPLACE 其它情况使用pt-osc，虽然存在copy data pt-osc比online ddl要慢一倍左右，因为它是根据负载调整的 无论哪种方式都选择的业务低峰期执行 特殊情况需要利用主从特性，先alter从库，主备切换，再改原主库

相关文章