"Reprint" MySQL common system table Daquan

Reprint Address: http://blog.csdn.net/xlxxcc/article/details/51754524

MySQL5.7 default mode is: Information_schema, with 61 tables, M ysql, with 31 tables, Performance_schema, with 87 tables; SYS, with 1 tables and 100 views.

The information_schema database is a MySQL-band that provides a way to access database metadata. What is meta data? Metadata is data about the data, such as the database name or table name, the data type of the column, or access permissions. Some of the other terms used to express this information include the data dictionary and the system catalog.

in MySQL, think of Information_schema as a database, specifically an information database. It holds information about all the other databases maintained by the MySQL server. such as database name, database table, table column data type and access permissions. In Information_schema, there are a few read-only tables. They are actually views, not basic tables, so you will not be able to see any files associated with them

1. MYSQL SHOW Command

desc table name;Table information
Show columns from table name;Table field
describe table name;Table information
Show create TABLE table name; Table Creation Statement
show create database name;displaying database information
Show table status from database name;Database status
show tables or show tables from database_name;Displays the names of all tables in the current database
show databases;Displays the names of all databases in MySQL
show Processlist;Displays all the processes that are running in the system, that is, the query that is currently executing. Most users can view their own processes, but if they have process permissions, they can see all processes, including passwords.
Show table status;Displays information about each table in the database that is currently being used or specified. Information includes the table type and the last updated time of the table
Show columns from table_name to database_name;Display column names in a table
show columns from Database_name.table_name;Display column names in a table
Show grants for [email protected];Displays the permissions of a user, displaying results similar to the grant command
Show index from table_name;Displays the index of the table show status; Explanation: Displays information about some system-specific resources, such as the number of threads running
show variables;Displays the name and value of the system variable show privileges; Explanation: Show different permissions supported by the server
Show CREATE DATABASE database_name;Displays whether the CREATE DATABASE statement can create the specified databases
Show CREATE TABLE table_name;Displays whether the CREATE DATABASE statement can create the specified databases
show Engies;Displays the storage engine and the default engine that are available after installation.
show InnoDB status;Displays the status of the InnoDB storage engine
show logs;Displaying logs for the BDB storage engine
show warnings;Displays errors, warnings, and notifications resulting from the last statement executed
Show errors;Show only errors resulting from the last execution statement

Use the following statement to view the number of IP connections for the current database

select SUBSTRING_INDEX(host,‘:‘,1) as ip , count(*)from information_schema.processlist group by ip

• 1
• 2

• 1
• 2

2. Information_schema Database Section table Description
? schemata table:Provides information for all databases in the current MySQL instance. This table is the result of show databases.
? Tables table:Provides information about the tables in the database, including views. Describes in detail which schema, table type, table engine, creation time, and so on, a table belongs to. This table is the result of show tables from SchemaName.
? Columns table:Provides the column information in the table. Describes in detail all the columns of a table and the information for each column. This table is the result of show columns from Schemaname.tablename.
? Statistics Table:Provides information about the index of a table. This table is the result of show index from Schemaname.tablename.
? User_privileges (User Rights) Table:The information about the full-length permission is given. This information is derived from the Mysql.user authorization form. Non-standard tables.
? Schema_privileges (Program permissions) Table:Provides information about the program (database) permissions. This information is from the Mysql.db authorization form. Non-standard tables.
? Table_privileges (table permissions) Table:Provides information about the permissions of a table. This information is derived from the Mysql.tables_priv authorization form. Non-standard tables.
? Column_privileges (column permissions) Table:Gives information about column permissions. This information is derived from the Mysql.columns_priv authorization form. Non-standard tables.
? Character_sets (character Set) Table:Provides information about the available character sets for MySQL instances. This table is the show CHARACTER set result set.
? Collations table:Provides cross-reference information about each character set.
? Collation_character_set_applicability 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:A table describing the existence of constraints. And the constraint type of the table.
? Key_column_usage table:Describes the key columns that have constraints.
? Routines Table:Provides information about storing subroutines (stored programs and functions). At this point, the routines table does not contain a custom function (UDF). A column named "Mysql.proc name" indicates the corresponding INFORMATION_SCHEMA. The Mysql.proc table column for the routines table.
? Views Table:Gives information about the views in the database. You need to have show views permission, otherwise you cannot view the view information.
? Triggers table:Provides information about the triggering program. You must have super permissions to view the table.

