MySQL基础管理07之元数据获取

MySQL基础管理07之元数据获取

第一章 什么是元数据

什么是元数据: 除了表之外的都是元数据,也称之为基表
比如库表属性、状态信息、权限、列名、数据库名、用户名、版本以及从SHOW语句得到的结果中的大部分字符串是元数据

第二章 元数据获取

1.通过show语句获取

  • 常用show语句
help show;
show databases;          	    # 查询所有库名
show tables;             	    # 查询当前库的所有表名  
show tables from world;  	    # 查询world库下的所有表名
show create database world;         # 查询world建库语句
show create table city;             # 当前库下的city表建表语句
show create table world.city;       # world库下的建表语句   
show privileges;                    # 数据库中所有权限
show engines;                       # 数据库中支持的存储引擎
show grants for root@'localhost'    # 查询某个用户权限
show charset;                       # 查询数据库字符集支持
show collation;                     # 查询所有校对规则的支持
show variables like '%trx%'         # 查询数据库参数 
show status like 'com_%'            # 查询数据库的状态
show processlist;                   # 查询所有会话信息
show engine innodb status           # 查询innodb引擎相关的状态
show binary logs                    # 查询二进制日志文件信息
show binlog events in 'xxx'         # 查看二进制日志事件
show master status ;                # 当前正在使用的二进制日志信息
show slave status\G                 # 查看主从状态相关信息
show slave hosts;                   # 查看从库主机信息

2.通过系统表查询元数据

①库表分类

  • mysql库
权限表: 
     user
     db
统计信息表 :
	innodb_index_stats
	innodb_table_stats
  • IS(information_schema库)
对象属性(表):
	tables  
	columns
  • PS(performance_schema库)

性能有关

  • SYS(库)

性能有关

②IS中的tables和columns的应用

  • 数据库资产统计
use information_schema;
desc tables;
-- table_schema   表所在的库
-- table_name     表名
-- engine         存储引擎
-- table_rows     表行数
-- avg_row_length 平均行长度
-- index_length   索引长度
-- data_free      碎片的大小
  • 统计案例
--- 例子1 :每个业务库表的个数和名称
mysql> select  table_schema,count(*),group_concat(table_name)
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;
+--------------+----------+------------------------------+
| table_schema | count(*) | group_concat(table_name)     |
+--------------+----------+------------------------------+
| younggirl    |        1 | student                      |
| oldli        |        2 | student,stu                  |
| school       |        4 | sc,course,teacher,student    |
| world        |        3 | countrylanguage,country,city |
+--------------+----------+------------------------------+
4 rows in set (0.01 sec)

--- 例子2: 统计每个库的数据量 
mysql> select  table_schema,SUM(table_rows*avg_row_length+index_length)/1024 as total_KB
from information_schema.tables
where table_schema not in ('mysql','information_schema','performance_schema','sys')
group by table_schema;
+--------------+----------+
| table_schema | total_KB |
+--------------+----------+
| oldzhang     |  16.0000 |
| oldli        |   0.0000 |
| school       |  63.9795 |
| world        | 779.7744 |
+--------------+----------+
4 rows in set (0.01 sec)

--- 例子3: 查询业务表中,所有不是InnoDB引擎的表 
mysql> select  table_schema,table_name
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| oldli        | test       |
+--------------+------------+
1 row in set (0.00 sec)
  • 命令拼接
# 将数据库中不表的引擎不是innodb的全部查询出来并替换成innodb,这时候需要用到mysql命令拼接了。
# 另外IS是虚拟表,无法直接在这个表里面去修改存储引擎,可以使用一下方法实现:
mysql> select  concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb';
+----------------------------------------------------------------------+
| concat("alter table ",table_schema,".",table_name," engine=innodb;") |
+----------------------------------------------------------------------+
| alter table oldli.test engine=innodb;                               |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)
# 或者直接将上述结果导出,然后直接source(生产勿用!!!)
mysql> select  concat("alter table ",table_schema,".",table_name," engine=innodb;")
from information_schema.tables
where 
table_schema not in ('mysql','information_schema','performance_schema','sys')
and engine!='innodb' into outfile '/tmp/alter.sql';
$ cat /tmp/alter.sql
  alter table oldli.test engine=innodb;

mysql> source /tmp/alter.sql

以上就是MySQL元数据的使用,仅供参考!