Altering MySQL InnoDB table with minimal downtime
I've got a h开发者_运维问答uge 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:
- create the new partitioned table
- insert into this new table all the data from the old one using "insert ... select ..."
- make server unavailable for clients
- somehow sync changes which happened to the old table during step 2 with the new table
- replace the old table with the new one
- 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:
- setup a replicating slave server
- sync this slave server with master
- switch master/slave roles and re-configure all clients to connect to the new master
- alter table on the previous master
- wait for master/slave synchronization
- switch master/slave roles again, re-configure all clients
Which one would you recommend?
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:
stop mysql on the master
ifconfig down the extra IP on the master
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).
精彩评论