I have ~1,000 MyISAM and ~1,000 InnoDB tables that I am currently doing daily full backups with mysqldump. I have a month’s worth of daily backups and a year’s worth of monthly backups.
There are ~60 InnoDB tables that are in the 1G to 50G size that take a fairly long time to backup with mysqldump. I’d like to use XtraBackup for those 60-ish tables using the --tables-file=xtrabackup.tables option with a full backup on the 1st of the month and incrementals otherwise.
- Is there any reason why that would not work ?
When I do table restores I assume that I would have to go the route of something like CREATE TABLE tmp_original_table LIKE original_table; ALTER TABLE tmp_original_table DISCARD TABLESPACE; < copy .ibd and .exp files for table(s) being restored from backup dir to db dir > ; ALTER TABLE tmp_original_table IMPORT TABLESPACE; etc.
There are times when I want to recover a single row from a table, but I don’t know when the good data last existed. With my mysqldump backups I can simply go through the backups for the given table day by day until I find the good data.
- How might I do this day-by-day search for the data that I want when using XtraBackup incremental backups ?
Francis