3. Performance_schema Database Section table Description  
     Performance_schema This feature is turned off by default. Parameters need to be set: Performance_schema can start the function, this parameter is a static parameter, can only be written in MY.CNF can not be modified dynamically.  
setup_table:   Settings table, configuring monitoring options. &NBSP
current_events_table:   Record What's going on with those thread currently. &NBSP
history_table:   History Table of various events that occurred  
summary_table:   Statistics on various events  
setup_consumers\ setup_instruments:   Describes various events, sets which events can be collected  
setup_instruments:   Describes the name of the table under this database and whether to turn on monitoring. &NBSP
setup_timers:   Describes the time interval for which the monitoring option has sampled frequency  
Threads:   Monitor all connections to the server  
performance_timers:   Set Some monitoring information to specify the monitoring cycle available for the MySQL service, cycle means 2,603,393,034 detections per second , currently performance-schema only supports ' wait ' time monitoring, and functions under wait/on the code tree can be monitored.

4. MySQL Database section table description
In the MySQL database, there are mysql_install_db scripts to initialize the permissions table, and the tables that store the permissions are:
1. User table: Users column, permission column, security column, Resource control column
2. DB table: User column, permission column
3. Host Table
4. Table_priv table
5. Columns_priv table
6. Proc_priv Table

5. sys database table Description
Sys_config: This is the only table that exists on this system library, and the table structure is as follows:

