EmergencyEMERGENCY? Get 24/7 Help Now!

Using MMM to ALTER huge tables

 | March 27, 2008 |  Posted In: Insight for DBAs


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.

Aurimas Mikalauskas

Aurimas joined Percona as a first MySQL Performance Consultant in 2006, a few months after Peter and Vadim founded the company. His primary focus is on high performance, but he also specializes in full text search, high availability, content caching techniques and MySQL data recovery.


  • 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.

  • 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)?

  • 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.

  • 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.

  • Tobias,

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

  • 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.

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.