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

2024-06-04 16:48 abce 阅读(178) 评论(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"，因为工具被指示不执行表交换。

1 2	`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.正式执行

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

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