0

Today I had an issue where I accidentally deleted a whole table (which cascaded to a few other tables) due to a funky piece of MySQL syntax, see here.

Luckily I had a backup from midnight, however all data was lost between midnight and to when I deleted all the rows in my table.

How can I better back up my database to be more up to date with the live system, or perhaps better just lagging a hour behind it? I've seen people mention replication and binary logs but this is all kind of new to me. From my understanding, having a replication setup, deleting my table in the master would also delete in the slave database? Would I be better off trying to take hourly differential backups? Or should i just do full backups every hour? Our database is tiny, less than a gig, and we have room so it's feasible, but I would rather do things a proper way that would work for larger databases as well.

  • 2
    What you want is Percona's XtraBackup which performs hot incremental backups. With a small database like yours, it shouldn't impose too much of a performance penalty. If you really want a belt and braces approach, you could have two slaves and alternately backup each slave every half hour - having two gives the alternate a chance to catch up after backing up - possibly through an LVM snapshot (or, if you have ZFS expertise in house, through that system). Basically, every 9 will add a 0! – Vérace Jan 4 at 7:28
0
  1. Better choice is to depend upon a backup utility with which you shall configure backup frequency, etc. be it two slaves or...

Alternatively...

  1. If you have enabled mysql log, you shall pretty well re-create those entries since mid night backup till the current time by running all the pertaining queries.

2a. Either you shall revert the whole data to midnight backup and then run the rest of the log to re-create all and getting upto the current level

2b. Or you shall revert the particular data table to midnight backup and run the rest of the log (by screening the queries).

Depending upon your database / program flow structure you may choose any one of...

|improve this answer|||||

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.