Recommended Backup Schedule

[alexb]

Hi all,

I know this is an extremely open question but I was hoping for some feedback on what backup schedule is best to implement. I have this week purchased the enterprise version and I'm currently setting it up on two brand new database servers I will migrate to once I'm happy they are working correctly.


Application:

Web based CRM used by around 150 companies / 2000 users.
Application used 7 days a week but standard office hours.


Architecture:

MySQL mostly Innodb tables (200+) and few Myisam (< 4).
Master --> Slave replication
MySQL 5.0.77


My Schedule:

- Full Logical backup of master every Sunday 3:30am.
- Daily full Raw backup using LVM snapshots on slave (9pm)
- Hourly incremental Raw backup using LVM snapshots (this is what I'm not sure about, is it overkill?)


Notes:

- Full Raw backup = 13gb
- Full Mysqldump = 5.9gb
- Space on servers is not an issue (991gb)


Also any ideas on what would be reasonable retention period for each would be great.


Thanks for any help / advice.

Alex

[zmanda_jacob]

Your schedule looks pretty good. Please note that incremental backups are copies of the binary logs and no snapshot is necessary for these. ZRM will not do a snapshot for incremental backups - just full backups.

As for your retention requirements, this really depends on your business requirements and your disk space. If you need to be able to restore to a month ago, then your retention should be a month. If you don't then it shouldn't be. Usually with database backups unless you have compliance requirements, you will probably not need to restore data that is that old.