1

We have replication set up on some MyISAM servers, in a master/save scenario, MySQL v5.1.

One of the columns is currently declared as VARCHAR(255). We want to increase this to VARCHAR(512).

We have one master and 4 slaves. What is the best scenario to make the change.

If we do this:

ALTER TABLE item MODIFY url VARCHAR(512)

should that be ok, as long as we update the slaves first and then the master, or may it complain and it's best to do them all at once? Should I stop the slaves?

I've tried the update on a dev machine which takes about 10 mins, but it doesn't have replication set there. So looking for any flaws in my thinking.

Or looking further, does ALTER TABLE get replicated, and that's all I would need to do on the master?

  • 1
    does ALTER TABLE get replicated, and that's all I would need to do on the master? Correct. It replicates automatically unless you specifically prevent it from doing so, which is sometimes appropriate for long-running operations if you need to do the replicas first. – Michael - sqlbot Oct 25 '16 at 22:49
2

In a replication environment, for an ALTER that takes a long time, I recommend this:

  1. On one slave: Take out of rotation (keep clients from using it); perform the `ALTER; put back into rotation.
  2. Repeat for other slaves.
  3. SET @@session.sql_bin_log = 0 so it won't replicate; do the ALTER; set that back to 1.

That way, Slave users will not be impacted; Master user may be impacted.

With Galera / PXC, you would use "RSU" (Rolling Slave Update) and have zero downtime (but have to do it manually in a similar manner).

You need to upgrade to 5.6. With it many ALTERs (probably including your case) can use the fast ALGORITHM=INPLACE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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