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