Say I have a MySQL table:
CREATE TABLE tweets ( tweet_id INT NOT NULL AUTO_INCREMENT, author_id INT NOT NULL, text CHAR(140) NOT NULL, PRIMARY KEY (tweet_id) ) PARTITION BY HASH(tweet_id) PARTITIONS 12;
All is good. The table lives on a single server - Server1. But eventually I may want to scale out. So I'd want to shard the table and move 6 of the 12 partitions onto a new server - Server2.
1) Is there any quick & easy way to move those partitions from Server1 to Server2?
2) Now that I have 2 servers, how do I make sure the auto-increment tweet_id's generated by the 2 servers don't have the same value? I'd also need to make sure the tweet_id on each partition stays consistent, i.e. on Partition k every tweet_id's modulo 12 equals to k.
3) Ideally I'd like to continue this scale out process. So later on I'd want to add a 3rd server - Server3. I'd want to re-balance the partitions so that there're 4 partitions on each server. Again how do I make sure the auto-increment tweet_id's generated by the 3 servers are distinct and that the hash of tweet_id's stay consistent within each partition?