MySQL 8 And The FRM Drop… How To Recover Table DDL

[Frauke Killen]

MySQL 8 and The FRM Drop: How to Recover Table DDL

MySQL is one of the most popular open-source relational database management systems in the world. It is widely used for web applications, e-commerce, data warehousing, and more. However, MySQL is not immune to data corruption or disasters that can cause the server to fail or lose data. In such cases, it is crucial to have a backup and recovery strategy that can restore the database to a consistent state.

One of the challenges of recovering a MySQL database is to retrieve the table structure or definition, also known as Data Definition Language (DDL). In previous versions of MySQL, the table DDL was stored in external files with the .frm extension, along with the data files with the .ibd or .myd and .myi extensions. These files could be copied or moved to another server or directory, and MySQL could read them and recognize the tables. However, this approach had some drawbacks, such as:

MySQL 8 and The FRM Drop How To Recover Table DDL

DOWNLOAD https://ckonti.com/2wJjuz

• The .frm files were not transactional and could get out of sync with the data files.

• The .frm files were platform-dependent and could not be transferred between different operating systems or architectures.

• The .frm files were prone to corruption and could cause errors or crashes.

• The .frm files were not easy to read or parse by human or tools.

In MySQL 8, the .frm files have been dropped and replaced by a new data dictionary that stores all the metadata information inside InnoDB tablespaces. The data dictionary is transactional, platform-independent, resilient, and accessible by SQL queries or commands. This change brings many benefits, such as:

• Improved performance and scalability of DDL operations.

• Simplified backup and restore procedures.

• Enhanced security and consistency of metadata.

• Better compatibility and interoperability with other database systems.

However, this change also implies that the old methods of recovering table DDL from .frm files are no longer valid or applicable. In this article, we will explore how to recover table DDL in MySQL 8 without .frm files, using different tools and techniques.

Recovering Table DDL from SDI Files

One of the new features introduced in MySQL 8 is the Spatial Data Infrastructures (SDI) file. The SDI file is a JSON file that contains a copy of the table DDL and some additional information, such as tablespace name, schema name, table name, etc. The SDI file is stored inside the InnoDB tablespace that contains the table data. The SDI file is updated or modified by DDL operations on tables that reside in that tablespace.

The SDI file can be used as a source of table DDL recovery in MySQL 8. However, there are some limitations and caveats to consider:

• The SDI file is only available for InnoDB tables, not for other storage engines.

• The SDI file is only created if the innodb_file_per_table option is enabled, which is the default in MySQL 8.

• The SDI file is not guaranteed to be consistent with the table data, as it may not reflect the latest changes or transactions.

• The SDI file is not easy to extract or read from the InnoDB tablespace, as it requires special tools or commands.

To recover table DDL from SDI files, we need to use a tool called mysqlpump, which is a logical backup utility that can dump database objects and data into a SQL script. The mysqlpump tool has an option called --include-sdi that allows us to include the SDI information in the output. For example, we can run the following command to dump the table DDL from a schema named windmills:

mysqlpump --user=root --password --include-sdi --skip-dump-rows --exclude-databases=mysql windmills > windmills_sdi.sql

This command will create a file called windmills_sdi.sql that contains the table DDL along with the SDI information for each table in the windmills schema. The output will look something like this:

-- Dump created by MySQL pump utility

-- Dump start time: Tue May 23 02:08:40 2023

-- Server version: 8.0.23

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS;

SET @OLD_SQL_MODE=@@SQL_MODE;

SET UNIQUE_CHECKS=0;

SET FOREIGN_KEY_CHECKS=0;

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `windmills` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;

USE `windmills`;

