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.
Dear Saikrisha
I tried the database replication but I was hit errors.
Ours memsql production(PRD) have a cluster: 4 nodes:
- 1 MA (SGP011:2707) & 1 CA (SGP012:2707)
- 2 Leaf with SGP012:2708 & SGP013:2707
And running on 3 physical server
I have built new one cluster in a box (DR) on a VM server(SGV045) and have 2 nodes
- 1 MA with port 2707
- 1 Leaf with port 2708
I tried to set the database replication and run the following command on secondary-MA (DR cluster: SGV045)
REPLICATE DATABASE dbname FROM myaccount:‘mypassword’@SGP011:2707/dbname;
but can’t and errors as bellow:
“ERROR 1735 ER_CANNOT_CONNECT_TO_LEAF: Cannot connect to node (null)@SGP011:2707 with user distributed using password: YES [2002] Unable to get address information for ‘SGP011’:2707. Name or service not known”
Hopefully with your experience then you can advise me in this case
Thanks & Best regards
Cuong