Announcement

Announcement Module
Collapse
No announcement yet.

XtraBackup on small subset of tables and mysqldump otherwise ?

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • XtraBackup on small subset of tables and mysqldump otherwise ?

    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.

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

    2) How might I do this day-by-day search for the data that I want when using XtraBackup incremental backups ?

    Francis
Working...
X