Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld]
group header in the Slave's my.cnf
as follows:
[mysqld]
slave-skip-errors=1452
You will have to restart mysql for this to take effect.
Give it a Try !!!
CAVEAT : Your data integrity will deteriorate when added new data because many INSERT queries or multiple-row INSERTs will fail because of just one row not having parent keys.
You should really consider loading all referenced tables into the slave to have good, clean data. Otherwise, it's not a true copy of the Master.
Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event. This is usually "success" (0, no error).
When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error"). When this doesn't happen, replication stops. Note that the error message from the master isn't preserved, just the code. That's why you see placeholders in the error message, because the slave displays the sprintf()
template for the error message.
But note, carefully, where the error is.
Check the master server's error log.
It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade
utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.
http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html
You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication.
Error on master:
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.'
error code=1558 ;
The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all. In any event, your application should have seen this error also... perhaps it's ignoring it. :(
The slave didn't encounter any error executing the actual query.
Error on slave: actual message='no error', error code=0.
It occurred to me that it's very important that the actions performed by mysql_upgrade
can't be safely replicated, since the slave server is older. It turns out, this was anticipated in the design, so the mysql_upgrade
utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c
:
Master and slave should be upgraded separately.
All statements executed by mysql_upgrade will not be binlogged.
Best Answer
Given the Following
and You Want the Following
Given Master IP is 10.1.2.30
Logon to the Slave and run the mysqldump like this
If you have already performed this, let me address your comments.
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?
You have to make sure the Slave has the exact same
auto_increment_increment
andauto_increment_offset
as the Master you are replicating from.Goto the Master and run
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
If you did the following:
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
Restart mysql on all three servers
Load Data like this:
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