0

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.

|improve this question
  • Sounds like a bug report against percona.com is needed. – Rick James Dec 14 '18 at 19:08
  • My first question is if I use it right. – Gene Vincent Dec 14 '18 at 19:39
0

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.

|improve this answer
  • But I do backup the table that the fulltext index belongs to. During the backup, innobackupex doesn't complain about the FTS...ibd files and ends with "completed Ok", but they don't end up in the backup. Only in the 2nd run innobackupex shows the errors. – Gene Vincent Dec 15 '18 at 12:45
  • I'm sticking with innobackupex so far, because I'm using the --no-timestamp option, that seems to have been removed in 8.0 and there doesn't seem to be an equivalent. – Gene Vincent Dec 15 '18 at 12:48
  • Try to not include --tables-file=tables.txt during the prepare phase (--apply-log). I just tried to reproduce but I do not get the same error – jerichorivera Dec 15 '18 at 13:05
  • Using xtrabackup binary there is no need for --no-timestamp since xtrabackup will not create a timestamped backup directory. – jerichorivera Dec 15 '18 at 13:36
  • Same error without the --tables-file=tables.txt in the prepare phase. – Gene Vincent Dec 15 '18 at 19:37

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.