7.3 The mysql System Schema (original) (raw)

The mysql schema is the system schema. It contains tables that store information required by the MySQL server as it runs. A broad categorization is that themysql schema contains data dictionary tables that store database object metadata, and system tables used for other operational purposes. The following discussion further subdivides the set of system tables into smaller categories.

The remainder of this section enumerates the tables in each category, with cross references for additional information. Data dictionary tables and system tables use theInnoDB storage engine unless otherwise indicated.

mysql system tables and data dictionary tables reside in a single InnoDB tablespace file namedmysql.ibd in the MySQL data directory. Previously, these tables were created in individual tablespace files in the mysql database directory.

Data-at-rest encryption can be enabled for themysql system schema tablespace. For more information, see Section 17.13, “InnoDB Data-at-Rest Encryption”.

Data Dictionary Tables

These tables comprise the data dictionary, which contains metadata about database objects. For additional information, seeChapter 16, MySQL Data Dictionary.

Data dictionary tables are invisible. They cannot be read withSELECT, do not appear in the output of SHOW TABLES, are not listed in the INFORMATION_SCHEMA.TABLES table, and so forth. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried. Conceptually, theINFORMATION_SCHEMA provides a view through which MySQL exposes data dictionary metadata. For example, you cannot select from the mysql.schemata table directly:

mysql> SELECT * FROM mysql.schemata;
ERROR 3554 (HY000): Access to data dictionary table 'mysql.schemata' is rejected.

Instead, select that information from the correspondingINFORMATION_SCHEMA table:

mysql> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: mysql
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 2. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8mb3
    DEFAULT_COLLATION_NAME: utf8mb3_general_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
*************************** 3. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: performance_schema
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_0900_ai_ci
                  SQL_PATH: NULL
        DEFAULT_ENCRYPTION: NO
...

There is no Information Schema table that corresponds exactly tomysql.indexes, butINFORMATION_SCHEMA.STATISTICS contains much of the same information.

As of yet, there are no INFORMATION_SCHEMA tables that correspond exactly tomysql.foreign_keys,mysql.foreign_key_column_usage. The standard SQL way to obtain foreign key information is by using theINFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS andKEY_COLUMN_USAGE tables; these tables are now implemented as views on theforeign_keys,foreign_key_column_usage, and other data dictionary tables.

Some system tables from before MySQL 8.0 have been replaced by data dictionary tables and are no longer present in the mysql system schema:

Grant System Tables

These system tables contain grant information about user accounts and the privileges held by them. For additional information about the structure, contents, and purpose of the these tables, see Section 8.2.3, “Grant Tables”.

As of MySQL 8.0, the grant tables are InnoDB (transactional) tables. Previously, these wereMyISAM (nontransactional) tables. The change of grant-table storage engine underlies an accompanying change in MySQL 8.0 to the behavior of account-management statements such as CREATE USER andGRANT. Previously, an account-management statement that named multiple users could succeed for some users and fail for others. The statements are now transactional and either succeed for all named users or roll back and have no effect if any error occurs.

Note

If MySQL is upgraded from an older version but the grant tables have not been upgraded from MyISAM to InnoDB, the server considers them read only and account-management statements produce an error. For upgrade instructions, see Chapter 3, Upgrading MySQL.

Log System Tables

The server uses these system tables for logging:

Log tables use the CSV storage engine.

For more information, see Section 7.4, “MySQL Server Logs”.

Server-Side Help System Tables

These system tables contain server-side help information:

For more information, seeSection 7.1.17, “Server-Side Help Support”.

Time Zone System Tables

These system tables contain time zone information:

For more information, see Section 7.1.15, “MySQL Server Time Zone Support”.

Optimizer System Tables

These system tables are for use by the optimizer:

Miscellaneous System Tables

Other system tables do not fit the preceding categories: