I have a data logger application that puts data into MySQL database. That database has a replica. My data logger is connected to the internet via 4G connection which is unreliable sometimes.
It is not a critical service, I'm using that replica to work with data when the connection over 4G is slow.
Yesterday we had bad weather (snowstorm) and my replication stopped. Error logs on my replica showed that I have duplicate keys. It looks like the replica was able to get data, and insert it, and then something happened (probably 4G connection died for a moment) and the server was not notified about these successful transactions.
I had to stop the replica, manually delete records that were sent again by the master, and start the replica. Fortunately, only two tables are replicated, so it wasn't much work, but I don't want to do this every time when we have bad weather.
Is there some way to avoid situations like this?
I saw somewhere that there is
ignore errors option for replica. Could this help? What kind of problems can I have if I turn this on?