DBA笔记-第七部分（percona toolkit工具添加索引 ）

为处理alter table 的情况DBA常使用percona toolkit工具

下载工具地址：

wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz

解压后cd到bin目录 或者添加环境变量

vim /etc/profile

环境变量最下行添加你自己的bin路径：

export PATH=$PATH:/my_mysql/percona-toolkit-3.1.0/bin

pt-online-schema-change --alter "ADD INDEX idx_column (column_name)" D=my_database,t=my_table --execute

执行失败的话顺序安装CPAN工具-Digest::MD5模块-perl-DBD-MySQL链接工具

-sudo yum install perl-CPAN

sudo cpan Digest::MD5

sudo yum install perl-DBD-MySQL

 我这边多实例选择的是socket连接执行的是（注意要有个主键，我找了半天创建失败问题）

[root@my1 ~]# pt-online-schema-change --alter "ADD INDEX idx_b (b)" D=emp,t=a --execute --socket /temp/mysql.sock4
No slaves found.  See --recursion-method if host my1 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 `emp`.`a`...
Creating new table...
Created new table emp._a_new OK.
Altering new table...
Altered `emp`.`_a_new` OK.
2024-07-26T14:31:12 Creating triggers...
2024-07-26T14:31:12 Created triggers OK.
2024-07-26T14:31:12 Copying approximately 3 rows...
2024-07-26T14:31:12 Copied rows OK.
2024-07-26T14:31:12 Analyzing new table...
2024-07-26T14:31:12 Swapping tables...
2024-07-26T14:31:12 Swapped original and new tables OK.
2024-07-26T14:31:12 Dropping old table...
2024-07-26T14:31:12 Dropped old table `emp`.`_a_old` OK.
2024-07-26T14:31:12 Dropping triggers...
2024-07-26T14:31:12 Dropped triggers OK.
Successfully altered `emp`.`a`.

 工具执行完后查看索引是否创建

show create table a;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

具体percona toolkit工具原理，简单来说创建几个增删改replace机制的触发器来同步修改备份表，然后把表按固定行数分批插入到备份表中（执行插入时会使用低优先级执行，且忽略报错，因为先有的增量备份可能会有数据重复部分），同时最后删除原表将备份表改为原表名

具体操作情况可以打开通用日志（具体怎么打开，看我前面的文章），将日志改为表，然后执行修改，在通用日志中可以查看修改时执行的语句