Percona Toolkit 神器全攻略（开发类）

作者：GreatSQL

2024-08-23
福建
本文字数：6802 字
阅读完需：约 22 分钟

Percona Toolkit 神器全攻略（开发类）

Percona Toolkit 神器全攻略系列共八篇，前文回顾：

全文约定：$为命令提示符、greatsql>为 GreatSQL 数据库提示符。在后续阅读中，依据此约定进行理解与操作

开发类

在 Percona Toolkit 中开发类共有以下工具

pt-duplicate-key-checker：列出并删除重复的索引和外键
pt-online-schema-change：在线修改表结构
pt-show-grants：规范化和打印权限
pt-upgrade：在多个服务器上执行查询，并比较不同

pt-duplicate-key-checker

概要

检查 MySQL/GreatSQL 表中的重复或冗余索引和外键

用法

pt-duplicate-key-checker [OPTIONS] [DSN]

选项

该工具所有选项如下

最佳实践

创建一张表，包含两个重复索引

-- 创建一张test_table表CREATE TABLE `test_table` (  `id` INT NOT NULL AUTO_INCREMENT,  `name` VARCHAR(255) DEFAULT NULL,  `age` INT DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB;
-- 创建name字段的索引greatsql> CREATE INDEX `idx_name` ON `test_table` (`name`);
-- 创建age字段的索引greatsql> CREATE INDEX `idx_age` ON `test_table` (`age`);
-- 创建一个冗余的索引`idx_name_age`greatsql> CREATE INDEX `idx_name_age` ON `test_table` (`name`, `age`);

复制代码

使用pt-duplicate-key-checker工具

$ pt-duplicate-key-checker -uroot --ask-pass --socket=/data/GreatSQL/MySQL.sock -d test_db Enter password: # 这里输入密码 # ######################################################################### test_db.test_table                                                      # ########################################################################
# idx_name is a left-prefix of idx_name_age# Key definitions:#   KEY `idx_name` (`name`),#   KEY `idx_name_age` (`name`,`age`)# Column types:#         `name` varchar(255) default null#         `age` int default null# To remove this duplicate index, execute:ALTER TABLE `test_db`.`test_table` DROP INDEX `idx_name`;
# ######################################################################### Summary of indexes                                                      # ########################################################################
# Size Duplicate Indexes   1023# Total Duplicate Indexes  1# Total Indexes            11

复制代码

由上述输出的检查信息中，可以看到，表test_table中存在冗余索引，并且给出对应可以删除重复索引的 SQL 命令

结尾的统计信息：

Size Duplicate Indexes，检查的索引占用空间大小
Total Duplicate Indexes，检查的冗余索引数量
Total Indexes，检查的总的索引数量

当然也可以直接看系统表schema_redundant_indexes

greatsql> select * from sys.schema_redundant_indexes\G*************************** 1. row ***************************              table_schema: test_db                table_name: test_table      redundant_index_name: idx_name   redundant_index_columns: nameredundant_index_non_unique: 1       dominant_index_name: idx_name_age    dominant_index_columns: name,age dominant_index_non_unique: 1            subpart_exists: 0            sql_drop_index: ALTER TABLE `test_db`.`test_table` DROP INDEX `idx_name`

复制代码

pt-online-schema-change

概要

在线修改表结构，特点是修改过程中不会造成读写阻塞

原理

工作原理是创建要更改的表的空副本，根据需要对其进行修改，然后将原始表中的行复制到新表中。复制完成后，它会移走原始表并用新表替换。默认情况下，它还会删除原始表

用法

pt-online-schema-change [OPTIONS] DSN

选项

互斥关系

该工具所有选项如下

最佳实践

先创建一张表，并插入 1 万条数据

greatsql> CREATE TABLE test_db.ptosc (id INT PRIMARY KEY AUTO_INCREMENT,k BIGINT NOT NULL,c VARCHAR(255) NOT NULL,pad VARCHAR(255) NOT NULL);
greatsql> select count(*) from ptosc;+----------+| count(*) |+----------+|    10000 |+----------+1 row in set (0.00 sec)

复制代码

添加一列

使用pt-online-schema-change工具添加一列：a，类型为 INT

$ pt-online-schema-change --host=localhost --user=root --ask-pass --alter "ADD COLUMN a INT" D=test_db,t=ptosc --print --execute

复制代码

--print 是打印工具执行过程
--execute 确认开始

此时会输出工具的执行过程，来一段段解析

在执行前的一些状态检查及默认的操作设置

# 下一行表示工具没有找到任何从服务器（slaves）No slaves found.  See --recursion-method if host myarch has slaves.# 下一行表示工具没有检查从服务器的延迟，因为它没有找到任何从服务器，并且也没有指定 --check-slave-lag 选项来强制检查从服务器的延迟。Not checking slave lag because no slaves were found and --check-slave-lag was not specified.# 以下部分列出了 pt-online-schema-change 工具在更改过程中可能会执行的操作，以及默认的重试次数和等待时间：Operation, tries, wait:# 分析原表的结构，以准备进行更改。如果失败，将重试 10 次，每次失败后等待 1 秒。  analyze_table, 10, 1# 从原表复制行到新表。如果复制失败，将重试 10 次，每次失败后等待 0.25 秒。  copy_rows, 10, 0.25# 在新表上创建触发器，以便捕获对原表的任何更改，并将这些更改应用到新表。如果失败，将重试 10 次，每次失败后等待 1 秒。  create_triggers, 10, 1# 在切换完成后删除这些触发器。如果失败，将重试 10 次，每次失败后等待 1 秒。  drop_triggers, 10, 1# 切换原表和新表，使新表成为活动表。如果失败，将重试 10 次，每次失败后等待 1 秒。  swap_tables, 10, 1# 更新与新表相关的任何外键约束。如果失败，将重试 10 次，每次失败后等待 1 秒。  update_foreign_keys, 10, 1

复制代码

创建一张名为_ptosc_new的新表

Creating new table...CREATE TABLE `test_db`.`_ptosc_new` (  `id` int NOT NULL AUTO_INCREMENT,  `k` bigint NOT NULL,  `c` varchar(255) NOT NULL,  `pad` varchar(255) NOT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciCreated new table test_db._ptosc_new OK.

复制代码

对新表_ptosc_new增加一列 a

Altering new table...ALTER TABLE `test_db`.`_ptosc_new` ADD COLUMN a INTAltered `test_db`.`_ptosc_new` OK.

复制代码

创建三个触发器 DELETE、UPDATE、INSERT

2024-04-10T14:53:52 Creating triggers...-----------------------------------------------------------Event : DELETE Name  : pt_osc_test_db_ptosc_del SQL   : CREATE TRIGGER `pt_osc_test_db_ptosc_del` AFTER DELETE ON `test_db`.`ptosc` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test_db`.`_ptosc_new` WHERE `test_db`.`_ptosc_new`.`id` <=> OLD.`id`; END  Suffix: del Time  : AFTER ----------------------------------------------------------------------------------------------------------------------Event : UPDATE Name  : pt_osc_test_db_ptosc_upd SQL   : CREATE TRIGGER `pt_osc_test_db_ptosc_upd` AFTER UPDATE ON `test_db`.`ptosc` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; DELETE IGNORE FROM `test_db`.`_ptosc_new` WHERE !(OLD.`id` <=> NEW.`id`) AND `test_db`.`_ptosc_new`.`id` <=> OLD.`id`; REPLACE INTO `test_db`.`_ptosc_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`); END  Suffix: upd Time  : AFTER ----------------------------------------------------------------------------------------------------------------------Event : INSERT Name  : pt_osc_test_db_ptosc_ins SQL   : CREATE TRIGGER `pt_osc_test_db_ptosc_ins` AFTER INSERT ON `test_db`.`ptosc` FOR EACH ROW BEGIN DECLARE CONTINUE HANDLER FOR 1146 begin end; REPLACE INTO `test_db`.`_ptosc_new` (`id`, `k`, `c`, `pad`) VALUES (NEW.`id`, NEW.`k`, NEW.`c`, NEW.`pad`);END  Suffix: ins Time  : AFTER -----------------------------------------------------------2024-04-10T14:53:52 Created triggers OK.

复制代码

拷贝旧表数据到新表

2024-04-10T14:53:52 Copying approximately 9861 rows...INSERT LOW_PRIORITY IGNORE INTO `test_db`.`_ptosc_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `test_db`.`ptosc` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) LOCK IN SHARE MODE /*pt-online-schema-change 214419 copy nibble*/SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `test_db`.`ptosc` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/2024-04-10T14:53:52 Copied rows OK.

复制代码

分析新表，并交换新旧表，最后删除旧表

2024-04-10T14:53:52 Analyzing new table...2024-04-10T14:53:52 Swapping tables...RENAME TABLE `test_db`.`ptosc` TO `test_db`.`_ptosc_old`, `test_db`.`_ptosc_new` TO `test_db`.`ptosc`2024-04-10T14:53:52 Swapped original and new tables OK.2024-04-10T14:53:52 Dropping old table...DROP TABLE IF EXISTS `test_db`.`_ptosc_old`2024-04-10T14:53:52 Dropped old table `test_db`.`_ptosc_old` OK.

复制代码

删除触发器，完成所有操作

2024-04-10T14:53:52 Dropping triggers...DROP TRIGGER IF EXISTS `test_db`.`pt_osc_test_db_ptosc_del`DROP TRIGGER IF EXISTS `test_db`.`pt_osc_test_db_ptosc_upd`DROP TRIGGER IF EXISTS `test_db`.`pt_osc_test_db_ptosc_ins`2024-04-10T14:53:52 Dropped triggers OK.Successfully altered `test_db`.`ptosc`.

复制代码

修改字符集

将表 ptosc 的 c 字段的字符集修改为 utf8mb4

$ pt-online-schema-change --host=localhost --user=root --ask-pass --alter "modify column c varchar(255) character set utf8mb4" D=test_db,t=ptosc --alter-foreign-keys-method=auto --execute

复制代码

这里设置了一个--alter-foreign-keys-method用于设置外键约束的处理方法，设置为auto就是自动选择处理方法

删除一列

$ pt-online-schema-change --host=localhost --user=root --ask-pass --alter "drop column c" D=test_db,t=ptosc --alter-foreign-keys-method=auto --execute

复制代码

删除外键

$ pt-online-schema-change --host=localhost --user=root --ask-pass --alter "drop foreign key _dept_emp_ibfk_1" D=test_db,t=ptosc --alter-foreign-keys-method=auto --execute

复制代码

注意事项

在使用该工具时，需要注意以下几点：

工具在运行过程中，避免对原表进行更改，可能会出现数据不一致
使用该工具前要充分测试
最好在业务低峰时候操作

pt-show-grants

概要

显示当前数据库中所有用户的授权情况，并以 GRANT 语句现实，方便复制到其他数据库上执行

用法

pt-show-grants [OPTIONS] [DSN]

选项

最佳实践

只显示 GreatSQL 用户的授权信息

$ pt-show-grants --host=localhost --user=root --ask-pass --only=GreatSQL

复制代码

输出如下

-- Grants dumped by pt-show-grants-- Dumped from server Localhost via UNIX socket, MySQL 8.0.32-25 at 2024-04-16 13:43:25-- Grants for 'GreatSQL'@'%'CREATE USER IF NOT EXISTS `GreatSQL`@`%`;ALTER USER `GreatSQL`@`%` IDENTIFIED WITH 'caching_sha2_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;GRANT ACCESS_PROCFS,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `GreatSQL`@`%` WITH GRANT OPTION;GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `GreatSQL`@`%` WITH GRANT OPTION;

复制代码

该工具会展示 GreatSQL 用户的所有权限，用户可以直接复制输出的结果，方便粘贴到其他数据库上执行

pt-upgrade

概要

用于验证不同数据库上的 SQL 语句查询结果是否相同，有助于确定升级（或降级）到新版本的 MySQL/GreatSQL 是否安全。

用法

pt-upgrade [OPTIONS] LOGS|RESULTS DSN [DSN]

查询差异的判断主要来源以下几点：

Row count，返回的行数是否相同
Row data，返回的行数据是否相同
Warnings，返回的警告是否相同
Query time，查询的相差时间
Query errors，查询错误，在一个数据库出错，则会报告为“查询错误”
SQL errors，SQL 错误，在两个数据库都出错，则会报告为“SQL 错误”

选项

最佳实践

只需提供两个实例的连接信息和文件名，直接比较一个文件中的 SQL 在两个实例中的执行效果

$ pt-upgrade h=host1 h=host2 slow.log

复制代码

使用--type参数轻松指定文件类型，支持慢日志、通用日志、二进制日志（经 mysqlbinlog 解析）、原始 SQL 语句和 tcpdump。若未指定，则默认为慢日志。

接下来做个简单的示范，创建一个pt_upgrade_test.sql包含了若干条测试语句

$ vim /tmp/pt_upgrade_test.sqlSELECT NOW();SELECT DATEDIFF('2023-10-23', '2023-01-01') AS days_diff;SELECT 5 + 3 * 2 AS result;SELECT DATABASE();SHOW GRANTS FOR CURRENT_USER();

复制代码

下面来看看这些语句的执行情况

本示例采用两个相同数据库进行演示。但请注意，在实际应用中，会对比两个不同数据库的相同 SQL 输出差异，因此，使用相同数据库进行对比并无实际意义。

$ pt-upgrade h=localhost,P=3306,u=root,--ask-pass h=localhost,P=3306,u=root,--ask-pass --type rawlog /tmp/pt_upgrade_test.sql --no-read-only
#-----------------------------------------------------------------------# Logs#-----------------------------------------------------------------------File: /tmp/pt_upgrade_test.sqlSize: 151#-----------------------------------------------------------------------# Hosts#-----------------------------------------------------------------------host1:  DSN:       h=localhost,P=3306  hostname:  myarch  MySQL:     GreatSQL, Release 25, Revision 79f57097e3f 8.0.32  host2:  DSN:       h=localhost,P=3306  hostname:  myarch  MySQL:     GreatSQL, Release 25, Revision 79f57097e3f 8.0.32#-----------------------------------------------------------------------# Stats#-----------------------------------------------------------------------failed_queries        0not_select            0queries_filtered      0queries_no_diffs      5queries_read          5queries_with_diffs    0queries_with_errors   0

复制代码

failed_queries：查询因为某种原因而失败的 SQL 个数
not_select：非 SELECT 类型的查询被处理的 SQL 个数
queries_filtered：查询因为某种过滤条件而被排除的 SQL 个数
queries_no_diffs：执行计划没有差异的 SQL 个数
queries_read：总共读取了几个查询进行比较
queries_with_diffs：在两个数据库上的执行计划存在差异的 SQL 个数
queries_with_errors：在执行过程中遇到错误的 SQL 个数

如果 queries_with_diffs 的值不为 0，就要着重检查下差异了

本文完 :) 下章节将介绍 Percona Toolkit 神器全攻略（复制类）

发布于: 刚刚阅读数: 3

原文链接:【http://xie.infoq.cn/article/52e1ba66b1bc77e92622bc9af】。文章转载请联系作者。

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

发布

暂无评论

创作场景

Percona Toolkit 神器全攻略（开发类）

Percona Toolkit 神器全攻略（开发类）

开发类

pt-duplicate-key-checker

概要

选项

最佳实践

pt-online-schema-change

概要

选项

最佳实践

添加一列

修改字符集

删除一列

删除外键

注意事项

pt-show-grants

概要

选项

最佳实践

pt-upgrade

概要

选项

最佳实践

GreatSQL

评论