20

I am in the process of migrating 2 DB servers (Master & Slave) to two new DB Servers (Master and Slave)

DB1 - Master (production)

DB2 - Slave (production)

DB3 - New Master

DB4 - New Slave

Currently I have the replication set up as:

DB1 -> DB2
DB3 -> DB4

To get the production data replicated to the new servers, I'd like to get it "daisy chained" so that it looks like this:

DB1 -> DB2 -> DB3 -> DB4

Is this possible? When I run show master status; on DB2 (the production slave) the binlog possition never seems to change:

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000020 |       98 |              |                  | 
+------------------+----------+--------------+------------------+

I'm a bit confused as to why the binlog position is not changing on DB2, Ideally it will be the master to DB3.

5 Answers 5

20

The binlog on DB2 wasn't updating the slave updates. To daisy chain the replication, one must set log-slave-updates in my.conf.

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_log-slave-updates

1
  • But then you write the logs twice, once in relay logs and once in binlog files. Why not replicate from slave using relay logs?
    – hpaknia
    Oct 23, 2019 at 18:57
11

Yes, it is possible :) This is called "Master with Relay Slave" replication and there a lot of documents about it on the net.

I'd recommend you to take a look at the official documentation here.

Btw, also take a look at these slides. They've some hints about replication topologies.

Hope this helps.

3

yes - you can daisy-chain mysql servers, you can even make a circular replication with two or more machines in it. just remember to enable binloging on slave that also acts as master.

5
  • 1
    Circular replication is incredibly fragile. It's ill-advised under most circumstances.
    – Warner
    May 25, 2010 at 17:51
  • @Warner - this was just example of even more complicated replication setup.
    – pQd
    May 25, 2010 at 19:03
  • Friends don't let friend daisy-chain mysql servers.
    – sjas
    May 22, 2017 at 8:24
  • @sjas unless they have a very specific reason to do it and understand potential consequences of breaking the chain.
    – pQd
    May 23, 2017 at 8:28
  • Sorry, but still no.Complexity always haunts you later.
    – sjas
    May 23, 2017 at 8:44
0

Yes, it is possible. Realistically, you need a new snapshot of DB2 to build DB3. At that point, if you have a good position, you can either use than for DB4 or take another snapshot of DB3.

Don't forget to set unique server_id for each server.

High Performance MySQL is a great book for referencing more advanced MySQL administration.

4
  • DB2 is currently only a slave, but it's binlog position isn't changing... Do you know if that's normal?
    – mmattax
    May 25, 2010 at 17:48
  • The slave (DB2) is up to date with the master, yet the binlog position isn't changing, what should I check?
    – mmattax
    May 25, 2010 at 17:54
  • Yes, I've confirmed that I can change data on DB1 and it's replicated to DB2, yet DB2's binlog position is the same.
    – mmattax
    May 25, 2010 at 17:57
  • I think DB@ isn't logging slave updates, hence why the position isn't changing: dev.mysql.com/doc/refman/5.1/en/…
    – mmattax
    May 25, 2010 at 18:06
0

Why not just add DB3 as a new slave, and then promote it to master when you're ready to make the switch? This would give you the temporary added benefit of having multiple slaves, and reduce or eliminate your downtime.

1
  • that's exactly what I am doing...
    – mmattax
    Jun 4, 2010 at 17:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .