pt-online-schema-change 完成部分数据归档和 schema 更改

从 Percona Toolkit 3.6.0 开始，pt-online-schema-change 支持 -where 选项，因此不仅可以实时更改表定义，还可以只复制满足特定条件的行。

在更改表定义时，可能并不需要复制所有数据。例如，如果表太大，而你只需要最近的数据。

要完成这项工作，pt-online-schema-change 要加上参数选项 -where='YOUR WHERE CONDITION' -no-drop-new-table -no-swap-tables。

 

以下是测试过程：

查看表中总的记录数，以及符合特定条件的记录数

?

1 2 3 4 5 6 7 8 9 10 11 12 13

> select count(*) from abce; +-----------+ | count(*) | +-----------+ | 134001309 | +-----------+   > select count(*) from abce where createTime > '2024-06-01 00:00:00'; +----------+ | count(*) | +----------+ | 637678 | +----------+

执行更改操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

./pt-online-schema-change --user=root --password='XXX' --socket=/mysql_data/mysql.sock P=3306,D=myabc,t=abce --alter="key_block_size=4" --where="createTime > '2024-06-01 00:00:00'" --execute --no-swap-tables --no-drop-new-table --new-table-name=abce_2024 No slaves found. See --recursion-method if host xdb 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_2024 OK. Altering new table... Altered `myabc`.`abce_2024` OK. 2024-06-19T16:34:25 Creating triggers... 2024-06-19T16:34:25 Created triggers OK. 2024-06-19T16:36:12 Copying approximately 82968416 rows... Copying `myabc`.`abce`: 0% 102+17:58:33 remain Copying `myabc`.`abce`: 0% 09:00:43 remain 2024-06-19T16:37:06 Copied rows OK. Not dropping old table because --no-swap-tables was specified. 2024-06-19T16:37:06 Dropping triggers... 2024-06-19T16:37:06 Dropped triggers OK. Not dropping the new table `myabc`.`abce_2024` because --no-drop-new-table was specified. To drop the new table, execute: DROP TABLE IF EXISTS `myabc`.`abce_2024`; Successfully altered `myabc`.`abce`.

上面的命令将创建一个新表 abce_2024，并保留原表不动。

?

1 2 3 4 5 6 7 8 9 10 11 12

> select count(*) from abce_2024 where createTime > '2024-06-01 00:00:00'; +----------+ | count(*) | +----------+ | 637678 | +----------+ > select count(*) from abce; +-----------+ | count(*) | +-----------+ | 134001309 | +-----------+

 

安全选项 –no-drop-new-table –no-swap-tables 用来保护原表中的数据。如果使用选项 -where 启动 pt-online-schema-change，但省略了 –no-drop-new-table –no-swap-tables 这些安全选项，它将拒绝执行。

?

1 2

./pt-online-schema-change --user=root --password='XXX' --socket=/mysql_data/mysql.sock P=3306,D=myabc,t=abce --alter="key_block_size=4" --where="createTime > '2024-06-01 00:00:00'" --execute --new-table-name=abce_2024 Using option --where together with --drop-new-table and --swap-tables may lead to data loss, therefore this operation is only allowed if option --force also specified. Aborting.

–drop-new-table 和 –swap-tables 选项可能会丢失数据，除非是用户知情的情况下加上参数 --force。

选项 -where 对 pt-online-schema-change 为拷贝新插入或更新的记录而创建的触发器没有任何影响，但是不建议在可能受正在进行的查询影响的条件下使用该选项。

 

以下是使用 --force选项的测试结果

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

./pt-online-schema-change --user=root --password='XXX' --socket=/mysql_data/mysql.sock  P=3306,D=myabc,t=abce --alter="key_block_size=4" --where="createTime > '2024-06-01 00:00:00'"  --execute --new-table-name=abce_2024 --force No slaves found.  See --recursion-method if host xdb 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_2024 OK. Altering new table... Altered `myabc`.`abce_2024` OK. 2024-06-19T16:48:18 Creating triggers... 2024-06-19T16:48:18 Created triggers OK. 2024-06-19T16:50:05 Copying approximately 82968416 rows... Copying `myabc`.`abce`:   0% 102+17:58:33 remain Copying `myabc`.`abce`:   0% 09:10:35 remain 2024-06-19T16:51:00 Copied rows OK. 2024-06-19T16:51:00 Analyzing new table... 2024-06-19T16:51:00 Swapping tables... 2024-06-19T16:51:00 Swapped original and new tables OK. 2024-06-19T16:51:00 Dropping old table... 2024-06-19T16:51:00 Dropped old table `myabc`.`_abce_old` OK. 2024-06-19T16:51:00 Dropping triggers... 2024-06-19T16:51:00 Dropped triggers OK. Successfully altered `myabc`.`abce`.   > select count(*) from abce; +----------+ | count(*) | +----------+ |   637678 | +----------+