Skip to Main Content
MySQL® Administrator's Bible
book

MySQL® Administrator's Bible

by Sheeri K. Cabral, Keith Murphy
May 2009
Intermediate to advanced content levelIntermediate to advanced
888 pages
20h 33m
English
Wiley
Content preview from MySQL® Administrator's Bible

Chapter 21. The MySQL Data Dictionary

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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL® Administrator's Guide

MySQL® Administrator's Guide

MySQL AB
Microsoft® SQL Server 2005 Management and Administration

Microsoft® SQL Server 2005 Management and Administration

MCDBA Ross Mistry, MCSE Chris Amaris, MCSE Alec Minty

Publisher Resources

ISBN: 9780470416914Purchase book