I have an amazon rds instance database, and I’m using a read replica for analytics. However, every week or so the read replica crashes with a replication error.
I’ve tried looking at the slave status and skipping replication errors as per this help article however, I’ve only been able to restore it by fiddling around and creating a new read replica.
This becomes problematic for several reasons because external services depend on the initial read replica.
The main database is fine, but it seems some integrity errors cause the read replica to crash and not recover.
Currently my read replica has these parameters:
Replication State: Error
Replication Error: Error 'Cannot add or update a child row: a foreign key constraint fails.....
Is there a way I can configure this read replica to skip all errors? I just am trying to figure out how to make it more stable. Thanks!
You should never, ever skip a replication error until you have understood what is causing the error, repaired the underlying problem, and
corrected any data inconsistencies between the master and replica.
With each error you skip, the divergence increases between your master and replica’s data sets, and any divergence is unacceptable.
You have no real alternative but to create a new replica instance and discard the old one. If a replication error occurs on the new one, stop, figure out why, and fix whatever is being done incorrectly in your configuration or your application to cause the errors.
Skipping replication errors on RDS for MySQL should be considered an emergency stop-gap measure, only, unless you have a thorough understanding of the internals of MySQL replication… because in a correct setup, they are rare.