Shulou(Shulou.com)05/31 Report--

This article mainly introduces what are the default libraries after installing and initializing mysql, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

Background introduction:

When we install and initialize mysql, several databases are built by default, so what is the use of these databases?

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

4 rows in set (0.00 sec)

1. Information_schema details:

The information_schema database is native to MySQL and provides a way to access database metadata. What is metadata? Metadata is data about data, such as database or table names, data types of columns, or access permissions. Sometimes other terms used to describe this information include "data dictionary" and "system catalog".

In MySQL, think of information_schema as a database, or rather an information database. It holds information about all other databases maintained by the MySQL server. Such as the database name, the table of the database, the data type and access limit of the table column, etc. In INFORMATION_SCHEMA, there are several read-only tables. They are actually views, not basic tables, so you won't be able to see any files associated with them.

Information_schema database table description:

SCHEMATA table: provides information about all databases in the current mysql instance. This table is taken from the results of show databases.

TABLES table: provides information about the tables in the database (including views). Detailed description of a table belongs to which schema, table type, table engine, creation time and other information. This table is taken from the results of show tables from schemaname.

COLUMNS table: provides column information in the table. Details all the columns of a table and the information for each column. This table is taken from the results of show columns from schemaname.tablename.

STATISTICS table: provides information about table indexes. This table is taken from the results of show index from schemaname.tablename.

USER_PRIVILEGES (user permissions) table: gives information about full permissions. This information is derived from the mysql.user authorization table. Is a non-standard table.

SCHEMA_PRIVILEGES (Scheme permissions) table: gives information about scheme (database) permissions. This information is from the mysql.db authorization table. Is a non-standard table.

TABLE_PRIVILEGES (Table permissions) Table: gives information about table permissions. This information is derived from the mysql.tables_priv authorization table. Is a non-standard table.

COLUMN_PRIVILEGES (column permissions) table: gives information about column permissions. This information is derived from the mysql.columns_priv authorization table. Is a non-standard table.

CHARACTER_SETS (character set) table: provides information about the character sets available to the mysql instance. This table is taken from the SHOW CHARACTER SET result set.

COLLATIONS table: provides comparative information about each character set.

COLLATION_CHARACTER_SET_ application table: indicates the character set that can be used for proofreading. These columns are equivalent to the first two display fields of SHOW COLLATION.

TABLE_ constraints table: describes tables that have constraints. And the constraint type of the table.

KEY_COLUMN_ USAGE table: describes key columns with constraints.

ROUTINES table: provides information about storage subroutines (stored procedures and functions). At this point, the ROUTINES table does not contain custom functions (UDF). The column named "mysql.proc name" indicates the mysql.proc column corresponding to the INFORMATION_SCHEMA.ROUTINES table.

VIEWS table: gives information about the views in the database. Show views permission is required, otherwise the view information cannot be viewed.

TRIGGERS table: provides information about triggers. You must have super permission to view the table

2. Introduction to the role of mysql:

Mysql: this is the core database of mysql, which is similar to the master table in sql server. It is mainly responsible for storing the control and management information that mysql needs to use, such as database users, permissions settings, keywords and so on. Can not be deleted, if you do not know much about mysql, do not easily modify the table information in this database.

3. Introduction to the role of performance_schema:

Mysql 5.5 adds a performance-optimized engine: PERFORMANCE_SCHEMA is turned off by default:

Need to set parameters: performance_schema can start this function, this parameter is static, can only be written in my.cnf can not be dynamically modified.

Let's take a look at what there is:

Mysql > use performance_schema

Database changed

Mysql > show tables

+-+

| | Tables_in_performance_schema |

+-+

| | cond_instances |

| | events_waits_current |

| | events_waits_history |

| | events_waits_history_long |

| | events_waits_summary_by_instance |

| | events_waits_summary_by_thread_by_event_name |

| | events_waits_summary_global_by_event_name |

| | file_instances |

| | file_summary_by_event_name |

| | file_summary_by_instance |

| | mutex_instances |

| | performance_timers |

| | rwlock_instances |

| | setup_consumers |

| | setup_instruments |

| | setup_timers |

| | threads |

+-+

17 rows in set (0.00 sec)

The data tables here are divided into several categories:

1) setup table: set the table and configure monitoring options.

2) current events table: record what is happening to those thread right now.

3) A historical record of various events that occurred in history table

4) summary table's statistical tables for various events

5) miscellaneous list, messy table.

Setup table:

Mysql > SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

-> WHERE TABLE_SCHEMA = 'performance_schema'

-> AND TABLE_NAME LIKE 'setup%'

+-+

| | TABLE_NAME |

+-+

| | setup_consumers |

| | setup_instruments |

| | setup_timers |

+-+

Setup_consumers describes various events

Setup_instruments describes the name of the table under this database and whether monitoring is enabled.

Setup_timers describes the time interval at which monitoring options have been sampled

4. Introduction to the role of test:

This is a test database created during installation, which, like its name, is a completely empty database with no tables and can be deleted.

Thank you for reading this article carefully. I hope the article "what are the default libraries after installing and initializing mysql" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.