Backup on one server and restore on another

[peterpunk]

We have been testing Xtrabackup and we are having problems in restoring a backup done on one server to another.

What did work was this: we did a backup on a Linux server with MySQL 5.5 (using the Xtrabackup 1.5 beta) and we then restored it on the same server - worked fine.

Then we did a backup on a server with MySQL 5.0 using Xtrabackup 1.4, and we tried to restore the backup on another server and database, still using MySQL 5.0 and with the same data path (var/lib/mysql) - but we get a non working copy. The database tables are listed (with “show tables”), but if you try to select anything actually the table does not result existing.

In the err log we get:

“Cannot find or open table mydatabase/olpl_wkl_sched_days from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.”

[xaprb]

SHOW TABLES just means that the .frm file exists. If you use innodb_file_per_table, then matching .ibd files should also exist. Do they?

[rrupsing]

I had a similar problem. Backed up using xtrabackup with one file per table and then restored to another mysql server.

I should also mention the second mysql server has a different path and data directory. Is that a problem?

The .frm and .ibd files are all there and seem to have mysql user permissions set.

SHOW TABLES shows all the appropriate tables but then I get this error message when I try to access to the table:

[ERROR] Cannot find or open table “db/table” from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.

[amzback]

I am having the same issue, Copied the xtrabackup from the production server and when restoring to another server and starting mysql I see the same errors -

110907 18:09:27 [ERROR] Cannot find or open table deta/votes from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshootin g.html
how you can resolve the problem.

When I do show tables; The tables do exist and there is a .frm and .ibd for each table.

Please do let me know if there is a solution for this