alter table mysql offline or not?

Question

I need to add a column to my current table.

This table is used a lot during the day and night. i found out i need to alter using the alter command found here

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

I tested it on a development server.

i took about 2 hours to complete. Now I want to execute this on the production server?

will this stop my website?

Answer 1

Why not display a message on the site saying you will perform maintenance from midnight UTC time January 7 2012.

This way, you won't break any data, you will not get any mysql errors. you execute your ALTER and you start the site again once its completed (don't forget your code to make sure you have the right field etc..). Easy solution.

Stackoverflow does it, why not yours?

Answer 2

Yes, during an ALTER TABLE all reads and writes are blocked. If your website needs to use that table, requests will hang.

Try pt-online-schema-change. It allows reads and writes to continue, while it captures changes to be replayed against the altered table once the restructure is done.

Test carefully on your development server so you know how it works and what to expect.