Mysql System database, mysql database

Mysql System database, mysql database
The mysql System database mainly stores some system information tables that store the MySQL service. In general, the tables in the mysql database are all MYASIM engines, unless in some cases. Mysql database tables can be divided into the following categories: (1) Authorization System Table (2) System Object Information System Table (3) log system table (4) server auxiliary system table (5) time zone System Table (6) Copy related system table (7) optimizer related system table (8) other system tables. Let's take a look at the following details: 1. Some Tables authorized by the Grant System Tables mainly include the following:

user: User accounts, global privileges, and other non-privilege columns.db: Database-level privileges.tables_priv: Table-level privileges.columns_priv: Column-level privileges.procs_priv: Stored procedure and function privileges.proxies_priv: Proxy-user privileges.

Basically, each system table contains a permission column and a range column, which also contains some primary information tables for authorization.

The user table controls whether a user can connect to the database. If the user table can be connected, information about the user's permissions is displayed. This table applies to all databases on the instance. The database table range column determines which users can access which databases from which hosts. The permission column determines the allowed operations. The permissions granted at the database level apply to all objects in the database and database, such as tables and stored programs. Tables_priv and columns_priv have more detailed table-level and column-level permissions. The procs_priv table is applicable to stored routines (procedures and functions ). The permissions granted at the general level only apply to a single process or function. Proxies_priv indicates that the user can act as a proxy, or whether the user has the permission to grant other users the proxy permission. Ii. System Object Information System Table event: system table of event. Each system startup will load it again unless the -- skip-grant-tables parameter is specified to start, otherwise, it will all load func: stores user-defined methods, unless the -- skip-grant-tables parameter is specified to start, otherwise the system will load again each time. Plugin: stores information about the server plug-in. Unless the -- skip-grant-tables parameter is specified to start, the system will load the plug-in again each time. The default storage engine is INNODBproc: The system table that stores stored procedures and methods. Iii. log system table. Generally, I can see that all tables are NULL.

general_log: The general query log table.slow_log: The slow query log table.

These two system tables can store slow logs and logs in the form of tables. However, these two tables have a higher performance and storage space, we recommend that you set it to an external file in a production environment.

Iv. Server auxiliary system table

help_category: Information about help categories.help_keyword: Keywords associated with help topics.help_relation: Mappings between help keywords and topics.help_topic: Help topic contents.

These tables store basic mysql HELP information. We can use the HELP + column to view the specific HELP information.

Example 5: related system table of time zone

time_zone: Time zone IDs and whether they use leap seconds.time_zone_leap_second: When leap seconds occur.time_zone_name: Mappings between time zone IDs and names.time_zone_transition, time_zone_transition_type: Time zone descriptions.

Set global time_zone = timezone;

You can use the following method to view: SELECT @ global. time_zone, @ session. time_zone; 6: Copy related table gtid_executed: stores information about GTID executed by replication. Metadata: slave_master_info, metadata, and metadata: related information is stored only when mysql is used as the slave. 7: innodb_index_stats and innodb_table_stats of optimizer: index information is displayed. Table 15.3 Columns of innodb_table_stats

Column name	Description
Database_name	Database name
Table_name	Table name, partition name, or subpartition name
Last_update	A timestamp indicating the last time that InnoDB updated this row
N_rows	The number of rows in the table
Clustered_index_size	The size of the primary index, in pages
Sum_of_other_index_sizes	The total size of other (non-primary) indexes, in pages

Table 15.4 Columns of innodb_index_stats

Column name	Description
Database_name	Database name
Table_name	Table name, partition name, or subpartition name
Index_name	Index name
Last_update	A timestamp indicating the last time that InnoDB updated this row
Stat_name	The name of the statistic, whose value is reported in the stat_value column
Stat_value	The value of the statistic that is named in stat_name column
Sample_size	The number of pages sampled for the estimate provided in the stat_value column
Stat_description	Description of the statistic that is named in the stat_name column

The last_update columns of the two tables record the last index change time. Both tables can be updated manually. modifying these two tables can force the execution plan of a query or test the alternative plan. The advantage is that you do not need to modify the database information. To update statistics, use flush table tbl_name to update statistics. You can query the index information of a table:

SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name='t1' AND stat_name = 'size' GROUP BY index_name;SELECT index_name, stat_name, stat_value, stat_description FROM mysql.innodb_index_stats WHERE table_name like 't1';SELECT index_name, stat_name, stat_value, stat_description FROM mysql.innodb_index_stats WHERE table_name like 't1' AND stat_name LIKE 'n_diff%';

Server_cost: server operation cost estimation information

Engine_cost: Specify the storage engine's cost estimation information. The FLUSH OPTIMIZER_COSTS command can read record information again. 8. Other system tables audit_log_filter and audit_log_user: These two tables provide information related to mysql audit. If they are not installed, they do not exist. Firewall_users, firewall_whitelist: mysql Enterprise Firewall must be installed. Servers: Mainly used by the FEDERATED engine. I will not introduce it. This is a rough introduction to the mysql System database. What we need to know is about authorization, system tables, log tables, optimized tables, and replication-related tables. Next, let's take a look at the mysql Log Type and other optimization-related aspects.