pt-online-schema-change 在线无缝修改表
2024-06-04 16:48 abce 阅读(74) 评论(0) 编辑 收藏 举报pt-online-schema-change 支持在不妨碍读写操作的情况下修改表的结构。这种方法允许管理员无缝地修改表，同时保持数据的完整性，并最大限度地减少中断。为了完成在线修改的目标，pt-online-schema-change 工具遵循定义明确的步骤：
·创建空副本：
pt-online-schema-change 会为将要修改的目标表创建一个空副本。根据需要修改空表，而不会影响原始表或正在进行的操作。
·行传输和同步：
空表准备就绪后，pt-online-schema-change 会将原始表中的行复制到修改后的表中。在此过程中，对原始表数据所做的任何修改都会与新表同步。
·替换原始表：
在所有行成功转移后，pt-online-schema-change 会用修改后的表替换原始表。
以上步骤可确保从旧表到新表的无缝过渡。详细的步骤如下所示：
Step 1: Create the new table.
Step 2: Alter the new, empty table.
This should be very quick, or die if the user specified a bad alter statement.
Step 3: Create the triggers to capture changes on the original table and apply them to the new table.
Step 4: Copy rows.
Step 5: Rename tables: orig -> old, new -> orig
Step 6: Update foreign key constraints if there are child tables.
Step 7: Drop the old table.
DROP TABLE IF EXISTS `_xx_old` DROP TRIGGER IF EXISTS`pt_osc_xx_xx_del`;
DROP TRIGGER IF EXISTS`pt_osc_xx_xx_upd`;
DROP TRIGGER IF EXISTS`pt_osc_xx_xx_ins`;
done
优点和注意事项
使用 pt-online-schema-change 在线执行表修改有几个优点：
·停机时间最短： 在修改过程中，应用程序可以继续访问原始表。
·数据一致性： 同步机制可确保复制过程中所做的修改准确反映在新表中。
·可控： 循序渐进的过程允许管理员监控和验证每个阶段，从而降低出错风险。
不过，管理员还应考虑以下因素：
·磁盘空间要求： 需要足够的空间来容纳空副本和修改期间的任何临时数据。
·带有外键的表： 在表包含外键的情况下使用该工具存在风险。在开始对包含外键的表进行修改之前，务必查阅工具文档，熟悉可用选项并评估相关风险。
·–no-swap-tables选项和外键： 如果表具有与子表相关联的外键，则该选项不起作用。
如果在 pt-osc 中选择 "rebuild_constraints"方法作为修改外键方法，并使用-no-swap-tables 选项，工具可能会遇到类似下面的错误。在这种情况下，不会出现 "old_tbl"，因为工具被指示不执行表交换。
`db_name`.`table_name`: *** rows; can use rebuild_constraints Error updating foreign key constraints: I need a old_tbl argument at /usr/bin/pt-online-schema-change line 11119.
具体错误表示 "old_tbl" 丢失或无法找到。出现这种情况是因为在 alter 过程中，"rebuild_constraints "方法依赖于 "old_tbl"的存在。
交换前的验证： 如果在 pt-online-schema-change 中选择使用-no-swap-tables、-no-drop-old-table、-no-drop-new-table 和-no-drop-triggers 选项，则必须遵循特定的手动交换流程。在进行手动交换之前，强烈建议验证 pt-online-schema-change 日志，以确保修改过程已成功完成。此外，建议比较表大小和记录计数，以确认它们是否匹配。
预先检查
1.外键
使用 pt-online-schema-change 工具执行在线模式修改时，必须考虑数据库中存在的外键。通过 pt-online-schema-change 中的 -alter-foreign-keys-method 选项，可以指定在修改过程中如何处理外键。
select table_schema, table_name, column_name, constraint_name, referenced_table_name, referenced_column_name from information_schema.key_column_usage where referenced_table_name = 'table_name';
外键检查非常重要：pt-online-schema-change 中的 -alter-foreign-keys-method 选项允许你指定一种方法，以确保安全，并在修改过程中尽量减少表元数据锁，同时考虑到引用表的大小。通过仔细选择适当的方法（auto、rebuild_constraints、drop_swap），可以控制外键的处理方式，同时确保表的安全性和完整性。
如果是 rebuild_constraints，会按照以下步骤删除和重新添加外键约束：
·将当前表t1重命名为t1_old，外键指向t1_old
·将新表重命名成t1，比较将_t1_new重命名成t1，这时候t1上没有外键引用
·alter 子表，将外键约束指向t1
如果是 drop_swap，会按照以下步骤删除和重新添加外键约束(这个过程存在一定的风险)：
·禁用当前会话的外键检查(FOREIGN_KEY_CHECKS=0)
·删除原先的表
·将新表重命名成t1，比较将_t1_new重命名成t1
如果是 auto，会根据子表的大小，在 "rebuild_constraints"和"drop_swap"自动选择
2.磁盘空间要求： 表大小的两倍以上，必须容纳表数据副本和修改期间的任何临时数据。确保复制拓扑或群集中的所有服务器都有足够的磁盘空间。
3.主键或唯一索引：在大多数情况下，使用 pt-online-schema-change 工具时，必须在表上定义主键或唯一索引。这一要求是必要的，因为该工具会创建一个 DELETE 触发器，以确保新表在整个修改过程中保持更新。
4.执行前模拟运
pt-online-schema-change中的-dry-run选项提供了一种在不修改表结构的情况下模拟修改过程的方法。使用该选项时，将执行模式修改的模拟运行，让您可以预览潜在的修改并评估其影响，然后再将其应用到实际表中。一旦确认一切正常，可以继续修改，就可以用 -execute 替换 -dry-run 选项，启动模式修改。
测试过程
1.源表
>show create table abce\G
*************************** 1. row ***************************
Table: abce
Create Table: CREATE TABLE `abce` (
`id` bigint NOT NULL AUTO_INCREMENT,
...
`tt_day` date DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`product` bigint DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
2.执行--dry-run测试分析
# ./pt-online-schema-change --user=root --password='xxxxxx' --socket=/data/mysql_data/mysql.sock P=3306,D=myabc,t=abce --no-check-unique-key-change --no-check-alter --alter="
modify column tt_day date not null,
drop index product_day,
drop primary key,
add primary key(id,tt_day)
PARTITION BY RANGE COLUMNS(tt_day)
(PARTITION p_202312 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION p_202401 VALUES LESS THAN ('2024-02-01') ENGINE = InnoDB,
PARTITION p_202402 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
PARTITION p_202403 VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB,
PARTITION p_202404 VALUES LESS THAN ('2024-05-01') ENGINE = InnoDB,
PARTITION p_202405 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
PARTITION p_202406 VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB,
PARTITION p_202407 VALUES LESS THAN ('2024-08-01') ENGINE = InnoDB,
PARTITION p_202408 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
PARTITION p_202409 VALUES LESS THAN ('2024-10-01') ENGINE = InnoDB,
PARTITION p_202410 VALUES LESS THAN ('2024-11-01') ENGINE = InnoDB,
PARTITION p_202411 VALUES LESS THAN ('2024-12-01') ENGINE = InnoDB,
PARTITION p_202412 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION p_202501 VALUES LESS THAN ('2025-02-01') ENGINE = InnoDB,
PARTITION p_202502 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION p_202503 VALUES LESS THAN ('2025-04-01') ENGINE = InnoDB,
PARTITION p_202504 VALUES LESS THAN ('2025-05-01') ENGINE = InnoDB,
PARTITION p_202505 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB,
PARTITION p_202506 VALUES LESS THAN ('2025-07-01') ENGINE = InnoDB,
PARTITION p_202507 VALUES LESS THAN ('2025-08-01') ENGINE = InnoDB,
PARTITION p_202508 VALUES LESS THAN ('2025-09-01') ENGINE = InnoDB,
PARTITION p_202509 VALUES LESS THAN ('2025-10-01') ENGINE = InnoDB,
PARTITION p_202510 VALUES LESS THAN ('2025-11-01') ENGINE = InnoDB,
PARTITION p_202511 VALUES LESS THAN ('2025-12-01') ENGINE = InnoDB,
PARTITION p_202512 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB
)
" --dry-run
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
Starting a dry run. `myabc`.`abce` will not be altered. Specify --execute instead of --dry-run to alter the table.
Creating new table...
Created new table myabc._abce_new OK.
Altering new table...
Altered `myabc`.`_abce_new` OK.
Not creating triggers because this is a dry run.
Not copying rows because this is a dry run.
Not swapping tables because this is a dry run.
Not dropping old table because this is a dry run.
Not dropping triggers because this is a dry run.
2024-06-03T14:06:01 Dropping new table...
2024-06-03T14:06:01 Dropped new table OK.
Dry run complete. `myabc`.`abce` was not altered.
3.正式执行
# ./pt-online-schema-change --user=root --password='xxxxxx' --socket=/mysql_data/mysql.sock P=3306,D=myabc,t=abce --no-check-unique-key-change --no-check-alter --alter="
modify column tt_day date not null,
drop index product_day,
drop primary key,
add primary key(id,tt_day)
PARTITION BY RANGE COLUMNS(tt_day)
(PARTITION p_202312 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
PARTITION p_202401 VALUES LESS THAN ('2024-02-01') ENGINE = InnoDB,
PARTITION p_202402 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
PARTITION p_202403 VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB,
PARTITION p_202404 VALUES LESS THAN ('2024-05-01') ENGINE = InnoDB,
PARTITION p_202405 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
PARTITION p_202406 VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB,
PARTITION p_202407 VALUES LESS THAN ('2024-08-01') ENGINE = InnoDB,
PARTITION p_202408 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
PARTITION p_202409 VALUES LESS THAN ('2024-10-01') ENGINE = InnoDB,
PARTITION p_202410 VALUES LESS THAN ('2024-11-01') ENGINE = InnoDB,
PARTITION p_202411 VALUES LESS THAN ('2024-12-01') ENGINE = InnoDB,
PARTITION p_202412 VALUES LESS THAN ('2025-01-01') ENGINE = InnoDB,
PARTITION p_202501 VALUES LESS THAN ('2025-02-01') ENGINE = InnoDB,
PARTITION p_202502 VALUES LESS THAN ('2025-03-01') ENGINE = InnoDB,
PARTITION p_202503 VALUES LESS THAN ('2025-04-01') ENGINE = InnoDB,
PARTITION p_202504 VALUES LESS THAN ('2025-05-01') ENGINE = InnoDB,
PARTITION p_202505 VALUES LESS THAN ('2025-06-01') ENGINE = InnoDB,
PARTITION p_202506 VALUES LESS THAN ('2025-07-01') ENGINE = InnoDB,
PARTITION p_202507 VALUES LESS THAN ('2025-08-01') ENGINE = InnoDB,
PARTITION p_202508 VALUES LESS THAN ('2025-09-01') ENGINE = InnoDB,
PARTITION p_202509 VALUES LESS THAN ('2025-10-01') ENGINE = InnoDB,
PARTITION p_202510 VALUES LESS THAN ('2025-11-01') ENGINE = InnoDB,
PARTITION p_202511 VALUES LESS THAN ('2025-12-01') ENGINE = InnoDB,
PARTITION p_202512 VALUES LESS THAN ('2026-01-01') ENGINE = InnoDB
)
" --execute
No slaves found. See --recursion-method if host db-p 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 `myabc`.`abce`...
Creating new table...
Created new table myabc._abce_new OK.
Altering new table...
Altered `myabc`.`_abce_new` OK.
2024-06-03T14:06:11 Creating triggers...
2024-06-03T14:06:11 Created triggers OK.
2024-06-03T14:06:11 Copying approximately 212865999 rows...
Copying `myabc`.`abce`: 1% 48:40 remain
Copying `myabc`.`abce`: 2% 47:52 remain
Copying `myabc`.`abce`: 3% 47:27 remain
Copying `myabc`.`abce`: 4% 47:03 remain
Copying `myabc`.`abce`: 5% 46:28 remain
Copying `myabc`.`abce`: 6% 45:49 remain
Copying `myabc`.`abce`: 7% 45:22 remain
Copying `myabc`.`abce`: 8% 44:54 remain
Copying `myabc`.`abce`: 9% 44:25 remain
Copying `myabc`.`abce`: 10% 43:56 remain
Copying `myabc`.`abce`: 11% 43:27 remain
Copying `myabc`.`abce`: 12% 43:00 remain
Copying `myabc`.`abce`: 13% 42:32 remain
Copying `myabc`.`abce`: 14% 42:04 remain
Copying `myabc`.`abce`: 15% 41:40 remain
Copying `myabc`.`abce`: 16% 41:14 remain
Copying `myabc`.`abce`: 17% 40:44 remain
Copying `myabc`.`abce`: 18% 40:19 remain
Copying `myabc`.`abce`: 19% 39:53 remain
Copying `myabc`.`abce`: 20% 39:24 remain
Copying `myabc`.`abce`: 21% 38:56 remain
Copying `myabc`.`abce`: 22% 38:25 remain
Copying `myabc`.`abce`: 23% 37:59 remain
Copying `myabc`.`abce`: 24% 37:27 remain
Copying `myabc`.`abce`: 25% 36:54 remain
Copying `myabc`.`abce`: 26% 36:24 remain
Copying `myabc`.`abce`: 27% 35:53 remain
Copying `myabc`.`abce`: 28% 35:22 remain
Copying `myabc`.`abce`: 29% 34:50 remain
Copying `myabc`.`abce`: 30% 34:20 remain
Copying `myabc`.`abce`: 31% 33:50 remain
Copying `myabc`.`abce`: 32% 33:18 remain
Copying `myabc`.`abce`: 33% 32:48 remain
Copying `myabc`.`abce`: 34% 32:19 remain
Copying `myabc`.`abce`: 35% 31:50 remain
Copying `myabc`.`abce`: 36% 31:20 remain
Copying `myabc`.`abce`: 37% 30:52 remain
Copying `myabc`.`abce`: 38% 30:24 remain
Copying `myabc`.`abce`: 39% 29:54 remain
Copying `myabc`.`abce`: 40% 29:24 remain
Copying `myabc`.`abce`: 41% 28:56 remain
Copying `myabc`.`abce`: 42% 28:26 remain
Copying `myabc`.`abce`: 43% 27:53 remain
Copying `myabc`.`abce`: 44% 27:23 remain
Copying `myabc`.`abce`: 45% 26:53 remain
Copying `myabc`.`abce`: 46% 26:23 remain
Copying `myabc`.`abce`: 47% 25:53 remain
Copying `myabc`.`abce`: 48% 25:23 remain
Copying `myabc`.`abce`: 49% 24:53 remain
Copying `myabc`.`abce`: 50% 24:24 remain
Copying `myabc`.`abce`: 51% 23:52 remain
Copying `myabc`.`abce`: 52% 23:22 remain
Copying `myabc`.`abce`: 53% 22:53 remain
Copying `myabc`.`abce`: 54% 22:24 remain
Copying `myabc`.`abce`: 55% 21:55 remain
Copying `myabc`.`abce`: 56% 21:26 remain
Copying `myabc`.`abce`: 57% 20:56 remain
Copying `myabc`.`abce`: 58% 20:26 remain
Copying `myabc`.`abce`: 59% 19:57 remain
Copying `myabc`.`abce`: 60% 19:27 remain
Copying `myabc`.`abce`: 61% 18:57 remain
Copying `myabc`.`abce`: 62% 18:26 remain
Copying `myabc`.`abce`: 63% 17:55 remain
Copying `myabc`.`abce`: 64% 17:25 remain
Copying `myabc`.`abce`: 65% 16:55 remain
Copying `myabc`.`abce`: 66% 16:25 remain
Copying `myabc`.`abce`: 67% 15:54 remain
Copying `myabc`.`abce`: 68% 15:25 remain
Copying `myabc`.`abce`: 69% 14:55 remain
Copying `myabc`.`abce`: 70% 14:24 remain
Copying `myabc`.`abce`: 71% 13:54 remain
Copying `myabc`.`abce`: 72% 13:24 remain
Copying `myabc`.`abce`: 73% 12:55 remain
Copying `myabc`.`abce`: 74% 12:26 remain
Copying `myabc`.`abce`: 75% 11:56 remain
Copying `myabc`.`abce`: 76% 11:26 remain
Copying `myabc`.`abce`: 77% 10:57 remain
Copying `myabc`.`abce`: 78% 10:27 remain
Copying `myabc`.`abce`: 79% 09:58 remain
Copying `myabc`.`abce`: 80% 09:28 remain
Copying `myabc`.`abce`: 81% 08:58 remain
Copying `myabc`.`abce`: 82% 08:27 remain
Copying `myabc`.`abce`: 83% 07:58 remain
Copying `myabc`.`abce`: 84% 07:27 remain
Copying `myabc`.`abce`: 85% 06:57 remain
Copying `myabc`.`abce`: 86% 06:27 remain
Copying `myabc`.`abce`: 87% 05:57 remain
Copying `myabc`.`abce`: 88% 05:27 remain
Copying `myabc`.`abce`: 89% 04:57 remain
Copying `myabc`.`abce`: 90% 04:27 remain
Copying `myabc`.`abce`: 92% 03:57 remain
Copying `myabc`.`abce`: 93% 03:27 remain
Copying `myabc`.`abce`: 94% 02:57 remain
Copying `myabc`.`abce`: 94% 02:28 remain
Copying `myabc`.`abce`: 95% 01:59 remain
Copying `myabc`.`abce`: 96% 01:31 remain
Copying `myabc`.`abce`: 97% 01:01 remain
Copying `myabc`.`abce`: 98% 00:32 remain
Copying `myabc`.`abce`: 99% 00:03 remain
2024-06-03T14:57:48 Copied rows OK.
2024-06-03T14:57:48 Analyzing new table...
2024-06-03T14:57:49 Swapping tables...
2024-06-03T14:57:49 Swapped original and new tables OK.
2024-06-03T14:57:49 Dropping old table...
2024-06-03T14:57:49 Dropped old table `myabc`.`_abce_old` OK.
2024-06-03T14:57:49 Dropping triggers...
2024-06-03T14:57:49 Dropped triggers OK.
Successfully altered `myabc`.`abce`.
