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: name
redundant_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确认开始

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

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

# 下一行表示工具没有找到任何从服务器（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

2. 创建一张名为_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_ci
Created new table test_db._ptosc_new OK.

3. 对新表_ptosc_new增加一列 a

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

4. 创建三个触发器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.

5. 拷贝旧表数据到新表

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.

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

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.

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

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

注意事项

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

1. 工具在运行过程中，避免对原表进行更改，可能会出现数据不一致

2. 使用该工具前要充分测试

3. 最好在业务低峰时候操作

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]

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

1. Row count，返回的行数是否相同
2. Row data，返回的行数据是否相同
3. Warnings，返回的警告是否相同
4. Query time，查询的相差时间
5. Query errors，查询错误，在一个数据库出错，则会报告为“查询错误”
6. 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.sql
SELECT 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.sql
Size: 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        0
not_select            0
queries_filtered      0
queries_no_diffs      5
queries_read          5
queries_with_diffs    0
queries_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 神器全攻略（复制类）