pt-online-schema-change

最新推荐文章于 2023-07-19 07:30:00 发布

TTRy_Harder

最新推荐文章于 2023-07-19 07:30:00 发布

阅读量4.5k

点赞数 2

分类专栏：数据库 Linux运维文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44352521/article/details/108558026

版权

Linux运维同时被 2 个专栏收录

24 篇文章 2 订阅

订阅专栏

数据库

6 篇文章 0 订阅

订阅专栏

问题：假如现在处于公司业务高峰期，但是需求要给数据库中某个表增加一个column，那么需要如何操作？

增加column属于alter表，在业务高峰期进行alter table table_name add column column_name datatype 会对业务带来较大影响；

所以需要解决这个问题，就需要通过在线ddl来实现，把对业务的影响减到最小；目前可用的 DDL 操作工具包括 pt-osc，github 的 gh-ost，以及 MySQL 提供的在线修改表结构命令 Online DDL。
pt-osc 和 gh-ost 均采用拷表方式实现，即创建个空的新表，通过 select+insert 将旧表中的记录逐次读取并插入到新表中，不同之处在于处理 DDL 期间业务对表的 DML 操作（增删改）。

mysql online ddl的问题现状：在运维 mysql 数据库时，我们总会对数据表进行 ddl 变更，修改添加字段或者索引，对于 mysql 而已，ddl 显然是一个令所有 MySQL dba 诟病的一个功能，因为在 MySQL 中在对表进行 ddl 时，会锁表，当表比较小比如小于 1w 上时，对前端影响较小，当时遇到千万级别的表就会影响前端应用对表的写操作。
对此，perconal推出一个工具 pt-online-schema-change ，其特点是修改过程中不会造成读写阻塞。

目前 InnoDB 引擎是通过以下步骤来进行 DDL 的： 
1. 按照原始表（original_table）的表结构和 DDL 语句，新建一个不可见的临时表（tmp_table）； 
2. 在原表上加 write lock，阻塞所有更新操作（insert、delete、update 等）； 
3. 执行 insert into tmp_table select * from original_table； 
4. rename original_table 和 tmp_table，最后 drop original_table； 
5. 释放 write lock； 

我们可以看见在 InnoDB 执行 DDL 的时候，原表是只能读不能写的；

以上大概就是mysql online ddl的原理，本文着重介绍pt-osc工具进行在线ddl 的原理和原理：

pt-osc 的原理：

1. 创建一张新表，表结构与旧表相同； 
2. Alter 新表；
3. 在原表上创建 insert、update、delete 三种类型的触发器； 
4. 将旧表的数据拷贝到新表中，同时通过触发器将旧表中的操作映射到新表； 
5. 如果原表有外键约束，处理外键； 
6. 原表重命名为 old 表，new 表重命名为原表，整个过程为原子操作； 
7. 删除 old 表（默认）；

首先需要安装percona-toolkit：
# wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz
# tar xf percona-toolkit-3.2.1_x86_64.tar.gz
# cd percona-toolkit-3.2.1
# perl Makefile.PL
# make
# make test
# make install

在 test_ptosc库中创建测试表online_table：
（目前online_table表中只有三个字段，需求是在线alter table online_table add column content text 即在数据库业务运行的状态下新加一个content字段）

测试表：
CREATE TABLE `online_table` ( 
`id` int(11) NOT NULL, 
`name` varchar(10) DEFAULT NULL, 
`age` int(11) DEFAULT NULL 
)engine = innodb default charset utf8;

在这里插入图片描述
表创建好之后，先使用pt-online-schema-change 命令跑一遍，但是–dry-run参数不会真正执行添加字段的操作，只是说可以先看下在线ddl的一个大概流程；

# pt-online-schema-change --user=root --password=redhat --host=localhost --alter "add column content text" D=test_ptosc,t=online_table --print --dry-run

之后出现报错：Can't locate Digest/MD5.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at /usr/local/bin/pt-online-schema-change line 6339.

