2

I have replication setup as follows

Master A ----> Slave B ------> Slave C
     \-------> Slave D
      \------> Slave E - H

I use this setup because I need a local copy at the office server (that is slave C). I don't want to put extra strain on Master A because it's already receiving all of the inserts and extra load from the slaves connections.

So I setup multi level replication. Master A replicates to Slave B, which in turn is master to Slave C.

Replication from A -> B works perfectly. Replication from B -> C breaks constantly with "Duplicate key" errors. I have the relay log enabled in server B to enable replication from B to C.

Has anybody encountered this problem before?

Master / Slave B my.cnf is as follows:

# Replication setup
log-bin=/var/log/mysql/mysql-bin
server-id=2
sync_binlog=0
binlog_format=mixed
log-slave-updates
replicate-same-server-id = 0
expire_logs_days=15

Is there anything I'm doing wrong?

Alex Recarey
  • 441
  • 1
  • 6
  • 14
  • Are you sure nothing's writing to Slave C ever? Do you have the right offsets set up (same as circular replication) so A and B don't create conflicting autoincrement IDs? – ceejayoz Oct 10 '11 at 20:01
  • Yes, only A gets any writes. I thought I would not need to setup offsets if I only write to one server? – Alex Recarey Oct 10 '11 at 21:31
  • If A is guaranteed to be the only server receiving writes you shouldn't be getting the error described. Have you taken a look at the queries causing the errors? – ceejayoz Oct 10 '11 at 21:34
  • All are random queries that should not be causing errors. Most of them are due to duplicate primary key errors, where the primary key is auto-increment – Alex Recarey Oct 18 '11 at 10:50

2 Answers2

2

If you are getting duplicate key errors, then something is writing to slave C (likely) or you are executing non-deterministic insert/update operations on the master that do not replicate well in MIXED format (unlikely). Having sync_binlog=0 set could cause some problems for you, but they would likely be rare and only occur around server crashes. If it is truly non-deterministic queries causing a problem, you might want to consider binlog_format=ROW.

Regardless, first you need to deal with syncing the data up. The simplest way to do this is to just start with a fresh backup of B and make sure you CHANGE MASTER TO the correct binary log coordinates. If a restoring from a backup is out of the question, then you can investigate using a tool such as mk-table-sync from maatkit.org. It's a complicated tool, though and if you aren't dealing with TBs of data, I'd go with a recent backup.

Then, make sure that C is set to read_only in my.cnf with and with 

[mysqld]
read_only

and also run this on the server

SET GLOBAL read_only=1;

However, keep in mind that read_only does not apply to users with the SUPER privilege, so ensure that no one except the root user has that access and that you are using unprivileged users to query the database.

Honestly, with only 7 slaves, I would skip the complexity of the multi-tiered setup and just keep it all at a single level. Overhead per slave is not expensive, unless you are dealing with a very slow network connection on the master. Do you have evidence that streaming binary logs to slaves is causing a performance degradation?

Aaron Brown
  • 1,697
  • 1
  • 12
  • 22
1

I second the read_only on the slaves. It's a sanity check if nothing else. Are you sure the server_ids are all different? Showing the actual duplicate key error might help as well (if you can). Could there be some stray trigger on C or B that? Investigate the table that's generating the errors. How many databases are on the master server? Are they all replicated?

sinping
  • 2,070
  • 14
  • 13