Mysql – Percona Xtrabackup fails to backup InnoDB table with fulltext index

backupMySQLmysql-5.7percona-toolsxtrabackup

I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12).

I have innodb_file_per_table set.

To create the backup, I'm running:

innobackupex --tables-file=tables.txt --no-timestamp /path/to/backup

Then I create the table exports:

innobackupex --tables-file=tables.txt --apply-log --export /path/to/backup

The –export run produces an error message for each of the fulltext index files ("FTS…") in my database directory:

InnoDB: Failed to find tablespace for table `webdb`.`FTS_00000000000005ce_0000000000000630_INDEX_1` in the cache...

Two questions:

Do I have to do the –export run right after I create the backup ? Or can I delay that until I actually need to restore a backup ?

What can I do about the error for the FTS files ? Is that normal ? Will Mysql re-create the fulltext index automatically or is it even included in the main tables .idb file ?

Update:

It would also help if someone could report that backing up tables with fulltext search using Xtravackup did or did not work with Mysql 5.7 for him. Then at least I would know if its a bug in the tool.

Best Answer

Do I have to do the --export run right after I create the backup ? Or can I delay that until I actually need to restore a backup ?

Not necessarily, as long as the backup completes with completed Ok then it is assumed that the backup is successful. It is still best practice to test your backups regularly.

What can I do about the error for the FTS files ? Is that normal ? Will Mysql re-create the fulltext index automatically or is it even included in the main tables .idb file ?

This is expected if in your test.txt file the table with FULLTEXT indexes is not included. Basically you are not backing up that particular table so it will be skipped during the backup operation and during the prepare operation

Easily reproduced if you create test1 and test2 tables and then on the test1 table make sure you create a FULLTEXT index. On the backup stage:

181215 10:31:22 [01] Skipping ./test/FTS_000000000000002e_0000000000000033_INDEX_1.ibd.

On the prepare stage:

InnoDB: Failed to find tablespace for table `test`.`FTS_000000000000002e_0000000000000033_INDEX_1` in the cache. Attempting to load the tablespace with space id 30

One more note, it is recommended to use xtrabackup binary instead of innobackupex because starting in version PXB 8.0 innobackupex is removed.

Related Question