0

I'm using mysql 5.7 (or rather percona 5.7)

Is it possible to backup only the innodb tables (easily, not to specify each table one by one because I have a lot of tables) with xtrabackup? and not take into account myisam tables that are read-only?

If I remember right, if you have myisam tables it will lock the whole DB thus breaking the application during the backup procedure.

2
  • 1
    , welcome to the site. what is the edition of mysql server? May 9, 2019 at 22:05
  • mysql 5.7, forgot to add it May 10, 2019 at 13:44

1 Answer 1

0

Xtrabackup website says something different:

Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup

This information used to be in the documentation, probably better detailed, but nowadays only this enigmatic claim remains. Probably you are worrying too much, but I will answer the rest of the question anyway.

There doesn't seem to be any way to easily exclude non-InnoDB tables from the backup. What you can do is to manually exclude those tables with --table-exclude If you have entire databases dedicated to non-InnoDB tables, --databases-exclude should be easier to use.

You can dynamically compose the list of those tables with this query:

SELECT GROUP_CONCAT(CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) SEPARATOR ',')
    FROM information_schema.TABLES
    WHERE
        ENGINE <> 'InnoDB'
        AND ENGINE IS NOT NULL
        AND TABLE_SCHEMA NOT IN (
            'mysql',
            'performance_schema',
            'information_schema',
            'sys'
        );
9
  • Look like ENGINE = 'InnoDB' is required, isn't it?
    – Kondybas
    May 10, 2019 at 11:06
  • Good answer, I'll try this (unless there is an even better solution). Thank you. By the way, I had tried and it effectively locked my whole application because the read only myisam tables are HUGE May 10, 2019 at 13:47
  • @Kondybas: no, the query must return a list of non-InnoDB tables - the ones we want to exclude from the backup. May 10, 2019 at 19:07
  • @pedrotester I understand. Did you consider to convert them to InnoDB? This would also avoid data losses if MySQL crashes. May 10, 2019 at 19:09
  • Yes but since they are huge (at least it seems to me, probably more than 200mb of read only tables), it seems like it would be better to stick to MyISAM instead of cnverting them all to innodb. Especially after reading this jfg-mysql.blogspot.com/2017/08/why-we-still-need-myisam.html May 10, 2019 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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