Database Replication

SingleStore Development
#1

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.

  1. 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?.

  2. 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.

  3. Pipelines
    is there any option to create pipeline from MemSQL database to another MemSQL Database in the cluster.

#2

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