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
On the face of it, that does seem impossible.
The thing is, your error suggests it's not that you're trying to delete at all.
The message you're getting suggests you're trying to insert or update a row in the child table, not delete a row from the parent table. If the foreign key you posted was causing the problem relative to a delete, you should see this message, instead:
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`bar`, CONSTRAINT `bar_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`))
It's also possible you have some BEFORE DELETE
trigger magic on survey_main that's doing something unexpected.
Right after this error occurs, try this:
SHOW ENGINE INNODB STATUS;
The LATEST FOREIGN KEY ERROR
section should give you something more to go on. Failing that, you could enable the general log, which will show queries executed by triggers and other stored programs, as well as the queries you're directly executing, to shed light on what might be going on behind the scenes.
Update (#1) Things are definitely not as they seem and the full table definitions are going to be pretty critical, here.
Also, the version of MySQL you're using may also be relevant, so please mention it.
With nothing more to go on at the moment, I'm speculating that you have invalid data in the survey_id column of the survey_answers table. To test that theory:
SELECT *
FROM survey_answers sa
LEFT JOIN survey_main sm ON sm.id = sa.survey_id
WHERE sm.id IS NULL;
If I understand your schema correctly, then this query will return zero rows if I am wrong. :) If you get rows returned, then those rows have survey_answers records that contain an survey_id value that doesn't exist in the id column of survey_main.
Best Answer
On the slave, you can configure MySQL to skip certain errors by using the
slave-skip-errors
configuration item.In the case above, you'd add the following to your my.cnf;
slave-skip-errors = 1452
But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...
http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_slave-skip-errors