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

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?

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
I'm running MySQL on EC2 with EBS volumes and can boot up slaves on the fly... I'm thinking that I don't need to do the second 3 steps... rather I can just promote the slave to master once it has caught up. At that point, I can shut down the old master and boot up a new slave DB from an EBS snapshot. –  Aaron Gibralter Jan 24 '11 at 0:52
add comment

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.

share|improve this answer
    
Cool, thank you! –  Aaron Gibralter Feb 4 '11 at 23:52
add comment

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.