Backup architecture for frequent backups of business critical DB

Question

We've got a database (mysql) driven application which contains business critical information, were looking at building a system that will allow us to backup the db frequently (every 15 minutes, ideally less) so that we mitigate the danger of any data loss.

Because the application is private and only being used by a handful of users (at most 12 at the same time) and because all the data is user inputted (not generate / feed / api driven) we dont really need to have too many considerations to scale of the DB or the backup architecture.

Is there an established architecture / workflow for running backups with these constraints ?

Answer 1

Do you use InnoDB tables or MyISAM? If the former, then use Percona's hot backup solution here for scheduled hot backups. If you're using MyISAM (why?), you can at best take warm backups - but you have to tell your users to stop updating every 15 mins - not the best idea.
HTH, Paul...

Answer 2

Using Xtrabackup for full backups every 15 minutes is overkill IMO. You should ensure that you have binary logs enabled and then mirror them off to another server using mysqlbinlog from MySQL 5.6 (it's backwards compatible) so that you can perform point in time restores. The binary logs contain all changes that occur on the instance whilst a full xtrabackup would contain the entire dataset. This will have obvious overhead. Alternatively look at the incremental backup capabilities of Xtrabackup and if you're having any issues using it head over to Percona's forums for some assistance.