You, Me And Read.FRM file: The Truth

A Read.FRM file containing the metadata, such as the table definition, of a MySQL table.

For backups, you must always keep the full set of .frm files along with the backup data to be able to restore tables that are altered or dropped after the backup.

Although each InnoDB table has a .frm file, InnoDB maintains its own table metadata in the system tablespace.

.frm files are backed up by the MySQL Enterprise Backup product. These files must not be modified by an ALTER TABLE operation while the backup is taking place, which is why backups that include non-InnoDB tables perform an FLUSH TABLES WITH READ LOCK operation to freeze such activity while backing up .frm files. Restoring a backup can result in .frm files being created, changed, or removed to match the state of the database at the time of the backup.

A file containing the metadata, such as the table definition, of a MySQL table. .frm files were removed in MySQL 8.0 but are still used in earlier MySQL releases. In MySQL 8.0, data previously stored in .frm files is stored in data dictionary tables.

Read this Blog: https://techqy.com/how-to-access-or-read-frm-file-explain-frm-file-in-mysql/

The following list briefly describes the main implications of this change:

The .frm metadata files previously associated with base tables and views no longer exist. Metadata previously stored in .frm files is now stored in data dictionary tables. Similarly, trigger metadata previously stored in .TRG and .TRN files are stored in a data dictionary table and those files no longer exist.

With the removal of .frm files, the 64KB table definition size limit imposed by the .frm file structure is removed.

With the removal of .frm files, the INFORMATION_SCHEMA.TABLES VERSION the field now reports a hardcoded value of, which is the last .frm file version used in MySQL 5.7.

With the removal of .frm files, the sync_frm system variable is removed.

A new dictionary object cache that serves the MySQL data dictionary stores previously accessed data dictionary objects in memory to enable object reuse and minimize disk I/O. An LRU-based eviction strategy is used to evict the least recently used objects from memory. The cache comprises several partitions that store different object types. For more information, see Dictionary Object Cache.

New internal data dictionary APIs enable the server, internal storage engines, and plugins to access and store data in the MySQL data dictionary. Internal data dictionary APIs are introduced for handling schemas, tablespaces, tablespace files, tables, partitioned tables, table partition data, triggers, stored routines, events, table objects, views, character sets, and collations.With this change, data dictionary updates and binary log writes for CREATE TRIGGER and DROP TRIGGER operations are combined into a single, atomic transaction.

Data dictionary tables are invisible, but in most cases, there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds while maintaining a stable INFORMATION_SCHEMA interface for application use.Some INFORMATION_SCHEMA tables have been reimplemented entirely as views on data dictionary tables:

CHARACTER_SETS

COLLATIONS

COLLATION_CHARACTER_SET_APPLICABILITY

COLUMNS

KEY_COLUMN_USAGE

SCHEMATA

STATISTICS

TABLES

TABLE_CONSTRAINTS

VIEWS

Queries on those tables are now more efficient because they obtain information from data dictionary tables rather than by other, slower means. In particular, for each INFORMATION_SCHEMA table that is a view of data dictionary tables:

The server no longer must create a temporary table for each query of the INFORMATION_SCHEMA table.

When the underlying data dictionary tables store values previously obtained by directory scans (for example, to enumerate database names or table names within databases) or file-opening operations (for example, to read information from .frm files), INFORMATION_SCHEMA queries for those values now use table lookups instead. (Additionally, even for a non-view INFORMATION_SCHEMA table values such as database and table names are retrieved by lookups from the data dictionary and do not require a directory or file scans.)

Indexes on the underlying data dictionary tables permit the optimizer to construct efficient query execution plans, something not true for the previous implementation that processed the INFORMATION_SCHEMA table using a temporary table per query.

The preceding improvements also apply to SHOW statements that display information corresponding to the INFORMATION_SCHEMA tables that are viewed on data dictionary tables. For example, SHOW DATABASES displays the same information as the SCHEMATA table.

For INFORMATION_SCHEMA queries that retrieve table statistics, the server now can use statistics cached in INFORMATION_SCHEMA tables, or obtain the latest statistics directly from storage engines. The information_schema_stats system variable controls which statistics source the server uses.

When information_schema_stats is CACHED (the default), the server uses cached statistics stored in the STATISTICS and TABLES tables.

When information_schema_stats is LATEST, the server obtains statistics directly from storage engines. In this case, the server treats queries on STATISTICS and TABLES as queries for the latest statistics stored in the STATISTICS_DYNAMIC and TABLES_DYNAMIC tables.Affected INFORMATION_SCHEMA table statistic columns include:

STATISTICS.CARDINALITY

TABLES.AUTO_INCREMENT

TABLES.AVG_ROW_LENGTH

TABLES.CHECKSUM

TABLES.CHECK_TIME

TABLES.CREATE_TIME

TABLES.DATA_FREE

TABLES.DATA_LENGTH

TABLES.INDEX_LENGTH

TABLES.MAX_DATA_LENGTH

TABLES.TABLE_ROWS

TABLES.UPDATE_TIME