0

I currently backup daily which I think is fine, however, I want to backup more frequently when there's no activity (e.g., after X minutes of no INSERT/UPDATE/DELETE excluding SELECT).

I'm currently thinking of creating a custom script that will check for CHECKSUM TABLE mytable and see if that changes, every X minutes.

Is this a good idea or is there a better way, perhaps an existing tool?

Improve this question
2
  • 1
    1) CHECKSUM causes read lock on a table - it may be not safe. 2) Table data may alter between calculation and backing up.
    – Akina
    Commented Dec 3, 2018 at 18:37
  • 1
    Is this a good idea I doubt it. is there a better way Possible way. Create service table to store last update timestamp for each table. Create trigger pack for any data alter for each table, update service table from it. Create event which checks service table periodically and starts table backup (data only! SELECT INTO OUTFILE) if it was changed too long ago or (!) its last backup is too old. Tables structure must be backed up separately.
    – Akina
    Commented Dec 3, 2018 at 18:45

2 Answers 2

Reset to default
1

If you set up Replication, you can lose the entire server, yet recover everything except anything that was "in progress" when the disaster hit.

That is, the Slave is always nearly an up-to-the-second copy of the Master. No backups, no locks, no reload, etc.

Then, if you like, you can "continually" backup the slave. It won't impact the Master and all the activity there.

If your fear is that something will be deleted, then needs to be recovered, see CHANGE MASTER TO ... MASTER_DELAY ....

Improve this answer
5
  • I need the ability to recover deleted data so I think replication won't help me as it's a mirror right? But it guess it will if I backup the slave continually.
    – IMB
    Commented Dec 4, 2018 at 14:21
  • 1
    @IMB - I added a link about MASTER_DELAY.
    – Rick James
    Commented Dec 4, 2018 at 16:31
  • Thanks I think that might work. Here's how I see it setup: 2 Slaves. The first Slave (has no delay) for high-availability purposes. The 2nd Slave (has delay) for data recovery. On top that, a continual backup of the first slave for data/disaster recovery. Sounds good?
    – IMB
    Commented Dec 4, 2018 at 16:41
  • @IMB - OK, but sounds excessive.
    – Rick James
    Commented Dec 4, 2018 at 16:43
  • Why is that? Seems to me this is the minimum required that covers all bases without affecting Master's performance. 1. Master failure, check. 2. Recover deletes, check.3 Recover from total disaster, check.
    – IMB
    Commented Dec 4, 2018 at 16:48
0

If your goal is to loose less in the event of value, then use binary logs as a point in time recovery mechanism.

Otherwise using mysqldump --single-transaction and xtrabackup has an throttle option to limit throughput.

And/or configure a replication slave purely for backup where it doesn't matter about the impacts on that slave.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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