How to Recover Table DDL from MySQL 8 without FRM Files
MySQL 8 introduced many changes and improvements to the database system, but one of the most noticeable ones was the removal of the FRM files. These files stored the metadata and structure of each table, and they were often used by database administrators and developers to recover table definitions in case of data loss or corruption.
However, in MySQL 8, the FRM files are no longer used or generated. Instead, all the table metadata is stored in a new data dictionary that resides inside the InnoDB engine. This means that you cannot use the old methods of recovering table DDL from FRM files, such as using the mysqlfrm utility or copying the FRM files to another server.
So, how can you recover table DDL from MySQL 8 without FRM files In this article, we will show you two possible ways to do it: using the INFORMATION_SCHEMA or using the mysqlpump utility.
Using the INFORMATION_SCHEMA
The INFORMATION_SCHEMA is a virtual database that contains information about all the objects in the MySQL server, such as databases, tables, columns, indexes, etc. You can query the INFORMATION_SCHEMA to get the table DDL for any table in your server. For example, to get the table DDL for the employees table in the test database, you can run this query:
SELECT CREATE_TABLE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'employees';
You can then copy and paste this result to create the table on another server or to restore it from a backup. However, this method has some limitations. For example, it does not include foreign key constraints, triggers, stored procedures, views, or events. Also, it may not work if the data dictionary is corrupted or inaccessible.
Using the mysqlpump utility
The mysqlpump utility is a new tool that was introduced in MySQL 8. It is similar to mysqldump, but it has some advantages, such as parallel processing, compression, and filtering. You can use the mysqlpump utility to export the table DDL for any table in your server. For example, to export the table DDL for the employees table in the test database, you can run this command:
This will create a file called employees.sql that contains the table DDL for the employees table. You can then import this file to another server or use it to restore your table from a backup. This method has some advantages over using the INFORMATION_SCHEMA. For example, it includes foreign key constraints and it works even if the data dictionary is corrupted or inaccessible. However, it does not include triggers, stored procedures, views, or events.
In this article, we have shown you how to recover table DDL from MySQL 8 without FRM files. You can use either the INFORMATION_SCHEMA or the mysqlpump utility to do it. However, both methods have some limitations and drawbacks. Therefore, we recommend that you always backup your database regularly and keep a copy of your table DDL in a safe place. 061ffe29dd