change a mysql table column on slave then promote to master

Question

If I want to change a NOT NULL column to allow for null values, is it safe for me to run that ALTER TABLE on my DB slave, wait for it to catch up, and then promote it to master?

Answer 1

As you're going from "NOT NULL" to "NULL", this should be ok. Here's what I would do:

1. Stop the slave SQL thread[1] on the slave: mysql> STOP SLAVE SQL_THREAD;
2. Do the ALTER TABLE
3. Start the slave thread: mysql> START SLAVE SQL_THREAD;

Then on the master, run:

1. Disable the bin log for that session[2]: mysql> SET SQL_LOG_BIN=FALSE;
2. Do your ALTER TABLE on the master
3. Re-enable your bin log for the session: mysql> SET SQL_LOG_BIN=TRUE;

[1] MySQL replication runs in two threads on the slave. The I/O thread copies replication data from the master to the slave. The SQL thread executes that SQL on the slave, doing the INSERT/UPDATE/DELETE/etc. You can stop each thread independently of the other.

[2] This effects only the session you're in, not other connections from your production applications. You're instructing MySQL not to add any commands you run into the bin log for replication. You need this so that your ALTER TABLE isn't replicated to the slave, as it has already been run there.

Answer 2

I just finished reading a great article about using MMM to ALTER huge tables that seems like it would address your problem, assuming you're using or can set up MMM.