0

I have two MySQL server having master-slave replication for DB1. The slave server holds DB2. Because the slave server is not so powerfull I want to run analytics upon DB2 on the third server. Is it possible to have such setup?

Dharani Dhar Golladasari
  • 7
  • 1
Ralfeus
  • 121
  • 1
  • 1
  • 6

1 Answers1

1

Your title and question are ambiguous. I'll give two answers.

  • Do not try to replicate some tables in one direction and other tables in the other direction.

  • It is perfectly fine to chain 3 (or more) servers chained together:

DB1 is a primary
DB2 is a replica of DB1 and also a primary
DB3 is a replica of DB2

Or you could have two Replicas hanging off one Primary. That is,

DB1 is a primary
DB2 and DB3 are each replicas of DB1 -- Note: their relay logs will not necessarily be in sync with with each other; this is fine.

Bidirectional replication -- A dual-Primary setup involves each of two servers being both "Primary" and "Replica". (This used to be called "dual-master".) It is mainly used to allow for relatively rapid failover.

While you could use limitations on what is replicated (via binlog/replicate-do/ignore), you lose the failover feature.

One thing to note in any replication topology. All writes are performed on all servers. (Unless filtered out.) My point is, that you cannot "scale writes" with normal replication.

DB1 at Server1 -> DB1 at Server2 DB2 at Server2 -> DB2 at Server3

So, there are 3 servers and at least 2 Databases?

On Server2, you may want "binlog_ignore = DB1" to avoid replicating that db to Server3.

Server2 is set up as a Primary (to Server3) and a Replica (of Server 1).

Rick James
  • 2,058
  • 5
  • 11
  • I'm not trying to replicate data bidirectional. What I want to do is: DB1 at Server1 -> DB1 at Server2 DB2 at Server2 -> DB2 at Server3 – Ralfeus Sep 22 '22 at 06:23
  • @Ralfeus - I added more. I would simply replicate everything and do different tasks (eg analytics) only on selected server(s). – Rick James Sep 22 '22 at 16:03