从 Percona Toolkit 3.6.0 开始,如果 pt-online-schema-change 被中断,你可以重新恢复它。
要重启任务,需要知道它在哪里失败了。这就是为什么第一个必须使用的选项是 -history。它指示 pt-online-schema-change 将进度存储在历史表中。历史表的默认名称是 percona.pt_osc_history。你可以使用选项 -history-table 来覆盖这个名称。历史表结构如下:
create table pt_osc_history ( job_id int not null auto_increment, db char(64) not null, tbl char(64) not null, new_table_name char(64) default null, altr text not null, args text not null, lower_boundary text, upper_boundary text, done enum('yes','no') not null default 'no', ts timestamp not null default current_timestamp on update current_timestamp, primary key (job_id) ) engine=innodb default charset=utf8mb4;
其中:
·job_id:是 pt-online-schema-change 运行的唯一标识符
·db:数据库名称
·tbl:表的名称
·new_table_name:pt-online-schema-change 为执行任务而创建的表副本的名称
·altr:alter table命令的 -alter 参数
·args:传递给 pt-online-schema-change 命令的其他参数
·lower_boundary:最新处理的 chunk 的下边界
·upper_boundary:最新处理的 chunk 的上边界
·done:任务是否完成的指示符
·ts:任务条目更新时记录的时间戳。实际上,这就是处理完最新数据块的时间戳。
如果表索引包含二进制列,如 BLOB 或 BINARY,请使用选项 -binary-index 创建历史表。在这种情况下,lower_boundary 和 upper_boundary 将使用 BLOB 数据类型,而不是 TEXT。pt-table-checksum中的-replicate表也采用了同样的思路。
一旦使用 -history 选项运行 pt-online-schema-change,它就会创建一条包含作业详细信息的记录。如果作业失败,你可以很容易地在该工具输出或历史表中找到它的唯一标识符,并重新启动它。
下面是几个 pt-online-schema-change 运行后 percona.pt_osc_history 表内容的示例:
mysql> select * from percona.pt_osc_historyG *************************** 1. row *************************** job_id: 1 db: test tbl: pt1717 new_table_name: __pt1717_new altr: engine=INNODB args: {"history":1,"execute":1,"progress":["time","5"],"drop-new-table":0,"max-lag":"10","chunk-size":"10","alter":"engine=INNODB","drop-triggers":0} lower_boundary: 9991 upper_boundary: 10000 done: yes ts: 2024-06-05 13:32:09 *************************** 2. row *************************** job_id: 2 db: test tbl: pt1717 new_table_name: __pt1717_new altr: engine=INNODB args: {"alter":"engine=INNODB","max-lag":"10","drop-new-table":0,"drop-triggers":0,"progress":["time","5"],"history":1,"chunk-size":"10","execute":1} lower_boundary: NULL upper_boundary: NULL done: no ts: 2024-06-05 13:32:09 *************************** 3. row *************************** job_id: 3 db: test tbl: pt1717 new_table_name: __pt1717_new altr: engine=INNODB args: {"progress":["time","5"],"execute":1,"chunk-size":"10","history":1,"drop-new-table":0,"alter":"engine=INNODB","max-lag":"10","drop-triggers":0} lower_boundary: NULL upper_boundary: NULL done: no ts: 2024-06-05 13:32:09 *************************** 4. row *************************** job_id: 4 db: test tbl: pt1717 new_table_name: __pt1717_new altr: engine=INNODB args: {"drop-triggers":0,"alter":"engine=INNODB","progress":["time","5"],"history":1,"chunk-size":"10","drop-new-table":0,"max-lag":"10","execute":1} lower_boundary: 4901 upper_boundary: 4910 done: no ts: 2024-06-05 13:32:12 4 rows in set (0,00 sec)
其中:
任务1成功结束了。
done: yes
任务2和任务2还没有启动:
lower_boundary: NULL upper_boundary: NULL done: no
任务3被中断了:
lower_boundary: 4901 upper_boundary: 4910 done: no
要重新启动任务,请运行 pt-online-schema-change,并选择 -resume=ID_OF_YOUR_PREVIOUSLY_FAILED_JOB。工具会检查之前创建的表副本及其触发器是否还在,并从之前失败的数据块启动作业。
$ ./bin/pt-online-schema-change h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=pt1717 --execute --chunk-size 10 --max-lag 10 --alter 'engine=INNODB' --progress time,5 --no-drop-new-table --no-drop-triggers --history --resume=4 2024-06-05T13:54:11 Job 5 started. Found 2 slaves: s76 -> 127.0.0.1:12346 s76 -> 127.0.0.1:12347 Will check slave lag on: s76 -> 127.0.0.1:12346 s76 -> 127.0.0.1:12347 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 `test`.`pt1717`... Altering new table... Altered `test`.`__pt1717_new` OK. 2024-06-05T13:54:11 Creating triggers... 2024-06-05T13:54:11 Created triggers OK. 2024-06-05T13:54:11 Copying approximately 9956 rows... 2024-06-05T13:54:14 Job 5 finished successfully. 2024-06-05T13:54:14 Copied rows OK. 2024-06-05T13:54:14 Analyzing new table... 2024-06-05T13:54:14 Swapping tables... 2024-06-05T13:54:14 Swapped original and new tables OK. Not dropping old table because --no-drop-triggers was specified. Not dropping triggers because --no-drop-triggers was specified. To drop the triggers, execute: DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt1717_del` DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt1717_upd` DROP TRIGGER IF EXISTS `test`.`pt_osc_test_pt1717_ins` Successfully altered `test`.`pt1717`.
可以同时使用 -history 和 -resume 选项,也可以不使用。需要注意的是,如果不使用选项 -history,一旦任务再次失败,就无法重新启动。
选项 -resume 的另一个前提条件是 -no-drop-new-table 和 -no-drop-triggers。如果删除了新表和触发器,pt-online-schema-change 就无法重新启动任务,因为没有先前复制的数据。
$ ./bin/pt-online-schema-change h=127.0.0.1,P=12345,u=msandbox,p=msandbox,D=test,t=pt1717 --execute --chunk-size 10 --max-lag 10 --alter 'engine=INNODB' --progress time,5 --history --resume=9 2024-06-05T13:58:10 Job 10 started. Found 1 slaves: s76 -> 127.0.0.1:12346 Will check slave lag on: s76 -> 127.0.0.1:12346 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 `test`.`pt1717`... New table `test`.`___pt1717_new` not found, restart operation from scratch `test`.`pt1717` was not altered. History saved. Job id: 10
总结
如果想在失败后恢复 pt-online-schema-change,可使用选项 -history -no-drop-new-table -no-drop-triggers 来恢复。如果任务失败,可以通过提供任务 ID 作为选项 -resume 的参数来恢复任务。你可以在 pt-online-schema-change 输出和历史表中找到失败任务的 ID。
标签:10,pt1717,drop,pt,online,table,new,change From: https://www.cnblogs.com/abclife/p/18252179