The Problem
I'd like to implement a MySQL backup strategy on a dedicated server.
It has multiple databases on, combined total is about 150GB. Most of this is weighted toward one of the databases.
Database is mixed engine MyISAM and InnoDB (plans to overhall are taking place long term but nothing possible short term).
Looking for suggestions for strategies/software that could be used.
Key Objectives
The ideal MySQL backup procedure will achieve the following objectives.
- Consistent MySQL backups.
- Lockless backups (or close to so that database can still serve requests).
- Per table restoration.
Suggestions so far
I'm not a sys/db admin by profession, so I would appreciate the learned advice of the community. Some suggestions I've had so far...
mysqllvm Holland Backup
Holland Backup has a mysqllvm option.
It works by taking a complete snapshot of the MySQL data directory. It locks for a very small amount of time whilst a snapshot is taken and is consistent.
It achieves objectives 1 and 2.
Objective 3, not so much... only restoration of the whole data directory is possible. It's not possible to do per database restoration, never mind per table.
mysqldumplvm Holland Backup
Holland Backup has a mysqldumplvm option.
This achieves objectives 1 and 2.
Objective 3, not so much... It only allows per-database restoration from .sql dumps, which due to the size of the database would take hours/day to restore to a dummy database just to extract one table. Disk space may start to become an issue here.