为处理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机制的触发器来同步修改备份表,然后把表按固定行数分批插入到备份表中(执行插入时会使用低优先级执行,且忽略报错,因为先有的增量备份可能会有数据重复部分),同时最后删除原表将备份表改为原表名
具体操作情况可以打开通用日志(具体怎么打开,看我前面的文章),将日志改为表,然后执行修改,在通用日志中可以查看修改时执行的语句
标签:12,07,DBA,31,toolkit,2024,percona,26T14,table From: https://blog.csdn.net/etinemark/article/details/140718009