报错是因为缺少perl-Digest-MD5包
# yum -y install perl-Digest-MD5

安装之后，又出现如下两个报错：
# pt-online-schema-change --user=root --password=redhat --host=localhost --alter "add column content text" D=test_ptosc,t=online_table --print --dry-run

Cannot connect to MySQL: Cannot connect to MySQL because the Perl DBI module is not installed or not found.  Run 'perl -MDBI' to see the directories that Perl searches for DBI.  If DBI is not installed, try:
  Debian/Ubuntu  apt-get install libdbi-perl
  RHEL/CentOS    yum install perl-DBI
  OpenSolaris    pkg install pkg:/SUNWpmdbi


# pt-online-schema-change --user=root --password=redhat --host=localhost --alter "add column content text" D=test_ptosc,t=online_table --print --dry-run

Cannot connect to MySQL: Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found.  Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql.  If DBD::mysql is not installed, try:
  Debian/Ubuntu  apt-get install libdbd-mysql-perl
  RHEL/CentOS    yum install perl-DBD-MySQL
  OpenSolaris    pgk install pkg:/SUNWapu13dbd-mysql

提示需要安装perl-DBI、perl-DBD-MySQL：
# yum install perl-DBI -y
# yum install perl-DBD-MySQL

在这里插入图片描述

解决掉上述错误之后，继续执行在线ddl，执行之后，再次出现报错 如下：
# pt-online-schema-change --user=root --password=redhat --host=localhost --socket=/tmp/mysql.sock --alter "add column content text" D=test_ptosc,t=online_table --print --dry-run

Cannot connect to MySQL: DBI connect('test_ptosc;host=localhost;mysql_socket=/tmp/mysql.sock;mysql_read_default_group=client','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at /usr/local/bin/pt-online-schema-change line 2345.
可以看到是因为认证插件失败:认证插件“caching_sha2_password”

在这里插入图片描述

解决方法：
法一：适用于第一次安装
//直接修改配置文件在重启服务即可
[mysqld]
default_authentication_plugin = mysql_native_password

systemctl restart mysqld.service

# mysql -u root -p

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)



法二：适用于已安装完成后新增用户
# mysql -u root -p
mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

可以看到：root 用户的验证器插件为 caching_sha2_password
修改身份验证类型(修改密码)：
mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'redhat' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'redhat';
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER USER 'root'@'172.19.0.32' IDENTIFIED WITH mysql_native_password BY 'redhat';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | mysql_native_password |
+-------------------------------+-----------------------+
1 row in set (0.01 sec)

mysql> select host,user,plugin from mysql.user;
+-------------+------------------+-----------------------+
| host        | user             | plugin                |
+-------------+------------------+-----------------------+
| %           | root             | mysql_native_password |
| 172.19.0.32 | root             | mysql_native_password |
| localhost   | mysql.infoschema | caching_sha2_password |
| localhost   | mysql.session    | caching_sha2_password |
| localhost   | mysql.sys        | caching_sha2_password |
+-------------+------------------+-----------------------+
root用户的身份验证器插件已经变为：mysql_native_password

到此错误都已经解决，我们可以先看下pt-osc的原理过程：

