Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

2 Answers 2

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...

share|improve this answer
    
Thanks @Verace, to confirm we are using InnoDB. Do you happen to know if the hot backup is available on the open source free version rather than the "MySQL Enterprise Backup (InnoDB Hot Backup)" version ? –  sam Feb 16 at 15:52
    
From the site "Percona XtraBackup makes MySQL hot backups for all versions of Percona Server, MySQL, MariaDB®, and Drizzle". That, IMHO, includes the open source version. –  Vérace Feb 16 at 18:35

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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