Modify MySQL InnoDB table with minimal downtime

Question

I have a huge InnoDB table (> 500 million rows) that I would like to split into a hash to reduce the size of the index. I would like to achieve this with minimal downtime (e.g. 10 minutes is acceptable), what are the possible options?

I was thinking of something like the following:

• create a new partitioned table
• insert all data from the old into this new table using "insert ... select ..."
• make the server unavailable to clients
• somehow synchronize the changes that happened to the old table during step 2 with the new table
• replace the old table with a new one.
• make the server available to clients

The main question is which tool can be used in step 4. The problem is that in step 2 there can be many changes in the original table: new inserts, updates, deletions - the synchronization tool should take all this into account ...

Another possible way, I believe, is:

• set up a replicating slave server
• synchronize this slave server with the master
• master / slave switch roles and reconfigure all clients to connect to the new wizard
• change the table of the previous wizard
• waiting for master / slave synchronization
• re-enable the role of master / slave, reconfigure 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 address to the wizard, changing clients to the new IP address. Then, when you are going to switch to a subordinate, simply:

• stop mysql on the main

• ifconfig additional wizard IP

• ifconfig additional wizard IP address

Clients will simply connect to the new wizard without any reconfiguration of the client. Then you do the same when you return to the original master (if you switch back).

I recommend that the slave always be the equivalent hardware for its master, so that when it takes over for the master, you don’t find it so slow that it cannot keep up, and your whole system fails. If you do this, you only need to switch once (from the current master to the new master).