0

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?

Aaron Gibralter
  • 197
  • 1
  • 9

2 Answers2

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.

Avleen
  • 1,042
  • 7
  • 4
  • 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 00:52
0

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.

metavida
  • 103
  • 4