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.

  • 1
    , welcome to the site. what is the edition of mysql server? – Md Haidar Ali Khan May 9 at 22:05
  • mysql 5.7, forgot to add it – pedrotester May 10 at 13:44
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'
        );
  • Look like ENGINE = 'InnoDB' is required, isn't it? – Kondybas May 10 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 – pedrotester May 10 at 13:47
  • @Kondybas: no, the query must return a list of non-InnoDB tables - the ones we want to exclude from the backup. – Federico Razzoli May 10 at 19:07
  • @pedrotester I understand. Did you consider to convert them to InnoDB? This would also avoid data losses if MySQL crashes. – Federico Razzoli May 10 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 – pedrotester May 10 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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