I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about 90-120 minutes to complete, so I can really only do that on a Saturday or Sunday night to avoid affecting the users of the database. If possible I would like to do this modification prior to then.
The column is also indexed, which I presume will make the ALTER TABLE slower, because it has to rebuild the index after modifying the column length.
The web app is set up in a MySQL replication environment (26 slaves and one master). I recall once reading somewhere that one method is to first perform the ALTER TABLE on each slave (minimising impact on users), then do this on the Master, but won't that then try to replicate the ALTER TABLE command to the slaves?
So my question is: what is the best way for me to modify this table with minimum disruption to my users?
Edit: the table is InnoDB.
Best Answer
If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see. Suppose the table you want to change is called WorkingTable. You could perform the changes in stages like this:
You can perform this on all slaves. What about the master ??? How do you prevent this from replicating to the slaves. Simple: Don't send the SQL into the master's binary logs. Simply shut off binary logging in the session before doing the ALTER TABLE stuff:
But wait !!! What about any new data that comes in while processing these commands ??? Renaming the table in the beginning of the operation should do the trick. Let alter this code a little to prevent entering new data in that respect:
Give it a Try !!!