MySQL® Administrator's Bible

Chapter 21. The MySQL Data Dictionary

IN THIS CHAPTER

• Examining the Object catalog

• System information

• Managing permissions

• Developing custom metadata

MySQL stores information about the data in the databases; this is called metadata. Much of this information is stored in the INFORMATION_SCHEMA database, following the SQL 2003 standard.

Tables in the INFORMATION_SCHEMA database are read-only, in-memory, and show data from various sources.

SHOW CREATE TABLE will show the tables as TEMPORARY, because they reside in memory and do not persist between mysqld restarts. INFORMATION_SCHEMA tables are called system views and they may be of different storage engine types. At the time of this writing all the system views are either the MEMORY, MyISAM, or Maria storage engine.

Regular SQL statements can be used to query them, though they have some special properties that other views do not have:

• mysqldump will not export any information (data, schema) from INFORMATION_SCHEMA system views

• There is no data directory for the INFORMATION_SCHEMA database

• There is no .frm file associated with the INFORMATION_SCHEMA views. The definitions are hard-coded into the database.

The table definitions for the data dictionary are hard-coded into the source code, and loaded when mysqld starts. Unlike other databases, there is no directory in the datadir for the INFORMATION_SCHEMA database. All users have permission to see the INFORMATION_SCHEMA database; however, they can only see the objects they have permission to see. ...