0

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?

0

First, why do u use MySQL for such a huge dtb? Second, how about MYLVMBACKUP ?? :)

HW conf plays a big role (in regards to performance and d2d operations) -> you should never keep >1TB data size within a single MySQL node/server (it's an overkill)

  • Sometimes you don't get to choose. You arrive to a place and that's the way things are setup and have to find solutions to make it more manageable. Thank you for your suggestion, I will look into it. Also, how would you go about splitting a large database yourself like that to make it more manageable? – Ulukai Aug 16 '17 at 10:25
  • check this out dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html .. it is very individual - there is no universal solution – Mr.P Aug 18 '17 at 6:41
0

If that one scenario is what keeps you up at night, then here is a way to deal with it.

Have a 'delayed' slave. In newer versions of MySQL/MariaDB, there is master_delay. For older versions, there were packages that had the same effect.

The idea is to keep one slave lagging behind the others. Then, if a rogue DROP occurs, and you realize it in time, you can play the binlog forward to the point of the DROP, skip it, then proceed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.