CREATE TABLE `wmillAUTOINC1` (

`id` bigint(11) NOT NULL AUTO_INCREMENT,

`uuid` char(36) COLLATE utf8mb4_bin NOT NULL,

`millid` smallint(6) NOT NULL,

`kwatts_s` int(11) NOT NULL,

`date` date NOT NULL,

`location` varchar(50) COLLATE utf8mb4_bin NOT NULL,

`active` tinyint(2) NOT NULL DEFAULT '1',

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`strrecordtype` char(3) COLLATE utf8mb4_bin NOT NULL,

PRIMARY KEY (`id`),

KEY `IDX_millid` (`millid`,`active`),

KEY `IDX_active` (`id`,`active`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

/*!80016 SET SDI='{"version":1,"type":"TABLE","name":"wmillAUTOINC1","space_id":5,"schema":"windmills","tablespace":"innodb_file_per_table","engine":"InnoDB","collation_id":255,"row_format":"DYNAMIC","columns":["name":"id","ordinal_position":1,"type":8,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":true,"length":20,"collation_id":63,"name":"uuid","ordinal_position":2,"type":254,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":false,"length":36,"collation_id":46,"name":"millid","ordinal_position":3,"type":2,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":false,"length":6,"collation_id":63,"name":"kwatts_s","ordinal_position":4,"type":3,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":false,"length":11,"collation_id":63,"name":"date","ordinal_position":5,"type":10,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":false,"length":-1,"collation_id":-1,"name":"location","ordinal_position":6,"type":253,"is_nullable":false,"is_unsigned":false,"is_zerofill":false,"is_auto_increment":false,"length":-1,"collation_id":-1,{"name":"active","ordinal_position":7,"type":1,"is_nullable":false,"is_unsigned":true,"is_zerofill":true,"is_auto_increment":

We can see that each table has a comment with a JSON object that contains the SDI information. We can ignore this comment and use only the CREATE TABLE statements to restore the table DDL on another server or directory.

Recovering Table DDL from DBSake or mysqlfrm Tools

If we do not have access to the SDI files or we want to use another method to recover table DDL in MySQL 8 without .frm files, we can use some tools that can read and parse the binary format of InnoDB tablespaces and extract the metadata information from them. Two of these tools are DBSake and mysqlfrm.

DBSake is a collection of command-line tools for MySQL administration and development. One of its features is frmdump, which can dump table DDL from .frm files or InnoDB tablespaces. To use DBSake frmdump on an InnoDB tablespace file (.ibd), we need to specify the --innodb option and provide some additional information about the server version and page size. For example:

dbsake frmdump --innodb --server-version=8.0 wmillAUTOINC1.ibd

This command will output something like this:

-- Table structure for table `wmillAUTOINC1`

-- Created with MySQL Version 8.0

CREATE TABLE `wmillAUTOINC1` (

`id` bigint(11)

Recovering Table DDL from MySQL Utilities

MySQL Utilities is a package of tools that provide various functionalities for MySQL administration and development. One of these tools is mysqlfrm, which can read and parse the binary format of .frm files or InnoDB tablespaces and extract the table structure information from them. The mysqlfrm tool can also spawn a local server instance and execute the table DDL on it, so that we can inspect the table structure using SQL commands.

To recover table DDL from mysqlfrm, we need to have access to the .frm files or the InnoDB tablespace files that contain the table structure information. We also need to have the mysqlfrm tool installed and configured on our system. For example, if we have a .frm file called wmillAUTOINC1.frm in the windmills schema, we can run the following command to recover its table DDL:

mysqlfrm --server=root:rootpassword@localhost windmills/wmillAUTOINC1.frm --port=3307

This command will spawn a local server instance on port 3307 and execute the CREATE TABLE statement for the wmillAUTOINC1 table on it. The output will look something like this:

# Source on localhost: ... connected.

# Starting the spawned server on port 3307 ... done.

# Reading .frm files

#

# Reading the wmillAUTOINC1.frm file.

# CREATE statement for wmillAUTOINC1:

CREATE TABLE `wmillAUTOINC1` (

`id` bigint(11) NOT NULL AUTO_INCREMENT,

`uuid` char(36) COLLATE utf8mb4_bin NOT NULL,

`millid` smallint(6) NOT NULL,

`kwatts_s` int(11) NOT NULL,

`date` date NOT NULL,

`location` varchar(50) COLLATE utf8mb4_bin NOT NULL,

`active` tinyint(2) NOT NULL DEFAULT '1',

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`strrecordtype` char(3) COLLATE utf8mb4_bin NOT NULL,

PRIMARY KEY (`id`),

KEY `IDX_millid` (`millid`,`active`),

KEY `IDX_active` (`id`,`active`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

We can see that this command generates a CREATE TABLE statement that matches the original table structure. However, this command does not include any indexes or constraints that may exist on the table. To include them, we need to use the --diagnostic option and provide an InnoDB tablespace file (.ibd) instead of a .frm file.

Best Practices for Table DDL Recovery in MySQL 8

As we have seen, recovering table DDL in MySQL 8 without .frm files is possible, but not always easy or straightforward. Depending on the situation, we may need to use different tools and techniques, and deal with various challenges and limitations. Therefore, it is advisable to follow some best practices that can help us avoid or minimize the need for table DDL recovery in MySQL 8, such as:

• Backup your database regularly and test your backup files for consistency and integrity.

• Keep your table definitions in SQL format under version control and update them whenever you make any changes.

• Monitor your database performance and health and fix any issues or errors as soon as possible.

• Use reliable hardware and software and avoid any actions that can cause data corruption or loss.

• Seek professional help if you encounter any serious problems or disasters that require data recovery.

By following these best practices, you can ensure that your MySQL database is safe and secure, and that you can recover your table DDL in MySQL 8 without .frm files if needed.

Conclusion

In this article, we have explored how to recover table DDL in MySQL 8 without .frm files, using different tools and techniques. We have seen that MySQL 8 has introduced a new data dictionary that stores all the metadata information inside InnoDB tablespaces, replacing the old .frm files. This change has many advantages, but also some challenges for backup and recovery scenarios. We have learned how to use SDI files, DBSake or mysqlfrm tools, backup files, and information schema views to extract and restore table structure information in MySQL 8. We have also discussed some best practices that can help us avoid or minimize the need for table DDL recovery in MySQL 8.

a8ba361960