元（meta）一般会被翻译成中文是”关于...的...”，元数据（meta data）等价于data about data，表示关于数据的数据；
一般是元数据就是结构化数据，例如存储在数据库里的数据，规定了字段的长度。类型等；
元数据就是描述数据的数据，在MySQL中就是描述database的数据，属性，状态等相关信息；
表示在数据库服务中有哪些数据库，库中有哪些表，表中有多少字段，字段是什么类型等等，这样的数据就是数据库的元数据；
方式一：命令获取（show）
# 查询数据库服务中的所有数据库信息（数据库名称-元数据）
mysql> show databases;
# 查询数据库服务中的相应数据表信息（数据表名称-元数据）
mysql> show tables;
mysql> show tables from mysql;
# 查询数据库服务中的建库语句信息 （建库语句参数-元数据 建库语句就是DDL语句，定义建立数据库的属性信息）
mysql> show create database <库名>;
# 查询数据库服务中的建表语句信息 （建表语句参数-元数据 建表语句就是DDL语句，定义建立数据表的属性信息）
mysql> show create table <表名>;
# 查询数据库服务中的数据表的结构（数据表的列定义信息-元数据）
mysql> desc <表名>;
mysql> show columns from <表名>;
# 查询数据库服务中的相应数据表状态 （数据表的状态信息/统计信息-元数据）
mysql> show table status from <库名>;
# 查看数据库服务中的具体数据库表的状态信息（属于单库或单表查询）
mysql> show table status from world like 'city' \G
*************************** 1. row ***************************
Name: city -- 数据表名称信息
Engine: InnoDB -- 使用的数据库引擎信息
Version: 10
Row_format: Dynamic
Rows: 4046 -- 数据表的行数信息
Avg_row_length: 101 -- 平均行长度
Data_length: 409600
Max_data_length: 0
Index_length: 114688 -- 索引长度信息
Data_free: 0
Auto_increment: 4080 -- 自增列的值计数
Create_time: 2022-11-04 09:13:27 -- 数据表创建时间
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci -- 校对规则信息
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# 查询数据库服务中的相应数据表的索引情况（了解即可）
mysql> show index from world.city\G
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: ID
Collation: A
Cardinality: 4035
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: CountryCode
Seq_in_index: 1
Column_name: CountryCode
Collation: A
Cardinality: 232
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)
# 查询数据库服务中的用户权限属性配置信息
mysql> show grants for root@'localhost';
# 查询数据库服务的系统状态信息，表示当前数据库的所有连接情况
mysql> show [full ] processlist;
+----+-----------------+-----------+------+---------+-------+-----------+-------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+-------+-----------+---------- --+
| 5 | event_scheduler | localhost | NULL | Daemon | 14160 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+-------+-----------+-------------+
2 rows in set, 1 warning (0.00 sec)
# 查询数据库服务的所有配置信息
mysql> show variables;
mysql> show variables like '%xx%';
# 查询数据库服务的系统整体状态，表示当前数据库服务运行的即时状态情况
mysql> show status;
mysql> show status like '%lock%';
# 查询数据库服务的所有二进制日志信息（binlog日志）
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 157 | No |
| binlog.000002 | 1591 | No |
| binlog.000003 | 1019 | No |
| binlog.000004 | 583 | No |
| binlog.000005 | 2552 | No |
| binlog.000006 | 3769 | No |
| binlog.000007 | 735358 | No |
| binlog.000008 | 3452 | No |
| binlog.000009 | 157 | No |
| binlog.000010 | 157 | No |
+---------------+-----------+-----------+
10 rows in set (0.04 sec)
# 查询数据库服务正在使用的二进制日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000010 | 157 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 查询数据库服务具体二进制日志内容事件信息
mysql> show binlog events in 'binlog.000010';
+---------------+-----+----------------+-----------+-------------+---------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------+
| binlog.000010 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.37, Binlog ver: 4 |
| binlog.000010 | 126 | Previous_gtids | 1 | 157 | |
+---------------+-----+----------------+-----------+-------------+---------------------+
2 rows in set (0.00 sec)
# 查询数据库服务存储引擎相关信息
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2024-06-29 13:25:56 139934062397184 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 25 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2 srv_active, 0 srv_shutdown, 14365 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 17
OS WAIT ARRAY INFO: signal count 16
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 5903
Purge done for trx's n:o < 5899 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421409191390424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409191389616, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421409191388808, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (read thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (write thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
1537 OS file reads, 314 OS file writes, 136 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 4 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 22408530
Log buffer assigned up to 22408530
Log buffer completed up to 22408530
Log written up to 22408530
Log flushed up to 22408530
Added dirty pages up to 22408530
Pages flushed up to 22408530
Last checkpoint at 22408530
Log minimum file id is 6
Log maximum file id is 6
42 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 528276
Buffer pool size 8192
Free buffers 7067
Database pages 1120
Old database pages 433
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 977, created 143, written 224
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1120, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=945, Main thread ID=139933649987328 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 9, updated 331, deleted 8, read 7356
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
# 在数据库服务主库查看从库信息
mysql> show slave hosts;
# 查询数据库服务主从状态信息
mysql> show slave status;
方式二：视图获取
上面是视图？
将查询基表元数据语句信息方法封装在一个变量或别名中，这个封装好的变量或别名就成为视图，视图信息都是存储在内存中的表
元数据信息存储在系统基表中，通过一般的select命令只能查看数据信息，不能查看到系统基表，以免被随意调整篡改；
而查询基表的语句过于复杂，可以将整个查询基表语句定义为一个视图信息（等价于别名/变量），调取视图等价于调取查询基表语句；
information_schema库中的内存表都是每次数据库服务启动时生成的，里面存储了查询元数据基表的视图信息；
mysql> use information_schema;
mysql> show tables; # 此时看到的所有表信息，其实都是视图信息
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| ADMINISTRABLE_ROLE_AUTHORIZATIONS |
| APPLICABLE_ROLES |
| CHARACTER_SETS |
| CHECK_CONSTRAINTS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMNS_EXTENSIONS |
| COLUMN_PRIVILEGES |
| COLUMN_STATISTICS |
| ENABLED_ROLES |
| ENGINES |
| EVENTS |
| FILES |
| INNODB_BUFFER_PAGE |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_BUFFER_POOL_STATS |
| INNODB_CACHED_INDEXES |
| INNODB_CMP |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_CMP_RESET |
| INNODB_COLUMNS |
| INNODB_DATAFILES |
| INNODB_FIELDS |
| INNODB_FOREIGN |
| INNODB_FOREIGN_COLS |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_CONFIG |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_DELETED |
| INNODB_FT_INDEX_CACHE |
| INNODB_FT_INDEX_TABLE |
| INNODB_INDEXES |
| INNODB_METRICS |
| INNODB_SESSION_TEMP_TABLESPACES |
| INNODB_TABLES |
| INNODB_TABLESPACES |
| INNODB_TABLESPACES_BRIEF |
| INNODB_TABLESTATS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_TRX |
| INNODB_VIRTUAL |
| KEYWORDS |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| RESOURCE_GROUPS |
| ROLE_COLUMN_GRANTS |
| ROLE_ROUTINE_GRANTS |
| ROLE_TABLE_GRANTS |
| ROUTINES |
| SCHEMATA |
| SCHEMATA_EXTENSIONS |
| SCHEMA_PRIVILEGES |
| STATISTICS |
| ST_GEOMETRY_COLUMNS |
| ST_SPATIAL_REFERENCE_SYSTEMS |
| ST_UNITS_OF_MEASURE |
| TABLES |
| TABLESPACES |
| TABLESPACES_EXTENSIONS |
| TABLES_EXTENSIONS |
| TABLE_CONSTRAINTS |
| TABLE_CONSTRAINTS_EXTENSIONS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_ATTRIBUTES |
| USER_PRIVILEGES |
| VIEWS |
| VIEW_ROUTINE_USAGE |
| VIEW_TABLE_USAGE |
+---------------------------------------+
79 rows in set (0.00 sec)
mysql> desc tables;
+-----------------+-------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------------------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(64) | NO | | NULL | |
| TABLE_SCHEMA | varchar(64) # 数据库信息 | NO | | NULL | |
| TABLE_NAME | varchar(64) # 表信息 | NO | | NULL | |
| TABLE_TYPE | enum('BASE TABLE','VIEW','SYSTEM VIEW') | NO | | NULL| |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | int | YES | | NULL | |
| ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES | | NULL | |
| TABLE_ROWS | bigint unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint unsigned | YES | | NULL | |
| DATA_LENGTH | bigint unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint unsigned | YES | | NULL | |
| DATA_FREE | bigint unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint unsigned | YES | | NULL | |
| CREATE_TIME | timestamp | NO | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(64) | YES | | NULL | |
| CHECKSUM | bigint | YES | | NULL | |
| CREATE_OPTIONS | varchar(256) | YES | | NULL | |
| TABLE_COMMENT | text | YES | | NULL | |
+-----------------+-------------------------------------+------+-----+---------+-------+
# 查看获取视图信息创建语句
mysql> show create view tables;
| 字段信息 | 解释说明 |
|---|---|
| TABLE_SCHEMA | 表示数据表所属库的名称信息 |
| TABLE_NAME | 表示数据库中所有数据表名称 |
| ENGINE | 表示数据库服务中的引擎信息 |
| TABLE_ROWS | 表示数据库相应数据表的行数 |
| AVG_ROW_LENGTH | 表示数据表中每行的平均长度 |
| INDEX_LENGTH | 表示数据表中索引信息的长度 |
| DATA_FREE | 表示数据库服务碎片数量信息 |
| CREATE_TIME | 表示数据表创建的时间戳信息 |
| UPDATE_TIME | 表示数据表修改的时间戳信息 |
| TABLE_COMMENT | 表示数据表对应所有注释信息 |
说明：使用information_schema的视图查看功能，可以看到全局数据库或数据表的元数据信息，探究全局层面的元
应用
- 统计数据库资产信息（数据资产），获取每个库中表的个数和名称信息（业务相关）
mysql> select table_schema,count(*),group_concat(table_name) from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_schema') group by table_schema;
+--------------+----------+--------------------------------+
| TABLE_SCHEMA | count(*) | group_concat(table_name) |
+--------------+----------+--------------------------------+
| db01 | 4 | class,student,t_foreign,test |
| db03 | 3 | class,student,t_foreign |
| school | 6 | student,course,sc,teacher,tv,v |
| world | 3 | city,country,countrylanguage |
+--------------+----------+--------------------------------+
4 rows in set (0.00 sec)
- 统计数据库资产信息（数据资产），获取每个数据库数据占用磁盘空间
mysql> select table_schema,sum(table_rows*avg_row_length+index_length)/1024/1024 from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') group by table_schema;
+--------------------+-------------------------------------------------------+
| TABLE_SCHEMA | sum(table_rows*avg_row_length+index_length)/1024/1024 |
+--------------------+-------------------------------------------------------+
| information_schema | 0.00000000 |
| db01 | 0.06250000 |
| db03 | 0.04687500 |
| world | 0.76261139 |
| school | 0.06248379 |
+--------------------+-------------------------------------------------------+
5 rows in set (0.01 sec)
- 统计数据库资产信息（数据资产），获取具有碎片信息的表
mysql> select table_schema,table_name,data_free from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
-- 碎片信息过多会导致索引信息失效，以及统计信息不真实的情况
- 统计数据库资产信息（数据资产），处理具有碎片信息的表
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and data_free >0 ;
- 统计数据库资产信息（数据资产），获取数据库中非innodb表信息
mysql>select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb';
- 统计数据库资产信息（数据资产），修改数据库中非innodb表信息替换成innodb
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;") from information_schema.tables where table_schema not in ('mysql','sys','performance_schema','information_') and engine!='innodb' into outfile '/tmp/alter.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
-- 修改配置文件参数信息，实现将数据库操作的数据信息导入到系统文件中，配置完毕重启数据库服务
mysql> source /tmp/alter.sql
-- 可以对不是innodb存储引擎的表做操作，实现数据表批量化引擎修改，调用数据库脚本信息
- 批量删除数据库中的表（除了核心库和teacher表之外）
mysql> select concat("drop table ",TABLE_SCHEMA,".`",TABLE_NAME,"`;") from tables where TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema','information_schema') and TABLE_NAME not in ('teacher');
+---------------------------------------------------------+
| concat("drop table ",TABLE_SCHEMA,".`",TABLE_NAME,"`;") |
+---------------------------------------------------------+
| drop table db01.`class`; |
| drop table db01.`student`; |
| drop table db01.`t_foreign`; |
| drop table db03.`class`; |
| drop table db03.`student`; |
| drop table db03.`t_foreign`; |
| drop table db01.`test`; |
| drop table world.`city`; |
| drop table world.`country`; |
| drop table world.`countrylanguage`; |
| drop table school.`student`; |
| drop table school.`course`; |
| drop table school.`sc`; |
| drop table school.`tv`; |
| drop table school.`v`; |
+---------------------------------------------------------+
15 rows in set (0.00 sec)
mysql> select concat("drop table ",TABLE_SCHEMA,".`",TABLE_NAME,"`;") from tables where TABLE_SCHEMA not in ('sys','information_schema','mysql','performance_schema','information_schema') and TABLE_NAME not in ('teacher') into outfile '/tmp/drop_table.sql';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 8
Current database: information_schema
Query OK, 15 rows affected (0.09 sec)
mysql> source /tmp/drop_table.sql;