We have a scenario where we need to replicate our database such that when ever there is a DDL/DML change it should propagate to the replica DB. We came across three options and their cons.
-
REPLICATE DATABASE
Here the duplicate database is read-only unless we stop replicating. We need duplicate database to be active and still replication to be carried. If we stop replicating and we done some DDL/DML changes in parent DB and again if we start replicating will this latest changes will be carried to child DB?. -
mysqldump
If we use mysqldump DDL’s in .sql file will not carry shard keys and we need to manually edit DDL to include shard key which is not feasible. -
Pipelines
is there any option to create pipeline from MemSQL database to another MemSQL Database in the cluster.