Spockproxy: Dump and load the schemas
Dump and load the schemas (one for universal and one for the shards).
Note – this post is a footnote for my MySQL users conference presentation Sharding Using Spockproxy: A Sharding-only Version of MySQL Proxy.
Dumping the schema is fairly straight forward using mysqldump. There are two issues: we need to separate the “universal” tables from the sharded tables and you need to decide what to do about auto_increment.
Separating the tables is simple if you have your shard_table_directory table set up the way you like it then run:
SELECT group_concat(table_name, SEPARATOR ‘ ‘) FROM shard_table_directory WHERE status = ‘federated’;
SELECT group_concat(table_name, SEPARATOR ‘ ‘) FROM shard_table_directory WHERE status = ‘universal’;
and keep the outputs separate. We’ll pass the table lists to mysqldump:
mysqldump [OPTIONS] database [tables]
Where the OPTIONS are -d (no data) and probably -p -u and -h; replace ‘database’ with your old database name and [tables] with the table list. Run this and save the two outputs.
OK now you have two files containing the create table SQL for the universal and sharded tables respectively. Before you load them you need to decide what to do about auto_increment. Your basic choices are:
1 – still use auto_increment but make sure the shards are set up so they do not use the same id’s
2 – let the proxy do it for you
3 – do it yourself using the get_next_id(‘table_name’, number_needed) function
4 – do it yourself in your application
(note: 2 and 3 are compatible, possibly 4 too if you update the shard_table_directory)
When you decide how you want to deal with the incrementing you may want to change the column definitions to not be ‘auto_increment’. Make this and any other changes necessary then load the universal file to your master universal DB; it will get replicated to the shards. Then load the shard file to each of your shard databases.