What do I need to know in order to change a table schema that is being replicated?

Question

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?

Answer 1

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.

Answer 2

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)