Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I need to update the schema as I accidentally left out some foreign key constraints.

The database server is MySQL 5.1.48 and the tables being altered are InnoDB.

The server is replicated using Master/Slave.

What do I need to know or do to properly alter a production database schema?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

ALTER TABLE statements are replicated via the binary log, in the same way as any other changes to the table. No special consideration is needed for the replication side of things.

share|improve this answer

I hope this is what you need, since you haven't provided enough details:

A) ALWAYS (and I really mean, ALWAYS) do a backup before you attempt such a change on prod

B) on mysql console: ALTER TABLE fsckeduptable DISABLE KEYS; SET FOREIGN_KEY_CHECKS=0; .... do what you have to do ... SET FOREIGN_KEY_CHECKS=1; ALTER TABLE yourtablename ENABLE KEYS;

C) if (shit_happened) { restore backup; abort this; find another method } D) if everything is ok, check table on slaves (show create table) and see if your modifications have propagated, then check prod apps using it; if (shit_happened) see #C)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.