How to optimize a big table for backup

Question

I have a table that is not too much big but still, it takes 30GB of storage.

When I try to make a backup with mysqldump it takes a lot of time and resources and I want to optimize it but I don't know how.

My data is based on the date. I never delete or change the old data except for sometimes that I make changes to one field that is not too important to be in everyday backup (maybe one weekly backup)

How I can change my backup process to only make a backup of the new data (for example only backup data from today)?

One of my solutions is to make a separate table for every 3 months and make daily backup only for the main table. But it make my application very complicated.

Should I create separate tables for archive? Or is there any better solution?

Answer 1

You probably want to use Differential or Incremental backups more frequently than your Full backups.

Differential backups only backup the changes to your database since the last Full backup ran:

Performing a series of differential backups. Each differential backups includes all the changes made to the data since the last full backup was performed. To restore data up to, for example, time t, you simply restore first the full backup, and then, on top of it, the differential backup taken for time t.

Incremental backups only backup the changes since the last Full or last Incremental type of backup ran:

Perform a series of incremental backup. Each incremental backup only includes the changes since the previous backup, which can itself be a full or incremental backup. The first backup in an incremental series is always then a differential backup; but after that, each incremental backup only contains the changes made since that last incremental backup. Each subsequent incremental backup is thus usually smaller in size than a differential backup, and is faster to make; that allows you to make very frequent incremental backups, and then enables you to restore the database to a more precise point in time when necessary.

So either are generally quicker to run than a Full backup, since they don't backup unchanged data.

Answer 2

Ponder

mysqldump ... --where "dt >= CURDATE() - INTERVAL 1 DAY AND dt < CURDATE()" db tbl

That should dump just yesterday's data for db.tbl. That will run nearly 90 times as fast as a full dump of 90 days.

But, you will need to run that every day; if you miss a day, then some similar statement will be needed to grab the missing day.

Also, it needs to be redirected to a suitable file, probably constructed from yesterday's date.