Given the Following

+<------+
|       ^
V       |
M1      M2
|       ^
V       |
+-------+

and You Want the Following

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

Given Master IP is 10.1.2.30

Logon to the Slave and run the mysqldump like this

MAS_IP=10.1.2.30
SLV_IP=localhost
MYSQL_MAS_CONN="${MAS_IP} -uroot -ppassword"
MYSQL_SLV_CONN="${SLV_IP} -uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=1 --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database --all-databases"
mysql ${MYSQL_MAS_CONN} -e"FLUSH LOGS;"
echo "STOP SLAVE;" > MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysql ${MYSQL_MAS_CONN} < MySQLData.sql

If you have already performed this, let me address your comments.

But upon adding the third DB server and starting slave on it, it throws Cannot add or update a child row. foreign key constraint fails error.

Why would foreign key problems happen if you copied the data from a Master to a Slave? The auto_increment ids on the Slave simply did not match that of the Master. This is true even if you the Slave's Master is where you got the data from. Why?

auto_increment_increment is set to 2 in both masters. auto_increment_offset is set to 1 in Master 1 and 2 in Master 2. We have mostly innoDB tables and some MyISAM tables.

You have to make sure the Slave has the exact same auto_increment_increment and auto_increment_offset as the Master you are replicating from.

Goto the Master and run

SHOW VARIABLES LIKE 'auto_increment%';

Put those values into the Slave's my.cnf and restart mysql.

REMEMBER : A Slave's auto_increment behavior needs to be identical to that of its master.

Give it a Try !!!

UPDATE 2013-04-06 17:26

Here is the problem: Let's say you have the setup

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

If you did the following:

• mysqldump data from M1
• load dump it S1
• enabled Replication from M1 to S1
• M1 and S1 have identical auto_increment_offset
• M1 and S1 have identical auto_increment_increment

there should never be foreign key violations.

What you may need to do is cleanup every server's view of the auto increment values

Here is something to try

• Set these values on M1's /etc/my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 1
• Set these values on M2's /etc/my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 1
• Set these values on S1's /etc/my.cnf
  • auto_increment_increment = 10
  • auto_increment_offset = 1

Restart mysql on all three servers

Load Data like this:

• mysqldump from M1 to
• Run SET GLOBAL sql_log_bin = 0; on M1
• Load the mysqldump into M1
• Run SET GLOBAL sql_log_bin = 1; on M1
• Run SET GLOBAL sql_log_bin = 0; on M2
• Load the mysqldump into M2
• Run SET GLOBAL sql_log_bin = 1; on M2
• Load the mysqldump into S1
• Reset Replication amongst the threee server

If nothing breaks, you can then do this

Set these values on M2's /etc/my.cnf - auto_increment_increment = 10 - auto_increment_offset = 2

Restart mysql on M2

Everything should be clean now. From here, INSERTs should be properly handled.

Give it a Try !!1

Actually both configurations are possible.

Remarks to your ideas

For configuration 1, just set up the first Master->Slave replication. See that you should activate --log-slave-updates on the slave so that the slave creates an appropriate binary log for the propagation of the next slave in the chain. Then set up the new slave with the previous slave as a master. This should be straight forward.

For configuration 2 you can use --replicate-do-table (or --replicate-wild-do-table) or --replicate-ignore-table options on the slave. This alters the behaviour of the slave executing received binary logs. Another way is to use --binlog-do-db or --binlog-ignore-db on the master. This alters the logged binary statements on the master directly. There are some pitfalls thought when using. You might want to read the manual yourself (basically it is unsafe to use the --replicate...--db or --binlog...--db options, because they may have unintended side effects).

These problems are all mentioned in this blog post of Percona too. Since there are no --binlog-do-table options, one does not have the chance to safely restrict the written statements on the master. In this setting the whole binary log would need to be transferred to all n slaves.

Another solution

They do propose a different solution for cases when there should become n slaves propagated, but only with a partial binary log. Setting up a replication chain like you did in configuration 1, but setting the storage engine to all tables on the first slave to BLACKHOLE. BLACKHOLE is just /dev/null, so all written data will be immediately lost. This allows to replicate everything from the first master to the first slave. Then the replication rules from configuration 2 (--replicate-ignore-table) are applied on the slave. All other clients are slaves from this "filter-slave". So the first slave is just responsible for filtering the binary log and propagating this log to (maybe) a lot of slaves. Thus the full binary log must be transferred only once, and not to all of the n slaves.

Maybe the last configuration is something you want to take into account.