online schema change

shadow_example.sql

/*
-- existing table structure, missing index for the field1
CREATE TABLE IF NOT EXISTS `table1` (
`id` int(11) NOT NULL auto_increment,
`field1` int(11) not null, 
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

*/

use test;
-- new table structure with added index
CREATE TABLE IF NOT EXISTS `table1_shadow` (
`id` int(11) NOT NULL auto_increment,
`field1` int(11) not null, 
PRIMARY KEY (`id`),
KEY idx_field1 (field1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TRIGGER IF EXISTS test_table1_update_before_trigger;
DROP TRIGGER IF EXISTS test_table1_insert_after_trigger;
DROP TRIGGER IF EXISTS test_table1_delete_after_trigger;
DROP PROCEDURE IF EXISTS proc_test_table1_update;
delimiter |
CREATE PROCEDURE proc_test_table1_update( op int
, a_id int(11) 
, a_field1 int(11) 
)
proc:BEGIN

INSERT INTO table1_shadow SET
  id=a_id
, field1=a_field1
ON DUPLICATE KEY UPDATE
  id=a_id
, field1=a_field1
;
END;
|
CREATE TRIGGER test_table1_insert_after_trigger AFTER INSERT ON table1 FOR EACH ROW BEGIN
CALL proc_test_table1_update(0
, new.id
, new.field1
);
END;
|
CREATE TRIGGER test_table1_update_before_trigger BEFORE update ON table1 FOR EACH ROW BEGIN
CALL proc_test_table1_update(1
, new.id
, new.field1
);
END;
|
CREATE TRIGGER test_table1_delete_after_trigger AFTER delete ON table1 FOR EACH ROW BEGIN
DELETE FROM table1_shadow where id=old.id LIMIT 1;
END;
|
delimiter ;


-- at this moment all updates to table1 will be reflected on table1_shadow


-- execute fake updates

-- echo 'select id from table1'|mysql test --skip-column-names >ids_to_update.txt
--
-- simplified example with fake updates
-- cat ids_to_update.txt|awk '{print "UPDATE table1 SET id=id WHERE id="$1" LIMIT 1;";}'|mysql test
--
-- at this moment, table1_shadow is full and live copy of table1
--
-- rename tables and drop triggers
-- mysql test
--
-- RENAME TABLE table1 to table1_old, table1_shadow to table1;
-- DROP TRIGGER IF EXISTS test_table1_update_before_trigger;
-- DROP TRIGGER IF EXISTS test_table1_insert_after_trigger;
-- DROP TRIGGER IF EXISTS test_table1_delete_after_trigger;
-- DROP PROCEDURE IF EXISTS proc_test_table1_update;

Great example!

On last app update, one alter table took at least of 1 hour. Now, I want test your solution here.