innobackupex from one server to another "missing tables"

MySQL & MariaDB Percona XtraBackup
1

We have a replication slave that we want to rebuild from one it’s siblings (aka they’ll share the same MASTER_HOST) which has a mix of innodb and myisam tables All servers are running Percona 5.6.22. On the rebuilt slave, we stopped mysql, removed all the relaylogs and binlogs, as well as all of the data, and then ran this:

/usr/bin/innobackupex --host=${master} --user=${user} --password=********* --safe-slave-backup --slave-info --no-timestamp --use-memory=1000M /mysql/backup/dump

This gave us a copy of our database on the slave but in a different directory. We then rsync everything from /mysql/backup/dump/ to the datadir, chown the files, and confirm that both directories are the same number of bytes.

From the datadir, we then ran:

/usr/bin/innobackupex --apply-log ./

This looks good, and so we start the mysql service, login and issue CHANGE MASTER (with all the necessary config options) and START SLAVE. Then SHOW SLAVE STATUS displays:

Last_Error: Error executing row event: ‘Table ‘XXXX.YYYY’ doesn’t exist’

Despite that the table exists on the filesystem, and SHOW TABLES displays it as well.

We see this in the logs:
2015-10-21 15:44:22 45947 [Warning] InnoDB: Cannot open table XXXX/YYYY from the internal data dictionary of InnoDB though the .frm file for the table exists. See [url]http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html[/url] for how you can resolve the problem.

We’ve tried starting mysql server with innodb_force_recovery = 1 and innodb_force_recovery = 2 but those didn’t help either, and looked into “Restoring Orphaned File-Per-Table ibd Files” from the above link, but we have a large number of tables (at least 60 of YYYY across different XXXX shards) so doing all of that manually seems like overkill.

Any ideas on what we are doing wrong?

2

Hi jshaw;

Looks like there is an issue with your tablespaces. Does both the source and target servers have innodb_file_per_table enabled (should be by default in 5.6)? I would also simply clear out your target data directory first, and not rsync over it. Then make sure that the shared ibdata1 file is there, and that all of the corresponding .ibd files are there for all of your InnoDB tables, and double check that ownership / permissions to make sure they are correct.

Chances are it is one of those things. Basically MySQL has an internal view of what it thinks should be there, and it is seeing something different on startup. Which generally means an inconsistency with the data files, either the files themselves, or the ownership/permissions.

-Scott