I want a tool to make some long-running schema changes almost non-blocking. They should block access to a table for no more than a few seconds. I also want to do some of these in place on a master rather than on a slave that has been taken offline.
I think this will work for most schema changes. It doesn't have to work for all of them and there are restrictions. This will not work when statements that modify the table for which the schema change is done reference other tables and the other tables are modified during the schema change. If production SQL cannot be changed to meet this restriction, then the schema change can be done on a slave that has been taken offline.
Is anyone else interested in such a tool? A hand-waving description of the process is:
- Create the new table on the master. The new table might use MyISAM without indexes initially to make the insert as fast as possible and reduce the load on InnoDB.
- Run set sql_log_bin=0 as what follows should not be written to the binlog
- Run start transaction with consistent innodb snapshot to start an Innodb transaction and get current binlog offset of the master
- Run insert into new_table select * from original_table on the master. Alas, this will get a transaction duration read lock on every row in original_table unless you use row based replication or hack InnoDB or set innodb_locks_unsafe_for_binlog.
- Convert new_table to InnoDB and create indexes on it
- Replay changes from the binlogs after the point in time recorded in step #3. This should extract changes to original_table and replay them against new_table.