Mysql – Altering a column length on a large Innodb table with minimal downtime

innodbMySQL

I need to change the the length of a column in a MySQL innodb table with 164M rows. Here's the script I want to run:
ALTER TABLE SESSION_DECISION CHANGE COLUMN NAME NAME VARCHAR(255);

When I tried running the alter query, the database churned for an hour and didn't complete the task. Users accessing the system (who needed to write to the table) were locked out.
This is a live system so I ended up ctrl-c'ing the request to let users access the site.

(this was in the middle of the night– usage was minimal but I was getting nervous).

I'm on MySQL 5.0.77.

Any suggestions as to how I can change the column with a minimum of downtime?

Best Answer

Your best option is to setup a slave database using mysql replication. Assuming your schema change is additive, then you can issue the update on that replica, which will be blocked for the duration of the update. Once that is done, and check your slave has caught up with all the updates then declare a small downtime to promote your slave database to the master.

You can find instructions on setting up mysql replication here.

Related Topic