Around April 2017 I noticed that my database lost records in a few columns of 2 tables, this happened inside 5 of databases my website is using. They are clones just with different data records. This happened after hosting company server maintenance. A few days ago the exact same issue happened again. I’ve rolled back the data from the backups again.
I’m really stressed about this and I’m searching for the solutions.
The perfect thing would be to prevent it from happening ever again, but I don’t know how to do it and first of all what is the exact mechanism behind this data loss. Hosting company technical support could not tell me either. As long as I’m aware about data loss fast (within 1 day) and it happens extremely rarely I’m fine with backups, however, I’m afraid that as database will grow larger I might not notice what have happened in time.
Are there any quick ways to detect data loss inside MySQL database?
The only ways I see for now for detecting it is to inspect each of the databases manually, open them with some database manager and sort the columns to see if there are any empty cells. Or dumping them and restoring locally on my pc, and then comparing tables with more advanced database managing software. However this is painful as it takes time, it would take probably 30-60 min daily.
Any suggestions would be greatly appreciated.
I'm on MySQL 5.5.41, FreeBSD & Apache.