# pt-online-schema-change --user=root --password=redhat --host=localhost --socket=/tmp/mysql.sock --alter "add column content text" D=test_ptosc,t=online_table --print --dry-run
//执行上面的语句，并不会真正的去修改表；因为--dry-run参数是创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run 与--execute 必须指定一个，二者相互排斥。和--print 配合最佳。
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
Starting a dry run.  `test_ptosc`.`online_table` will not be altered.  Specify --execute instead of --dry-run to alter the table.
// 创建新表，跟original table表结构一致
Creating new table...
CREATE TABLE `test_ptosc`.`_online_table_new` (
  `id` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
// new table创建完成
Created new table test_ptosc._online_table_new OK.
Altering new table...
// add column 
ALTER TABLE `test_ptosc`.`_online_table_new` add column content text
Altered `test_ptosc`.`_online_table_new` OK.
// 提示没有主键或者唯一索引
The new table `test_ptosc`.`_online_table_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
Please check you have at least one UNIQUE and NOT NULLABLE index.
2020-09-13T02:53:26 Dropping new table...
DROP TABLE IF EXISTS `test_ptosc`.`_online_table_new`;
2020-09-13T02:53:27 Dropped new table OK.
Dry run complete.  `test_ptosc`.`online_table` was not altered.

以上输出大概展示了pt-osc工具在线ddl的大概流程，目前所有环境已经设置完成，下面使用–execute参数执行看是否有效。在此之前，先为原表添加主键或者唯一索引：

mysql> alter table online_table add primary key (id),modify id int not null auto_increment;

在这里插入图片描述

添加完主键之后继续执行命令：
[root@test ~]# pt-online-schema-change --user=root --password=redhat --host=localhost --socket=/tmp/mysql.sock --alter "add column content text" D=test_ptosc,t=online_table --print --execute
//--execute确定修改表，则指定该参数。真正执行。

No slaves found.  See --recursion-method if host test 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 `test_ptosc`.`online_table`...
// 创建新表
Creating new table...
CREATE TABLE `test_ptosc`.`_online_table_new` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Created new table test_ptosc._online_table_new OK.
Altering new table...
// 修改新表
ALTER TABLE `test_ptosc`.`_online_table_new` add column content text
Altered `test_ptosc`.`_online_table_new` OK.
2020-09-13T02:57:37 Creating triggers...
// 触发器创建
2020-09-13T02:57:37 Created triggers OK.
// 执行数据复制的操作，原表数据插入到新表
2020-09-13T02:57:37 Copying approximately 1 rows...
INSERT LOW_PRIORITY IGNORE INTO `test_ptosc`.`_online_table_new` (`id`, `name`, `age`) SELECT `id`, `name`, `age` FROM `test_ptosc`.`online_table` LOCK IN SHARE MODE /*pt-online-schema-change 5725 copy table*/
2020-09-13T02:57:37 Copied rows OK.
// 插入完毕后交换表
2020-09-13T02:57:37 Analyzing new table...
2020-09-13T02:57:37 Swapping tables...
RENAME TABLE `test_ptosc`.`online_table` TO `test_ptosc`.`_online_table_old`, `test_ptosc`.`_online_table_new` TO `test_ptosc`.`online_table`
2020-09-13T02:57:37 Swapped original and new tables OK.
// 数据复制完毕之后，删除原表
2020-09-13T02:57:37 Dropping old table...
DROP TABLE IF EXISTS `test_ptosc`.`_online_table_old`
2020-09-13T02:57:37 Dropped old table `test_ptosc`.`_online_table_old` OK.
// 删除触发器
2020-09-13T02:57:37 Dropping triggers...
DROP TRIGGER IF EXISTS `test_ptosc`.`pt_osc_test_ptosc_online_table_del`
DROP TRIGGER IF EXISTS `test_ptosc`.`pt_osc_test_ptosc_online_table_upd`
DROP TRIGGER IF EXISTS `test_ptosc`.`pt_osc_test_ptosc_online_table_ins`
2020-09-13T02:57:37 Dropped triggers OK.
Successfully altered `test_ptosc`.`online_table`.

在这里插入图片描述

在这里插入图片描述
到此可以看出，执行完毕命令之后content字段已经成功添加；

TTRy_Harder

关注

2
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
pt-online-schema-change

通过测试的方式查看其原理：安装percona-toolkit：# wget https://www.percona.com/downloads/percona-toolkit/3.2.1/binary/tarball/percona-toolkit-3.2.1_x86_64.tar.gz# tar xf percona-toolkit-3.2.1_x86_64.tar.gz# cd percona-toolkit-3.2.1# perl Makefile.PL# make# make test
复制链接

扫一扫