Pre-General Availability Draft: 2017-11-22
mysql database is the system database. It
contains tables that store information required by the MySQL
server as it runs. A broad categorization is that the
mysql database 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 the
InnoDB storage engine unless otherwise
mysql system tables and data dictionary tables
reside in a single
InnoDB tablespace file named
mysql.ibd in the MySQL data directory.
Previously, these tables were created in individual tablespace
files in the
mysql database directory.
These tables comprise the data dictionary, which contains metadata about database objects. For additional information, see Chapter 14, MySQL Data Dictionary.
The data dictionary is new in MySQL 8.0. A data dictionary-enabled server entails some general operational differences compared to previous MySQL releases. For details, see Section 14.7, “Data Dictionary Usage Differences”. Also, for upgrades to MySQL 8.0 from MySQL 5.7, the upgrade procedure differs somewhat from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Section 2.10.1, “Upgrading MySQL”, particularly Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation.
catalogs: Catalog information.
character_sets: Information about available character sets.
collations: Information about collations for each character set.
column_statistics: Histogram statistics for column values. See Section 8.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. See Section 24.4, “Using the Event Scheduler”. The server loads events listed in this table during its startup sequence, unless started with the
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 when
ANALYZE TABLEis 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. See Section 24.2, “Using Stored Routines (Procedures and Functions)”.
resource_groups: Information about resource groups. See Section 8.12.5, “Resource Groups”
routines: Information about stored procedures and functions. See Section 24.2, “Using Stored Routines (Procedures and Functions)”.
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 when
ANALYZE TABLEis 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 with
SELECT, do not appear in the
SHOW TABLES, are not
listed in the
table, and so forth. However, in most cases there are
INFORMATION_SCHEMA tables that
can be queried. Conceptually, the
INFORMATION_SCHEMA provides a view through
which MySQL exposes data dictionary metadata. For example, you
cannot select from the
mysql> SELECT * FROM mysql.schemata; ERROR 3554 (HY000): Access to data dictionary table 'mysql.schemata' is rejected.
Instead, select that information from the corresponding
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 *************************** 2. row *************************** CATALOG_NAME: def SCHEMA_NAME: information_schema DEFAULT_CHARACTER_SET_NAME: utf8 DEFAULT_COLLATION_NAME: utf8_general_ci SQL_PATH: NULL ...
There is no
INFORMATION_SCHEMA table that
corresponds exactly to
contains much of the same information.
As of yet, there are no
tables that correspond exactly to
mysql.foreign_key_column_usage. The standard
SQL way to obtain foreign key information is by using the
KEY_COLUMN_USAGE tables; these
tables are now implemented as views on the
foreign_key_column_usage, and other data
Some system tables from before MySQL 8.0 have been
replaced by data dictionary tables and are no longer present in
mysql system database:
eventsdata dictionary table supersedes the
eventtable from before MySQL 8.0.
routinesdata dictionary tables together supersede the
proctable from before MySQL 8.0.
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 6.2.3, “Grant Tables”.
As of MySQL 8.0, the grant tables are
(transactional) tables. Previously, these were
MyISAM (nontransactional) tables. The change
of grant-table storage engine underlies an accompanying change
in MySQL 8.0 to the behavior of account-management statements
CREATE USER and
GRANT. 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.
If MySQL is upgraded from an older version but the grant
tables have not been upgraded from
InnoDB, the server considers them read
only and account-management statements produce an error. For
upgrade instructions, see Section 2.10.1, “Upgrading MySQL”.
user: User accounts, global privileges, and other non-privilege columns.
global_grants: Assignments of dynamic global privileges to users; see Section 6.2.2, “Static 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 executes
SET ROLE DEFAULT.
role_edges: This table lists edges for role subgraphs.
usertable row might refer to a user account or a role. The server can distinquish whether a row represents a user account, a role, or both by consulting the
role_edgestable for information about relations between authentication IDs.
password_history: Information about password changes.
These system tables contain information about stored programs, components, user-defined functions, and server-side plugins:
component: The registry for server components. Any components listed in this table are installed by a loader service during the server startup sequence. See Section 5.5, “MySQL Server Components”.
func: Information about user-defined functions (UDFs). See Section 29.4, “Adding New Functions to MySQL”. The server loads UDFs listed in this table during its startup sequence, unless started with the
plugin: Information about server-side plugins. See Section 5.6.1, “Installing and Uninstalling Plugins”, and Section 29.2, “The MySQL Plugin API”. The server loads plugins listed in this table during its startup sequence, unless started with the
The server uses these system tables for logging:
Log tables use the
CSV storage engine.
For more information, see Section 5.4, “MySQL Server Logs”.
These system tables contain server-side help information:
For more information, see Section 5.1.10, “Server-Side Help”.
These system tables contain time zone information:
For more information, see Section 10.6, “MySQL Server Time Zone Support”.
The server uses these system tables to support replication:
gtid_executed: Table for storing GTID values. See mysql.gtid_executed Table.
ndb_binlog_index: Binary log information for MySQL Cluster replication. See NDB Cluster Replication Schema and Tables.
ndb_binlog_indextable uses the
MyISAMstorage engine. It is created only if the server is built with
slave_worker_info: Used to store replication information on slave servers. See Section 18.2.4, “Replication Relay and Status Logs”.
These system tables are for use by the optimizer:
innodb_table_stats: Used for
InnoDBpersistent optimizer statistics. See Section 126.96.36.199, “Configuring Persistent Optimizer Statistics Parameters”.
engine_cost: The optimizer cost model uses tables that contain cost estimate information about operations that occur during query execution.
server_costcontains optimizer cost estimates for general server operations.
engine_costcontains estimates for operations specific to particular storage engines. See Section 8.9.5, “The Optimizer Cost Model”.
Other system tables do not fit the preceding categories:
servers: Used by the
FEDERATEDstorage engine. See Section 188.8.131.52, “Creating a FEDERATED Table Using CREATE SERVER”.
innodb_dynamic_metadata: Used by the
InnoDBstorage 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 the