Documentation Home
MySQL 9.3 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.8Mb
PDF (A4) - 40.9Mb
Man Pages (TGZ) - 261.1Kb
Man Pages (Zip) - 368.3Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.3 Reference Manual  / MySQL Server Administration  /  The mysql System Schema

7.3 The mysql System Schema

Themysql 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 singleInnoDB tablespace file namedmysql.ibd in the MySQL data directory. Previously, these tables were created in individual tablespace files in themysql database directory.

Data-at-rest encryption can be enabled for themysql system schema tablespace. For more information, seeSection 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.

  • catalogs: Catalog information.

  • character_sets: Information about available character sets.

  • check_constraints: Information aboutCHECK constraints defined on tables. SeeSection 15.1.22.6, “CHECK Constraints”.

  • collations: Information about collations for each character set.

  • column_statistics: Histogram statistics for column values. SeeSection 10.9.6, “Optimizer Statistics”.

  • column_type_elements: Information about types used by columns.

  • columns: Information about columns in tables.

  • dd_properties: A table that identifies data dictionary properties, such as its version. The server uses this to determine whether the data dictionary must be upgraded to a newer version.

  • events: Information about Event Scheduler events. SeeSection 27.5, “Using the Event Scheduler”. If the server is started with the--skip-grant-tables option, the event scheduler is disabled and events registered in the table do not run. SeeSection 27.5.2, “Event Scheduler Configuration”.

  • foreign_keys,foreign_key_column_usage: Information about foreign keys.

  • index_column_usage: Information about columns used by indexes.

  • index_partitions: Information about partitions used by indexes.

  • index_stats: Used to store dynamic index statistics generated whenANALYZE TABLE is executed.

  • indexes: Information about table indexes.

  • innodb_ddl_log: Stores DDL logs for crash-safe DDL operations.

  • parameter_type_elements: Information about stored procedure and function parameters, and about return values for stored functions.

  • parameters: Information about stored procedures and functions. SeeSection 27.2, “Using Stored Routines”.

  • resource_groups: Information about resource groups. SeeSection 7.1.16, “Resource Groups”.

  • routines: Information about stored procedures and functions. SeeSection 27.2, “Using Stored Routines”.

  • schemata: Information about schemata. In MySQL, a schema is a database, so this table provides information about databases.

  • st_spatial_reference_systems: Information about available spatial reference systems for spatial data.

  • table_partition_values: Information about values used by table partitions.

  • table_partitions: Information about partitions used by tables.

  • table_stats: Information about dynamic table statistics generated whenANALYZE TABLE is executed.

  • tables: Information about tables in databases.

  • tablespace_files: Information about files used by tablespaces.

  • tablespaces: Information about active tablespaces.

  • triggers: Information about triggers.

  • view_routine_usage: Information about dependencies between views and stored functions used by them.

  • view_table_usage: Used to track dependencies between views and their underlying tables.

Data dictionary tables are invisible. They cannot be read withSELECT, do not appear in the output ofSHOW TABLES, are not listed in theINFORMATION_SCHEMA.TABLES table, and so forth. However, in most cases there are correspondingINFORMATION_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 themysql.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: mysqlDEFAULT_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_schemaDEFAULT_CHARACTER_SET_NAME: utf8mb3    DEFAULT_COLLATION_NAME: utf8mb3_general_ci                  SQL_PATH: NULL        DEFAULT_ENCRYPTION: NO*************************** 3. row ***************************              CATALOG_NAME: def               SCHEMA_NAME: performance_schemaDEFAULT_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 noINFORMATION_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_SCHEMAREFERENTIAL_CONSTRAINTS andKEY_COLUMN_USAGE tables; these tables are now implemented as views on theforeign_keys,foreign_key_column_usage, and other data dictionary tables.

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, seeSection 8.2.3, “Grant Tables”.

The MySQL 9.3 grant tables areInnoDB (transactional) tables. Account-management statements are transactional and either succeed for all named users or roll back and have no effect if any error occurs.

  • user: User accounts, global privileges, and other nonprivilege columns.

  • global_grants: Assignments of dynamic global privileges to users; seeStatic Versus Dynamic Privileges.

  • db: Database-level privileges.

  • tables_priv: Table-level privileges.

  • columns_priv: Column-level privileges.

  • procs_priv: Stored procedure and function privileges.

  • proxies_priv: Proxy-user privileges.

  • default_roles: This table lists default roles to be activated after a user connects and authenticates, or executesSET ROLE DEFAULT.

  • role_edges: This table lists edges for role subgraphs.

    A givenuser table row might refer to a user account or a role. The server can distinguish whether a row represents a user account, a role, or both by consulting therole_edges table for information about relations between authentication IDs.

  • password_history: Information about password changes.

Object Information System Tables

These system tables contain information about components, loadable functions, and server-side plugins:

Log System Tables

The server uses these system tables for logging:

  • general_log: The general query log table.

  • slow_log: The slow query log table.

Log tables use theCSV storage engine.

For more information, seeSection 7.4, “MySQL Server Logs”.

Server-Side Help System Tables

These system tables contain server-side help information:

  • help_category: Information about help categories.

  • help_keyword: Keywords associated with help topics.

  • help_relation: Mappings between help keywords and topics.

  • help_topic: Help topic contents.

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

Time Zone System Tables

These system tables contain time zone information:

  • time_zone: Time zone IDs and whether they use leap seconds.

  • time_zone_leap_second: When leap seconds occur.

  • time_zone_name: Mappings between time zone IDs and names.

  • time_zone_transition,time_zone_transition_type: Time zone descriptions.

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

Replication System Tables

The server uses these system tables to support replication:

All of the tables just listed use theInnoDB storage engine.

Optimizer System Tables

These system tables are for use by the optimizer:

Miscellaneous System Tables

Other system tables do not fit the preceding categories:

  • audit_log_filter,audit_log_user: If MySQL Enterprise Audit is installed, these tables provide persistent storage of audit log filter definitions and user accounts. SeeAudit Log Tables.

  • firewall_group_allowlist,firewall_groups,firewall_memebership,firewall_users,firewall_whitelist: If MySQL Enterprise Firewall is installed, these tables provide persistent storage for information used by the firewall. SeeSection 8.4.8, “MySQL Enterprise Firewall”.

  • servers: Used by theFEDERATED storage engine. SeeSection 18.8.2.2, “Creating a FEDERATED Table Using CREATE SERVER”.

  • innodb_dynamic_metadata: Used by theInnoDB storage engine to store fast-changing table metadata such as auto-increment counter values and index tree corruption flags. Replaces the data dictionary buffer table that resided in theInnoDB system tablespace.