DBA MySQL元数据获取
information_schema
information_schema是一个自带的虚拟库，其中主要存储一些重要的的元信息。
它是一个虚拟库，不占用磁盘空间，存储的是数据库启动后的一些参数，如用户表信息、列信息、权限信息、字符信息等
在information_schema中以视图虚拟表的形式存储元数据。
使用以下命令进入information_schema库：
M > USE information_schema;
查看该库下所有的表，其实都是视图虚拟表：
M > SHOW TABLES;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| OPTIMIZER_TRACE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_SYS_DATAFILES |
| INNODB_FT_CONFIG |
| INNODB_SYS_VIRTUAL |
| INNODB_CMP |
| INNODB_FT_BEING_DELETED |
| INNODB_CMP_RESET |
| INNODB_CMP_PER_INDEX |
| INNODB_CMPMEM_RESET |
| INNODB_FT_DELETED |
| INNODB_BUFFER_PAGE_LRU |
| INNODB_LOCK_WAITS |
| INNODB_TEMP_TABLE_INFO |
| INNODB_SYS_INDEXES |
| INNODB_SYS_TABLES |
| INNODB_SYS_FIELDS |
| INNODB_CMP_PER_INDEX_RESET |
| INNODB_BUFFER_PAGE |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
| INNODB_SYS_TABLESPACES |
| INNODB_METRICS |
| INNODB_SYS_FOREIGN_COLS |
| INNODB_CMPMEM |
| INNODB_BUFFER_POOL_STATS |
| INNODB_SYS_COLUMNS |
| INNODB_SYS_FOREIGN |
| INNODB_SYS_TABLESTATS |
+---------------------------------------+
TABLES
这是一个重要的视图表，存储该MySQL实例下所有表的元数据。
对该表进行结构查看，可以看到如下信息：
M > DESC TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
重要的字段介绍如下：
| TABLES虚拟表字段介绍 | 描述 |
|---|---|
| TABLE_SCHEMA | 表所在库名 |
| TABLE_NAME | 表名 |
| ENGINE | 存储引擎 |
| TABLE_ROWS | 数据行 |
| AVG_ROW_LENGTH | 平均行长度 |
| INDEX_LENGTH | 索引行长度 |
对所有库表查询
显示所有的库与表的信息：
SELECT table_schema,table_name FROM information_schema.tables LIMIT 1;
以库进行分组，查看该库下所有的表：
SELECT table_schema, GROUP_CONCAT(table_name) FROM information_schema.tables
GROUP BY table_schema LIMIT 1;
查询所有的表的存储引擎，内置库除外：
SELECT table_schema,table_name ,engine FROM information_schema.tables
WHERE table_schema NOT IN ('sys','mysql','information_schema','performance_schema');
数据量占用空间
数据量占用空间大小的计算公式，默认结果为字节为单位：
# 平均行长度 * 行数 + 索引长度 = 占用空间(字节)
AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH
# 使用KB做为单位
(AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) / 1024
统计某个库下，某个表的数据量占用空间大小：
SELECT table_name, (AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) / 1024
FROM information_schema.tables
WHERE table_schema = "db1" AND table_name = "department";
统计某个库的数据量占用空间大小：
SELECT table_schema, SUM((AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) / 1024)
FROM information_schema.tables
WHERE table_schema = "db1";
统计所有库的数据量占用空间大小，按照升序排序：
SELECT table_schema, SUM((AVG_ROW_LENGTH * TABLE_ROWS + INDEX_LENGTH) / 1024) AS total_KB
FROM information_schema.tables
GROUP BY table_schema
ORDER BY total_KB;
CONCAT()语句拼接
模仿以下语句,进行数据库的分库分表备份：
mysqldump -uroot -p123 库名 表名 >/mysql_data/bak/库名_表名.sql
使用CONCAT()语句拼接完成需求：
SELECT CONCAT("mysqldump -uroot -p123 ",table_schema," ",table_name," >/bak/",table_schema,"_",table_name,".sql")
FROM information_schema.tables
WHERE table_schema = "库名" AND table_name = "表名";
模仿以下语句，进行批量生成命令，对特定库下所有表进行操作：
ALTER TABLE 库名.表名 DISCARD TABLESPACE;
使用CONCAT()语句拼接完成需求：
# 向文件输出，需要打开配置项：secure-file-priv用于进行受信任的安全路径的配置，配置文件中配置secure_file_priv =
# 即可，=后面不填内容，代表所有路径均受信任
SELECT CONCAT("ALTER TABLE ",table_schema,".",table_name," DISCARD TABLESPACE;") INTO OUTFILE "/tmp/edit.sql"
FROM information_schema.tables
WHERE table_schema = "库名" ;
