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?

score 14 · Accepted Answer · answered Jan 07 '12 at 01:02

14

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?

answered Jan 07 '12 at 01:02

Gilbert Kakaz

1,567
9
23

there's i no "perfect time" per say, check your stats and see when you have no or low traffic and do the alter then. – Gilbert Kakaz Jan 07 '12 at 01:06

score 1 · Answer 2 · answered Jan 07 '12 at 00:42

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.

score 0 · Answer 3 · answered Jan 07 '12 at 00:41

0

It won't stop your website, but it will likely make it throw errors.

Of course there is no way to answer this without looking at all the code of your application.

The bottom line is, when in doubt schedule a maintenance window.

answered Jan 07 '12 at 00:41

JohnFx

34,542
18
104
162

score -3 · Answer 4 · answered Jan 07 '12 at 00:42

Make the production server to point to dev db (or) mirror of prodcution db for some time.
Alter the table in production
Deploy the code which talks to production db (with the new attributes)

P.S: I feel this is safer and a fool proof way (based on my experience).

alter table mysql offline or not?

4 Answers4