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

This article introduces the relevant knowledge of "what are the default database classifications in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Default database in MySQL

Default database classification:

Information_schema

Performance_schema

Mysql

Sys

Information_schema

Information about all databases of the MySQL service is saved.

How many databases are there in the specific MySQL service, what tables are in each database, what data types are the fields in each table, what indexes are in each table, and what permissions are required for each database to access.

Mysql

Save permissions, parameters, objects, and status information for MySQL.

For example, which user can access this data, DB parameters, plug-ins, master and slave

Performance_schema

Mainly used to collect database server performance parameters

Provide details of the process waiting, including locks, mutexes, and file information

Save historical event summary information to make a detailed judgment for providing MySQL server performance

It is very easy to add and delete monitoring event points, and you can change the monitoring cycle of mysql server at will, such as (CYCLE, MICROSECOND)

Use show databases; to view all databases.

The four system built-in libraries are: information_schema, performance_schema, mysql, sys

Information_schema

Information_schema provides a way to access database metadata. Metadata is data about data, such as database or table names, data types of columns, or access permissions. Other terms that are sometimes used to describe this information include "data dictionary" and "system catalog".) In other words, information_schema is an information database that holds information about all other databases maintained by the MySQL server. (such as the name of the database, 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 system views, not basic tables.

View the specific table:

Use information_schema;show tables

SCHEMATA table: stores the basic information of all mysql databases, including database name, coding type path, etc.

TABLES table: provides information about the tables in the database (including views), detailing which schema a table belongs to, the table type (whether the table is a basic table or a system view), the table engine (what is the engine of the database), how many rows the table has, when it was created, when it was last updated, and so on. The result of the show tables from specific database name is taken from this table.

COLUMNS table: provides column information in a table, detailing all columns in a table and information about each column, for example, which column is in the table, the data type of the column, the encoding type of the column, the permissions of the column, the comments of the column, and so on. Show columns from database name. The result of the table name is taken from this table.

Mysql

The core database of mysql, similar to the master table in sql server, 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. (it is common to change the password of a root user in the mysql.user table).

Use mysql;show tables;performance_schema

Mainly used to collect database server performance parameters. And the storage engine of the library table is PERFORMANCE_SCHEMA, and the user cannot create a table with the storage engine of PERFORMANCE_SCHEMA. MySQL5.7 is enabled by default.

Sys

All the data sources of the Sys library come from: performance_schema. The goal is to reduce the complexity of performance_schema so that DBA can better read the contents of the library. Let DBA know the operation of DB faster.

Differences between MySQL 5.0 and MySQL 5.0 or later

There is an information database, INFORMATION_SCHEMA, that stores database information in MySQL version 5.0.It stores information about all other databases maintained by the MySQL server. Such as the name of the database, the table of the database, the data type and access of the table column, etc. But not below 5.0.

That's all for "what are the default database classifications in MySQL?" Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.