I'm trying to wrap my head around the many options on how to possibly sync database tables automatically from one database to the next (and next, and next, …).
We're trying to create a very simple database sharding system for our marketing saas product where we've created ID's that represent a project-id (the id we'd like to "shard" on) and a local-id (primary key of the table). These ID's are 64 bits and look something like this: 1099511627777 (project: 1, local id: 1).
We like to create a system that allows us to scale 'horizontally' by letting us move projects from one database or server to another one or even to a completely new one.
Now, I've tried researching all the options and it's just too much for me to start. We like to create a system where only the database tables are replicated to all the other databases. But only the tables, not the data inside these tables.
Is there such a way to replicate only the tables and thus keeping the 'cluster' of databases in sync with each other? Ideally we want to have our application (Laravel/PHP/MySQL based) run migrations only on one database/server and replicates it automatically to all other databases and servers
Thanks in advance
Best Answer
"Sharding" involves servers running in parallel, not replicating one from another.
The most practical way to copy a schema without the data is via
mysqldump --no-data. However, there is no automation for that.Bluntly put, you have to reinvent the sharding wheel. There is very little that addresses the many issues of sharding.
That said, I suggest you look at Spider, Clustrix, NDB Clustering, and perhaps a few others.
I strongly suggest you perfect a way to copy a 'project' from one shard to another. It should be as close to non-invasive as practical. Then it could be the main tool for updating hardware, updating software, offloading a busy shard, retiring hardware, etc. Specifically to your question, you can build a new shard with upgraded Laravel/schema/etc, then migrate projects to it. Rinse, repeat.