标签：abce 01 pt PARTITION remain Copying online change myabc

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`.

标签：abce,01,pt,PARTITION,remain,Copying,online,change,myabc
From： https://www.cnblogs.com/abclife/p/18229010

pt-online-schema-change 在线无缝修改表