数据变更注意事项 - 1 生产环境改表结构

1. pt-online-schema-change

官网: https://docs.percona.com/percona-toolkit/

1. 新建一模—样的_new表
2. 新表执行更改字段操作
3. 原表上加三个触发器，DELETE/UPDATE/INSERT
4. 原表的数据拷贝到新表中，然后替换掉原表

---

2. 安装

下载地址：https://downloads.percona.com/downloads/percona-toolkit/3.3.1/binary/tarball/percona-toolkit-3.3.1_i386.tar.gz

解压

tar -zxvf percona-toolkit-3.3.1_i386.tar.gz

---

3. 尝试运行

cd percona-toolkit-3.3.1/bin

下面这句运行的时候可能会出现一些错误提示

./pt-online-schema-change --help

---

4. 解决错误提示

• 错误1

Can't locate Data/Dumper.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 ./pt-online-schema-change line 67.
BEGIN failed--compilation aborted at ./pt-online-schema-change line 67.

解决办法

yum install -y 'perl(Data::Dumper)'

• 错误2

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 ./pt-online-schema-change line 6340.
BEGIN failed--compilation aborted at ./pt-online-schema-change line 6340.

解决办法

yum install -y 'perl(Digest::MD5)'

---

5. 一个例子

含注释的例子

./pt-online-schema-change 
--charset=utf8  \ # 编码
--no-version-check \
--user=root \ # 用户名
--password=123456 \ # 密码
--host=127.0.0.1 P=3306, \ # ip 和 端口
D=myshop, t=t1\ # 数据库和要修改的表
--alter "ADD COLUNN c VARCHAR(64) NULL" \ # 要执行的修改指令
--execute 

一个完整的语句（不含注释）

[root@localhost bin]# ./pt-online-schema-change --charset=utf8 --no-version-check --user=root --password=123456 --host=127.0.0.1 P=3306,D=myshop,t=t1 --alter "ADD COLUMN c varchar(64) NULL" --execute;
No slaves found.  See --recursion-method if host localhost.localdomain 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 `myshop`.`t1`...
Creating new table...
Created new table myshop._t1_new OK.
Altering new table...
Altered `myshop`.`_t1_new` OK.
2022-10-13T07:33:34 Creating triggers...
2022-10-13T07:33:34 Created triggers OK.
2022-10-13T07:33:34 Copying approximately 1 rows...
2022-10-13T07:33:34 Copied rows OK.
2022-10-13T07:33:34 Analyzing new table...
2022-10-13T07:33:34 Swapping tables...
2022-10-13T07:33:34 Swapped original and new tables OK.
2022-10-13T07:33:34 Dropping old table...
2022-10-13T07:33:34 Dropped old table `myshop`.`_t1_old` OK.
2022-10-13T07:33:34 Dropping triggers...
2022-10-13T07:33:34 Dropped triggers OK.
Successfully altered `myshop`.`t1`.

第一次执行以上的指令可能会遇到一些错误提示，一般都是缺少了一些必要的要安装的内容。具体的解决办法，错误提示内容的下面都会提示。

• 错误1

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

解决办法

yum install perl-DBI -y

• 错误2

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

解决办法

yum install perl-DBD-MySQL -y

• 执行的流程

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 

---

如果觉得有收获就点个赞吧，更多知识，请点击关注查看我的主页信息哦~