Mysql – shard a thesql database. I want to start with 12 shards on 2 machines. What is the best way to move some of them when I want to add a new server


All tables are InnoDb. I would rather not use mysqldump, because the shard sizes will be about 200 GB (about 700 million rows), and that will take too long.

I was hoping to just stop mysql for an hour, copy the data files to a new machine, and start back up. But you can't do this with InnoDb, as some data is in the shared tablespace. Even if I have the innodb_file_per_table option set.

This is not a website, but a custom application, used by tens of thousands right now, so uptime and performance are important. I suppose I could add logic into my server application to allow for gradual rebalancing / moving of a shard.

Does anyone have a better idea?

Best Answer

You can try with innodb hotcopy from

A little expensive, but seems you can get an evaluation copy.