Adding slave to existing master-master config throws foreign key constraint failed error We have two MySQL servers running in master - master configuration.
Now we have to add a slave to the existing configuration, but upon adding the third database server and starting slave on it, it throws:

Cannot add or update a child row. foreign key constraint fails

We have tried taking a mysqldump from the master with --master-data and restored it. After that, we started the slave on the slave DB. But even then, it says the same error. We have also tried XtraBackup. But that also throws the same error. Are we missing something?
mysqldump command:
mysqldump --master-data -uroot -p dbname &gt; dbname.sql

We have also tried it this way: percona xtrabackup
In both cases, upon getting the foreign key error, we tried to dump and restore individual tables referenced by the foreign keys manually from the master to the slave. Upon doing this, the replication starts and seems to work normally with 0 seconds behind master for a few minutes, after which another foreign key error shows up, stopping the replication.

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.
MySQL version 5.5.30.

There is a follow up question: After adding a slave to Master - Master configuration, slave syncs from only one master
Given the Following

+&lt;------+
|       ^
V       |
M1      M2
|       ^
V       |
+-------+


and You Want the Following

      +&lt;------+
      |       ^
      V       |
S1&lt;---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;" &gt; MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} &gt;&gt; MySQLData.sql
echo "START SLAVE;" &gt;&gt; MySQLData.sql
mysql ${MYSQL_MAS_CONN} &lt; 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

      +&lt;------+
      |       ^
      V       |
S1&lt;---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