How to pause ALTER using pt-online-schema-change
Well, first things first , to use pt-online-schema-change for OSC , we need to know what it does . To know more on how the tool works you can read through a blog explaining its working and its documentation. This blog is demonstration of pt-osc’s parameter --pause-file to pause the already running copy of rows from the table to the new table. We have table cost_customers with below structure.
| cost_customers | CREATE TABLE `cost_customers` (
`time` char(13) NOT NULL,
`customer` int(5) NOT NULL,
`cost` decimal(10,6) NOT NULL DEFAULT '0.000000',
`profit` decimal(10,6) NOT NULL DEFAULT '0.000000',
PRIMARY KEY (`time`,`customer`),
KEY `customer` (`customer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
And let’s say we want to modify the column profit . So , the use of --pause-file is , the parameter takes string as input and execution of the tool will be paused while the file specified by this parameter exists.
The command used for pt-osc is
pt-online-schema-change --execute
--pause-file=/home/siddhant/pt-osc_pause_file
--no-drop-old-table
--alter="modify column profit decimal(10,5) NOT NULL DEFAULT '0.00000'"
h=localhost,D=sidd_test,t=cost_customers
If the tool finds the file /home/siddhant/pt-osc_pause_file at the mentioned location , the tool will pause its execution and wait for default of 60 seconds to check again.Note the user should have read access to the directory. Here is it’s execution ,
[root@siddhant_2 ~]# pt-online-schema-change --execute --check-interval=2 --pause-file=/home/siddhant/pt-osc_pause_file --no-drop-old-table --alter="modify column profit decimal(10,7) NOT NULL DEFAULT '0.00000'" h=localhost,D=sidd_test,t=cost_customers
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
Altering `sidd_test`.`cost_customers`...
Creating new table...
Created new table sidd_test._cost_customers_new OK.
Altering new table...
Altered `sidd_test`.`_cost_customers_new` OK.
2017-05-24T08:26:46 Creating triggers...
2017-05-24T08:26:46 Created triggers OK.
2017-05-24T08:26:46 Copying approximately 72372 rows...
Sleeping 60 seconds because /home/siddhant/pt-osc_pause_file exists
Sleeping 60 seconds because /home/siddhant/pt-osc_pause_file exists
I created /home/siddhant/pt-osc_pause_file file when pt-osc started its execution.
As soon as pause-file was deleted
[root@siddhant_2 siddhant]# rm -rfv /home/siddhant/pt-osc_pause_file
removed `/home/siddhant/pt-osc_pause_file'
The execution continued and rows were copied to the new table and alter was completed.
2017-05-24T08:51:27 Copied rows OK.
2017-05-24T08:51:27 Analyzing new table...
2017-05-24T08:51:27 Swapping tables...
2017-05-24T08:51:28 Swapped original and new tables OK.
Not dropping old table because --no-drop-old-table was specified.
2017-05-24T08:51:28 Dropping triggers...
2017-05-24T08:51:28 Dropped triggers OK.
Successfully altered `sidd_test`.`cost_customers`