Altering MySQL InnoDB table with minimal downtime

Question

I've got a huge InnoDB table(>500 millions rows) which I'd like to partition by hash in order to decrease the index size. I'd like to achieve this with a minimal downtime(e.g 10 minutes is acceptable), what are the possible options?

I was thinking about something as follows:

1. create the new partitioned table
2. insert into this new table all the data from the old one using "insert ... select ..."
3. make server unavailable for clients
4. somehow sync changes which happened to the old table during step 2 with the new table
5. replace the old table with the new one
6. make server available for clients

The main question is what tool can be used in the step 4. The problem is that during step 2 there can be lots of changes to the original table: new inserts, updates, deletes - the sync tool should take all of this into account...

Another possible way, I believe, is:

1. setup a replicating slave server
2. sync this slave server with master
3. switch master/slave roles and re-configure all clients to connect to the new master
4. alter table on the previous master
5. wait for master/slave synchronization
6. switch master/slave roles again, re-configure all clients

Which one would you recommend?

Answer 1

I would go with master/slave replication. If the master and slave can be on the same subnet, I would also add a new IP to the master, change the clients to point to the new IP. Then when you are about to switch to the slave, just:

1. stop mysql on the master

2. ifconfig down the extra IP on the master

3. ifconfig up the extra IP on the new master

Clients will just connect to the new master without any client reconfig. Then you do the same thing when you switch back to the original master (if you switch back).

I recommend that a slave always be equivalent hardware to its master so that when it takes over for the master you don't find out it's so much slower that it can't keep up and your whole system fails. If you do that, then you need only switch once (from the current master to the new master).