Lately my attention has been focused on distributed MySQL database replication to other web servers. All was going well, but then I noticed the replication would stop and there were there "Duplicate Primary Key" errors in SHOW SLAVE STATUS;
I started to trace these down and noticed that this is a very common issue in MySQL replication with many of these distributed web applications. It seems that the slave has dB tables of it's own like log files, authorization files, statistics. A mind of it's own! What kind of a slave is that!? Ha Ha...
Anyway, after doing the google-research-thing (GRT) for nearly an hour, I finally made the decision to add this line to my.cnf:
slave-skip-errors = 1062 #skip duplicate key errors in slave
.... and I feel "bad" about it.
I always thought dB replication was dB replication, pure-and-simple, but now there is a more complex side.
It seems to actually be a "good replicator" we need to know which tables in the database to replicate, heaven forbid!
Most folks on the net, based on my GRT, use the slave-skip-errors = 1062 solution (where 1062 is the error number of the duplicate key insert error), but I feel like I've just struck a deal with the devil.
Now, I have no idea what is going on... because the longer I run in this slave-skip-errors = 1062 mode, the further and further the dBs will lose consistency.
GRT yielded no good solution.
I'm lazy to test, test, test... find 1062 errors .... and then see if the table in question should be excluded from the replicate list.
Surely there must be a better way!?