pt-online-schema-change

问题：假如现在处于公司业务高峰期，但是需求要给数据库中某个表增加一个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;

添加完主键之后继续执行命令：
[[e-mail] ~]# 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字段已经成功添加；