Recover schema and the data in it from accidentally deleted schema



  • I did something careless. I deleted a schema. I have backups of the \mysql...\data\refadmin folder so I (hope) I have all the tables and data. That's backed up daily.

    When I look at work bench, I still have the connection which works, and I can see the schema refadmin but there are no tables or data.

    I don't have any recent dumps.

    The MYSQL server is on Windows 2012 R2. The PC I am using is Windows 10 with Workbench 8. I am using mysql 8.0.0.25 I believe.

    Is there a way to recover the tables and data?

    thank you.



  • I set up a second schema for a new project I was starting. For some silly reason I dropped the schema for the other project. That wiped out all files in the MySQL data folder. Workbench still shows the tables but they are not accessible as the data folder is empty.

    However, I have resolved the issue. I had a backup of the data folder which is backed up daily so I only lost 1 days work. Minimal impact.

    I downloaded the MySQL utilities and with the help of mysqlfrm was able to retrieve the table structures.

    There were 50 tables to recover one at a time with a very specific sequence of commands.

    1. Copy frm and ibd from backup to mysql data folder.
    2. Run mysqlfrm to retrieve table structure
    3. Copy the CREATE table sql to workbench and run it. Fails with table does not exist
    4. drop the table. Fails with table dos not exist (even though it shows in the list of tables and both the frm and ibd files do exist)
    5. Run the create command again. Fails with tablespace exists
    6. Delete the ibd file from the data folder
    7. Run the CREATE table command again. Creates table successfully.
    8. ALTER table discard tablespace
    9. Copy the ibd file back to data folder
    10. ALTER table import tablespace

    Any deviation from the order of steps 3-10 will not get the table back. Just keeps failing with table does not exist.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2