pt-online-schema-change 完成部分数据归档和 schema 更改
2024-06-25 08:58 abce 阅读(143) 评论(0) 编辑 收藏 举报从 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。
以下是测试过程：
查看表中总的记录数，以及符合特定条件的记录数
> select count(*) from abce; +-----------+ | count(*) | +-----------+ | 134001309 | +-----------+ > select count(*) from abce where createTime > '2024-06-01 00:00:00'; +----------+ | count(*) | +----------+ | 637678 | +----------+
执行更改操作
./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，并保留原表不动。
> 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 这些安全选项，它将拒绝执行。
./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选项的测试结果
./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 | +----------+
