What does the information_schema database represent?
I have one database in mysql.
But when i log into phpMyAdmin , it shows another database called information_schema.
Is that database always present with one database?
I mean to say is there a copy of information_schema for every database present in mysql or is there one database called inforemation_schema per mysql server?
If i modify this information_schema database how will that affect my current database?
Answer
You can think of information_schema as a "master database" that holds details about all the other databases on the server such as the names and types of tables, columns and users.
What is information_schema?
From the reference documentation:
INFORMATION_SCHEMA provides access to database metadata.
Metadata is data about the data, such as the name of a database or table, the data type of a column, or access privileges. Other terms that sometimes are used for this information are data dictionary and system catalog.
INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. Inside INFORMATION_SCHEMA there are several read-only tables. They are actually views, not base tables, so there are no files associated with them.
What is stored in information_schema?
You can see the kinds of things stored in information_schema, and the way in which they are organised, by viewing this diagram (for MySQL 5.0) or this diagram (for MySQL 5.1).
What happens if I modify information_schema?
In reality, information_schema is a collection of read-only views. As such, it should be impossible to modify it and do any damage. However, the MySQL FAQ on this topic has this to say:
23.7.3: Can I add to or otherwise modify the tables found in the INFORMATION_SCHEMA database?
No. Since applications may rely on a certain standard structure, this should not be modified. For this reason, we cannot support bugs or other issues which result from modifying INFORMATION_SCHEMA tables or data.
This implies that if you do find yourself able to modify information_schema (which should be impossible, and is in MySQL, but other vendor implementations of SQL might allow it) you should at the very least choose not to. If you could damage/modify information_schema you'd be damaging the actual structure (e.g. table names, column types) of your other databases.
Does every user have their own copy of information_schema?
Each user can see information_schema that pertains to the tables and databases they have access to. Some users with heavily limited rights will still see information_schema but will see only NULL values for any information_schema queries. However, it is important to remember that information_schema is not an actual database, but simply a convenient way SQL provides so that you can select information about your database(s) and table(s).
From the reference documentation:
Each MySQL user has the right to access these tables, but can see only the rows in the tables that correspond to objects for which the user has the proper access privileges. In some cases (for example, the ROUTINE_DEFINITION column in the INFORMATION_SCHEMA.ROUTINES table), users who have insufficient privileges will see NULL.
Where can I find more information?
- The MySQL Reference Manual article on information_schema (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html)
- The MySQL FAQ article on information_schema (http://dev.mysql.com/doc/refman/5.0/en/faqs-information-schema.html)