GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Backup on one server and restore on another

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Backup on one server and restore on another

    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."

  • #2
    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?

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment

        Working...
        X