5.3 The mysql System Schema

Inhaltsverzeichnis

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 the mysql 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 the InnoDB storage engine unless otherwise indicated.

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.

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

Data Dictionary Tables

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

Important

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.11, “Upgrading MySQL”, particularly Section 2.11.5, “Preparing Your Installation for Upgrade”.

catalogs: Catalog information.
character_sets: Information about available character sets.
check_constraints: Information about CHECK constraints defined on tables. See Section 13.1.20.7, “CHECK Constraints”.
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 --skip-grant-tables option.
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 when ANALYZE 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. See Section 24.2, “Using Stored Routines”.
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”.
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 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 with SELECT, 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, the INFORMATION_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 corresponding INFORMATION_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: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
DEFAULT_ENCRYPTION: NO
...

There is no INFORMATION_SCHEMA table that corresponds exactly to mysql.indexes, but INFORMATION_SCHEMA.STATISTICS contains much of the same information.

As of yet, there are no INFORMATION_SCHEMA tables that correspond exactly to mysql.foreign_keys, mysql.foreign_key_column_usage. The standard SQL way to obtain foreign key information is by using the INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE tables; these tables are now implemented as views on the foreign_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:

The events data dictionary table supersedes the event table from before MySQL 8.0.
The parameters and routines data dictionary tables together supersede the proc table from before MySQL 8.0.

Inhaltsverzeichnis Haut

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

As of MySQL 8.0, the grant tables are InnoDB (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 such as 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.

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 Section 2.11, “Upgrading MySQL”.

user: User accounts, global privileges, and other nonprivilege columns.
global_grants: Assignments of dynamic global privileges to users; see 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.
A given user table 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_edges table for information about relations between authentication IDs.
password_history: Information about password changes.

Inhaltsverzeichnis Haut

Object Information System Tables

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 --skip-grant-tables option.
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 --skip-grant-tables option.

Inhaltsverzeichnis Haut

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 the CSV storage engine.

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

Inhaltsverzeichnis Haut

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, see Section 5.1.14, “Server-Side Help Support”.

Inhaltsverzeichnis Haut

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, see Section 5.1.13, “MySQL Server Time Zone Support”.

Inhaltsverzeichnis Haut

Replication System Tables

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 NDB Cluster replication. This table is created only if the server is built with NDBCLUSTER support. See Section 22.6.4, “NDB Cluster Replication Schema and Tables”.
slave_master_info, slave_relay_log_info, slave_worker_info: Used to store replication information on slave servers. See Section 17.2.4, “Replication Relay and Status Logs”.

All of the tables just listed use the InnoDB storage engine.

Inhaltsverzeichnis Haut

Optimizer System Tables

These system tables are for use by the optimizer:

innodb_index_stats, innodb_table_stats: Used for InnoDB persistent optimizer statistics. See Section 15.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.
server_cost, engine_cost: The optimizer cost model uses tables that contain cost estimate information about operations that occur during query execution. server_cost contains optimizer cost estimates for general server operations. engine_cost contains estimates for operations specific to particular storage engines. See Section 8.9.5, “The Optimizer Cost Model”.

Inhaltsverzeichnis Haut

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. See Audit Log Tables.
firewall_users, firewall_whitelist: If MySQL Enterprise Firewall is installed, these tables provide persistent storage for information used by the firewall. See Section 6.4.7, “MySQL Enterprise Firewall”.
servers: Used by the FEDERATED storage engine. See Section 16.8.2.2, “Creating a FEDERATED Table Using CREATE SERVER”.
innodb_dynamic_metadata: Used by the InnoDB 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 the InnoDB system tablespace.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-system-schema.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut