Frequent Database Backups (Jetbackup)

[jazee]

I'm using Jetbackup for backups. Used it for years. Works really well. I have several sites I want to backup the databases more frequently than once a day. A couple of the sites, there's a lot of things being updated in the database constantly. The most critical one is 1.3GB of data, 2.5 million total rows at this point. I see I did configure an hourly backup some time ago but for some reason I disabled it and can't remember why!

I don't think Jetbackup locks the database when it does the backup. I don't want it to because users still need to use the application during the backup. If worse case I lose an hours worth of updates having to go to the last hours backup, that won't be a catastrophe, much better than losing an entire day's worth of new data. I have a Hot Spare server that I'm using MySQL replication to sync the database but in the event there's a large corruption of the data, the replication is going to sync the corrupt data!

The only solution I can think of is setting up MySQL replication to a third copy of the database but, if possible, put a one hour delay on the replication. But then if the corrupted data isn't discovered within an hour, I'm again in a SOL situation. I'm also concerned about increasing server load doing so frequent of backups (although incremental). Maybe that's why I disabled it?

Thoughts?

 

[cPRex]

Hey there! What if you did a root cronjob with a mysqldump of that database, stored to a certain directory? I'd try just running a mysqldump on the database to see how long it takes to run, and then seeing if that is a good option.

 

[ZenHostingTravis]

If you haven't already, I'd touch base with the Jetapps team.

You can ask for your issue to be escalated and one of the team will get back to you with suggestions.

 

[cPRex]

@JetAppsAdam might want to review this too.

 

[jazee]

There's no problem with JetBackup. I'm now piecing my memory together. I disabled the Jetbackup hourly DB backup because low and behold I set a shell script that read the database table list, does a mysqldump on each table and rsyncs to a backup server that has 23 folders so I have 24 hours of hourly backups. It was so long ago I completely forgot about it. I just accidentally discovered it looking at the root cron schedule for another reason.

I think the reason I did it is because in JetBackup you can't just restore a specific table, only the whole database. Plus using gzipped MySQL table dumps, I can restore the database without using Jetbackup if necessary.

I turned the hourly DB backups on with Jetbackup. It has (which I think it didn't have in the past) an incremental option. The hourly backup is taking less than 2 minutes. Doesn't disrupt the application use and has minimal CPU load. So now I have a convenient way to restore an entire database and an alternate option to restore data in a specific table. All for a whopping $6/month.

I've been extremely impressed with Jetbackup. With almost 30 years experience in IT, I've seen so many sales pitches on backup systems costing 4-5 figures a year in licensing. Jetbackup has been rock solid, full featured, and extremely easy to use and I've used about everything out there.

 

[timkah]

No doubt Jetbackup is an excellent option, but if you want backup without any interruption then database replication is the best option to have an up-to-minute backup.

 

[jazee]

No doubt Jetbackup is an excellent option, but if you want backup without any interruption then database replication is the best option to have an up-to-minute backup.

I've had no issues with "interruption" using the mysqldump method or Jetbackup, as in new data not being written to the database during backup. I do not believe either method 'locks' the DB or tables so they are read-only during backup.

I did confirm with Jetbackup that even the latest version 5, does not allow restore of individual tables, only whole databases. So I'm using mysqldump of the tables and zipping every hour in case I just want to restore a single table and reactivated Jetbackp incremental hourly DB backups as it would be easier for me to restore the entire DB that way.

 

[ffeingol]

Just for other that might be reading the thread:

I don't believe that JetBackup has any way of doing "incremental" MySQL database backups. It's really not technically possible unless you are doing transaction log dumps and that would be much more complicated to restore.

Secondly, and much more important, unless you REALLY understand the database and the relationship between the tables in a database, the idea of restoring a single table can cause more issues than it solves. Other rows in related tables may "logically" disappear because they won't join properly, you can cause issues with foreign key constraints etc. etc.

I'm happy to hear that your process works for you, but "in general" restoring a single table in a database is not a great idea.

 

[JetAppsAdam]

Hello,

Just wanted to address a couple of points brought up in this thread. Firstly, as already mentioned in this thread JetBackup does not perform incremental backups of databases as it is currently not feasible to accomplish this in any way other than a database dump every time the backup job is run. Secondly, regarding JetBackup locking databases backing up, we actually have a setting related to this that you can read more about here: Settings — JetBackup 5 Documentation documentation


Thank you,

Adam G
JetApps, LLC.
JetApps.com | JetBackup.com