Using MMM to ALTER huge tables

Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding/dropping columns or indexes, changing data type, converting data to different character set – MySQL master-master replication especially accompanied by MMM can be very handy to do the changes with virtually no downtime.

Couple of days ago I worked with one of our MySQL support customers as they were upgrading their application and mysql schema. We deployed and used MySQL Master-Master replication manager (MMM) ever since we started working so doing all the schema changes synchronously and with only couple seconds of “downtime” was really trivial. I’d like to share my experience.

The article does not cover MMM installation nor how to do this without MMM. It assumes you have one master-master pair named C1 with db1 (current active master) and db2 (standby master) in it.

Precautions: Before applying any changes, you should ensure that your application does not have inserts without columns named (bad e.g. “INSERT INTO table VALUES (id,value1,value2,..)”; good e.g. “INSERT INTO tables (id,col1,col2,..) VALUES (id,value1,value2,..)”), deletes or updates of rows that will not exist after schema changes and other things that would break logical replication if tables on master and replica are different. binary log should be a good place to look for these.

OK, the procedure:

1. on monitor: set db2 off-line

When schema changes will be applied to db2, we will have to cut the replication between db2 and db1. In that case MMM would switch db2 and db1 roles. This step ensures that when replication db2 => db1 is broken, db1 will be available as “writer” and roles stay consistent.

2. on db1: stop replication

we don’t want schema changes to be replicated to db1 now.

3. on db2: load the sql scripts

At this step we load sql commands from changes.sql though of course one can just enter commands from cli, execute some php/python/ruby script or anything else. Most importantly – this must actually be executed as SUPER user (remember, standby master is running with read-only=1) and they must be executed on db2 so better use mysql file socket, or real IP of the node, but not virtual MMM IP as all virtual IPs are assigned to db1 at this stage.

If you’re altering gigabyte size tables, this step may take some time. Go get a pizza or read some cool articles on our blog. When new changes are loaded:

4. on db2: start replication

On step #1 MMM should have stopped db1 => db2 replication so at this stage we start it to ensure db2 accumulates the data changes from db1. If you did not follow the Precautions, there’s a chance replication will fail at this step. If this happens, you’ll have to clone db2 from current db1 (mmm_clone), fix the application and start from the beginning.

The essential requirement for this step is to ensure replication has caught up. If you have multiple pairs doing some changes in parallel and you want to ensure switch from old layout to new one happens on all of them at pretty much the same time, you should wait until all of them reach this point.

5. on monitor: switch masters

Once standby servers (only db2 in our case) have caught-up, we are ready to upgrade the application. The sequence is:

This is the “downtime” part. The longer it takes to apply application changes, the longer is the downtime. Our customer built his application so it automatically detects the new schema and so no changes had to be made during these two MMM steps – it’s a good practice if you want to minimize the downtime. Also, application can typically work with new schema without any changes rolled out, so if that’s the case – you can apply those changes independently.

This is the only downtime you would (or would not =) have. Further steps are only to ensure db1 also gets the schema updated (it’s now a standby master) and db1 is back on-line for a failover. There are two ways to do it:

6. Allow replication to catch up

This can be slower or faster method depending on your overall database size, scope of changes, and few other variables though if you can’t use mmm_clone i.e. you don’t have lvm or another way to create snapshots, this could be the only good way to go.

6.1 on db1: start replication

db1 will receive the updates that were executed by changes.sql or some other script and rebuild its tables.

When replication on db1 has caught-up:

6.2 on monitor: set db1 on-line

Now you can check MMM status for the cluster – it should display db2 as active master (“writer”) and db1 as standby master (having only “reader” role) which means you’re all set.

If mmm_clone is configured to do mysql snapshots, this could be a better way to achieve the same:

Alternative 6. Clone db1 from db2

On db1 execute:

There are several different methods to clone a master, also you can have your custom ones though if you’re using MMM, I suppose this is already configured.

Pretty much the same can be achieved with master-master replication even if you’re not using MMM though it takes few more steps i.e. you must change application to use different IP for MySQL or ensure some load balancer/proxy does this, set read-only manually on standby master so application can’t write to it etc. so MMM really helps here ensuring all is done in the background.

Share this post

Comments (11)

  • angel

    je vous remercie de partager, très gentil de votre part

    March 27, 2008 at 12:00 am
  • Chris

    If you’re just doing an ALTER, a simple way to do it is run:


    First on one server then on the other, switching masters each time.

    March 27, 2008 at 3:09 pm
  • Mark Callaghan

    This is a great way to do it without having the complexity in the MySQL server from trying to do it online as Oracle might. I hope that MySQL doesn’t spend too much time trying to support that. The MMM approach works for most schema changes with far less complexity and risk. But if MySQL spends time on that, then this is an opportunity for MMM as it works right now. The problem gets more interesting as you increase the number of slaves on which the change must be done.

    When can we expect the merger of MMM + Maatkit (== MMMaatkit)?

    March 27, 2008 at 5:00 pm
  • Keith Murphy

    Nice article Peter. Thanks for the example!!

    March 27, 2008 at 5:48 pm
  • Tobias Petry

    Great idea, but what do you do if you have to alter the structure due to better performance?
    While rading this article i had an idea, i would be really happy if you could comment it:

    At first you create in both Masters views which represent the new data structure (maybe you have to alter the relationship of tables). Then you will change application to use these new views instead of the old tables, because the views will later be tables.
    Now you turn off one of the masters, and change the tables according to the views you have created. This will take a long long time…
    When all changes are ready you can turn the master on, and it can load all changes from the master who was online. Now you have got one master with all data and the new structure. Now you can copy the data to the other master who was online all the time.

    March 28, 2008 at 6:36 am
  • Simetrical

    This is no different from if you use master-slave replication, surely? The only caveat there is that you need to promote one of the slaves to a master if you do this, but that shouldn’t be a problem. Wikipedia does essentially this to alter tables without downtime, and of course it uses slave replication, not master-master.

    March 28, 2008 at 10:29 am
  • peter


    This article is by Aurimas, lets give him a credit !

    March 31, 2008 at 8:59 pm
  • peter


    In theory you can use this VIEW approach for complex migrations but in practice there can be many performance gotchas you can run into.

    March 31, 2008 at 9:01 pm
  • peter

    Another note – besides ALTER this works for other similar type of statements. For example you can do online OPTIMIZE table this way.

    April 2, 2008 at 8:37 pm
  • arun

    another method to dump all the rows, reload them into new table with new structure, rename table, and drop the old table. This coupled with SET SQL_BINLOG = 0 (first comment) should work.

    August 18, 2010 at 11:49 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.