CREATETABLE' Sys_config ' (' Variable 'varchar128)NotNull ' value ' varchar (128) default null,  ' set_time ' timestamp not NULL default current_timestamp ON update current_timestamp,  ' set_by ' varchar (128) default null, primary KEY ( variable ')) Engine=innodb default Charset=utf8     

• 1
• 2
• 3
• 4
• 5
• 6
• 7

• 1
• 2
• 3
• 4
• 5
• 6
• 7

? variable: configuration option name
? Value : Configuration option value
? Set_time: The line configures the modified time
? set_by: The line configuration information modifier, if the installation has not been modified, then this data should be null

The session variable for the above values is the Variable field in the @sys.+ table, for example: @sys. Statement_truncate_len
You can set @sys. Statement_truncate_len = 32 To temporarily change the value, this value will be used in the session, and if you want to restore the default value of using the table, you only need to set the session value to Null;set @sys. Statement_ Truncate_len = null;
? Diagnostics.allow_i_s_tables: Default is off, if open means allow diagnostics () stored procedure to perform scan information_schema.tables table, if the table is many, it may be very performance-consuming,
? Diagnostics.include_raw: The default is off, and turning on will output unprocessed data from the metrics view.
? Statement_performance_analyzer.limit: The maximum number of rows returned when the view has no limit restriction
? Statement_performance_analyzer.view
? Statement_truncate_len: The maximum length of the value returned by the Format_statement () function

This table has a non-default option and a @sys.debug parameter that can be added manually:

INSERT INTO sys_config (variable, value) VALUES(‘debug‘, ‘ON‘);UPDATE sys_config SET value = ‘OFF‘ WHERE variable = ‘debug‘;SET @sys.debug = NULL;

• 1
• 2
• 3

• 1
• 2
• 3

5.1. There are two triggers for this table
5.1.1 Sys_config_insert_set_user Trigger
If you add a new row through the INSERT statement, the trigger will set the Set_by column as the current operator
5.1.2 Sys_config_update_set_user Trigger
If you add a new row through the UPDATE statement, the trigger will set the Set_by column as the current operator

5.2. View: The following sections only describe the content of the view that does not contain x$
5.2.1 Host_summary (host profile)
? Host: Listen for connected hosts
? Statements: Total number of statements executed by the current host
? Statement_latency: Statement Wait time (delay time)
? Statement_avg_latency: Average delay time for executing statements
? Table_scans: Number of table scans
? File_ios:io Time Total
? File_io_latency: File IO delay
? Current_connections: Current number of connections
? Total_connections: Total number of links
? Unique_users: Number of unique users for this host
? Current_memory: Memory allocated by current account
? Total_memory_allocated: Total memory allocated by this host

5.2.2 The Host_summary_by_file_io_type
? Host: Host
? Event_name:io Event Name
? Total: Events that occur on this host
Total_latency: Total IO Event delay time for this host
Max_latency: Maximum delay time for this host IO event

5.2.3 The Host_summary_by_file_io
? Host: Host
? Ios:io total number of events
? Io_latency:io Total Delay time

5.2.4 the Host_summary_by_stages
? Host: Hosts
? Event_Name: Name
? Total: Number of occurrences
? Total_latency: Total delay time
? Avg_latency: Average delay time

5.2.5 the Host_summary_by_statement_latency
? Host: Hosts
? Total: Number of statements for this host
? Total_latency: Total delay time for this host
? Max_latency: Maximum delay time of the host
? Lock_latency: Lock delay time to wait for a lock
? Rows_sent: Total number of rows returned by the host through the statement
? Rows_examined: The number of rows returned by the statement on the storage engine
? Rows_affected: Total number of rows affected by this host through statements
? Full_scans: Total number of statements scanned in full table

5.2.6 The Host_summary_by_statement_type This presentation summarizes the execution statement information, typically grouped by host and claim type. By default, sort by host
? Host: Hosts
? Statement: The last statement event name
? Total number of Total:sql statements
? Total number of Total_latency:sql statement delays
? Max_latency: Maximum number of SQL statement latencies
? Lock_latency: Total Lock delay
? Rows_sent: The total number of rows returned by the statement
? Rows_examined: Total number of rows read by the SQL statement of the storage engine
? Rows_affected: Total number of rows affected by the statement
? Full_scans: Total number of statement events for full table scan

5.2.7 The Innodb_buffer_stats_by_schema This table is the InnoDB cache of the InnoDB engine that is counted through the database
? Object_schema: Database name
? Allocated: The total number of bytes allocated to the current database
? Data: Number of bytes allocated to the current database
? Pages: Total pages allocated to the current database
? pages_hashed: Number of hash pages allocated to the current database
? Pages_old: The old number of pages assigned to the current database
? rows_cached: Number of rows in the current database cache

5.2.8 The innodb_buffer_stats_by_table This table is the INNODB cache for each table InnoDB engine
? Object_schema: Database name
? object_name: Table Name
? Allocated: Total number of bytes allocated to the table
? Data: Number of bytes allocated for this table
? Pages: Number of pages assigned to a table
? Pages_hashed: The number of hash pages allocated to the table
? Pages_old: The old number of pages assigned to the table
? rows_cached: Row cache count for table

5.2.9 The Innodb_lock_waits this table actually from the view statement is INFORMATION_SCHEMA This database innodb_locks, Innodb_trx these two tables integration, Enables clearer display of the current instance's lock condition
? Wait_started: The time the lock waits to occur
? Wait_age: How long has the lock been waiting
? Wait_age_secs: Displays the time in seconds that the lock has been waiting (this column is added in 5.7.9)
? Locked_table: Locked Table
? Locked_index: Locked Index
? Locked_type: Lock Type
? WAITING_TRX_ID: The transaction ID that is waiting
? Waiting_trx_started: Time to wait for transaction to start
? Waiting_trx_age: How long have you been waiting for a transaction
? waiting_trx_rows_locked: Number of rows being locked for a pending transaction
? Waiting_trx_rows_modified: Number of pending row redefinition
? Waiting_pid: The thread ID that is waiting for the transaction
? Waiting_query: Query waiting for a lock
? WAITING_LOCK_ID: The ID of the waiting lock
? Waiting_lock_mode: The mode of waiting for a lock
? BLOCKING_TRX_ID: The transaction ID that blocked the wait lock
? Blocking_pid: The thread ID being locked
? Blocking_query: The query being locked
? BLOCKING_LOCK_ID: The lock ID that is blocking the wait lock.
? Blocking_lock_mode: Blocking lock mode
? Blocking_trx_started: Time to block transaction start
? Blocking_trx_age: The time that the blocked transaction has been executed
? Blocking_trx_rows_locked: The number of rows locked by the blocking transaction
? Blocking_trx_rows_modified: Blocking transactions redefining the number of rows
? Sql_kill_blocking_query:kill statement kills a running blocking transaction and is added in mysql5.7.9
? Sql_kill_blocking_connection:kill statement kills a running blocking transaction in a session and is added in mysql5.7.9

5.2.10 The Io_by_thread_by_latency This process the main message is to show the IO wait time through the consumption of IO
? User: For the current thread, this value is the account that the thread is assigned to, which is the name of the thread for the background thread
? Total number of Total:io events
? Total delay of the Total_latency:io event
? Min_latency: Single Minimum IO event latency
? Avg_latency: Average IO Latency
? Max_latency: Maximum IO Latency
? THREAD_ID: Thread ID
? PROCESSLIST_ID: This is the ID for the current thread, and null for the background

"Reprint" MySQL common system table Daquan