I am trying to get my head around how to have a simple recovery solution for mysql databases in case of accidental deletion.
Let's just say for argument's sake that you are an admin of a 800 gig database and one day you or someone accidentally deletes a table or a hundred rows. Here are two potential solutions I thought of, and the problems I see with them.
1) Database sync is enabled with a replication server. Meaning you can restore the data from your replication slave server by doing a dump of the table and then restoring it on the main server?
Problem: if you delete the table on the master server, this would get replicated on the slave, meaning you lose the table in both, am I right?
2) You enable the binary log and restore somehow using this.
Problem: My understanding of the recoveries with the binary log, is that first you have to restore a full backup from the previous day, and then use the binary log to rebase the changes back to the point previously before you deleted the table or rows. I think this is a huge problem if your DB is 800 gigs, because restoration could take a whole day, and that would be a whole day without the server running - not feasible.
What I want is a simple and reliable solution to restore to a previous version at a specific date and set the servers accordingly so in case something like this were to happen, recovery is simple and doesnt take an entire day or hours. Is it not possible to use the binary log to roll back without doing a full backup restore? Are there any other solutions to this problem?