Easy Steps To A Winning Read.FRM file Strategy
- 106 Views
- Blogger
- July 21, 2023
- Technology
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 Read.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.
Read Blog:
.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.
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 inRead.FRM file
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
Read.FRM file
, the 64KB table definition size limit imposed by the.frm
file structure is removed. - With the removal of
.frm
files, theINFORMATION_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, thesync_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
andDROP 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 stableINFORMATION_SCHEMA
interface for application use. SomeINFORMATION_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 eachINFORMATION_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-viewINFORMATION_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 theINFORMATION_SCHEMA
table using a temporary table per query.
SHOW
statements that display information corresponding to theINFORMATION_SCHEMA
tables that are viewed on data dictionary tables. For example,SHOW DATABASES
displays the same information as theSCHEMATA
table.ForINFORMATION_SCHEMA
queries that retrieve table statistics, the server now can use statistics cached inINFORMATION_SCHEMA
tables, or obtain the latest statistics directly from storage engines. Theinformation_schema_stats
system variable controls which statistics source the server uses.- When
information_schema_stats
isCACHED
(the default), the server uses cached statistics stored in theSTATISTICS
andTABLES
tables. - When
information_schema_stats
isLATEST
, the server obtains statistics directly from storage engines. In this case, the server treats queries onSTATISTICS
andTABLES
as queries for the latest statistics stored in theSTATISTICS_DYNAMIC
andTABLES_DYNAMIC
tables.AffectedINFORMATION_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
- The server no longer must create a temporary table for each query of the