05-Informatica_schema获取元数据

一、获取元数据

1、元数据在什么位置

我们之前存储数据 .ibd文件存储真实数据行,frm存储的是基本列结构,那其他元数据呢,比如权限,占用空间大小的其他属性存储在哪呢,我们把他们存储的位置称为基表，基表是不能被用户直接访问到的，为了安全，可以通过间接手段查询，比如我们常用的show语句，它封装了查询元数据的方法，但是show语句只能是固定模式的查询，只是一小部分

例如：

show tables
# 是说看某个数据库下的所有表
# 那我现在的需求是查看所有数据库下的所有表,show命令就支持不了了

其他:

"""
SOHW databases：列出所有数据库
SHOW TABLES：列出默认数据库中的表
SHOW TABLES FROM <database_name>：列出指定数据库中的表
SHOW INDEX FROM <table_name>：显示表中有关索引和索引列的信息
SHOW CHARACTER SET：显示可用的字符集及其默认整理
SHOW STATUS：列出当前数据库状态
SHOW VARIABLES：列出数据库中的参数定义值
"""

2、Informatica_schema

"""
看起来 information_schema 像是一个数据库,但是它并不是一个数据库,
数据库在磁盘上的一个表现是有一个目录,但是它没有,information_schema是一个虚拟库
是mysql启动后生成的一个虚拟库
"""

# 虚拟库里面存着很多类似于表的东西,称为视图,视图对象和表对象存的东西差不多

解释一下视图例如：

我们总会做一些复杂查询，可以创建一个视图代替某条语句的执行

CREATE VIEW test AS 复杂查询语句

这样就创建了一个test的视图代替复杂语句的执行

SELECT * FROM test

informatica_schema 里都是系统预设好的一些视图,包括很多元数据的查询方法

"""
SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。

TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的结果取之此表。

COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns fromschemaname.tablename的结果取之此表。

STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。

USER_PRIVILEGES（用户权限）表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。

SCHEMA_PRIVILEGES（方案权限）表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。是非标准表。

TABLE_PRIVILEGES（表权限）表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。

COLUMN_PRIVILEGES（列权限）表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。

CHARACTER_SETS（字符集）表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。

TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。

VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。

TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表
"""

视图

TABLES视图的元数据信息

字段	含义
table_catalog	数据表登记目录
table_schema	数据表所属的数据库名
table_name	表名称
table_type	表类型[system view\|base table]
engine	使用的数据库引擎[MyISAM\|CSV\|InnoDB]
version	版本，默认值10
row_format	行格式[Compact\|Dynamic\|Fixed]
table_rows	表里所存多少行数据
avg_row_length	平均行长度
data_length	数据长度
max_data_length	最大数据长度
index_length	索引长度
data_free	空间碎片
auto_increment	做自增主键的自动增量当前值
create_time	表的创建时间
update_time	表的更新时间
check_time	表的检查时间
table_collation	表的字符校验编码集
checksum	校验和
create_options	创建选项
table_comment	表的注释、备注

例1:

统计django_orm_01数据库下所有表的的行数和数据量(行长度+索引长度)

select table_schema,table_name,table_rows,(AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS size
from TABLES where table_schema='django_orm_01';

例2:

统计整库的真实数据量

select SUM(AVG_ROW_LENGTH+INDEX_LENGTH)/1024/1024 AS sum_mb FROM TABLES

例3:

1、整个数据库中所有表的名字及所在的库名

SELECT table_name ,TABLE_SCHEMA FROM information_schema.TABLES;

2、整个数据库下，所有有库下表的个数

SELECT table_schema,COUNT(table_name) FROM information_schema.TABLES GROUP BY table_schema;

3、查一下数据库引擎是innodb表，表名+库名

SELECT table_schema,table_name,ENGINE FROM information_schema.TABLES WHERE ENGINE ='innodb';

4、统计world数据库下city表的占用空间大小

SELECT (data_length+index_length)/1024/1024 AS data_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA='world' AND table_name='city';

5、统计整个数据库的数据量

SELECT SUM(AVG_ROW_LENGTH+index_length)/1024/1024 AS data_mb FROM information_schema.TABLES

6、统计world数据库的数据量

SELECT SUM(AVG_ROW_LENGTH+index_length)/1024/1024 AS data_mb FROM information_schema.TABLES WHERE table_schema='world';

7、备份数据库下的每一张表,有以下语句格式:备份world库下的city到/BACKUP/world_city.sql

--- 请模仿以下格式命令，单独备份每一个数据库中的表，1000张表，实现。
--- mysqldump -uroot -p123 world city > /BACKUP/world_city.sql

固定不变的有 mysqldump -uroot -p123 world_ .sql

变化的是 world city world_city.sql中的city

SELECT table_schema,table_name FROM information_schema.tables;

CONCAT('mysqldump -uroot -p123 world ')

SELECT CONCAT('mysqldump -uroot -p1 ',' ',table_schema,' ',table_name,'> /backup/',table_schema,'_',table_name,'.sql')
FROM information_schema.tables WHERE table_schema='world';

-- 写到脚本中
SELECT CONCAT('mysqldump -uroot -p123 ',' ',table_schema,' ',table_name,'> /backup/',table_schema,'_',table_name,'.sql')
FROM information_schema.tables WHERE table_schema='world' INTO OUTFILE '/tmp/backup.sh';

----注：如果需要将sql的执行结果导出到操作系统中的文件时，需要MySQL设置安全目录5.6版本

vim /etc/my.cnf
添加以下内容，重启数据库即可
secure-file-priv=/tmp

8、以以下语句为例：

create table world.city_bak like world.city;
给world数据库下的每个表创建一个bak表
(1)

select concat('create table ',table_schema,'.',table_name,'_bak like ',table_schema,'.',table_name,';')
from information_schema.tables
where table_schema='world'
into outfile '/tmp/create.sql';

(2)centos系统运行

source /tmp/create.sql

9、以下语句为例：

alter table world.city_bak discard tablespace;
清除所有world数据库下所有bak表中的ibd文件

SELECT CONCAT('alter table ',table_schema,'.',table_name,' discard tablespace;')
FROM information_schema.tables
WHERE table_schema='world' AND table_name LIKE '%bak'
INTO OUTFILE '/tmp/alter.sql';

source /tmp/alter.sql

10、

（1）关闭数据库
（2）将/application/mysql/data/world数据库下的不带_bak的bak文件名复制一份：
例如:
cp city.ibd city_bak.ibd

cp city.ibd city_bak.ibd
cp country.ibd country_bak.ibd
cp countrylanguage.ibd countrylanguage_bak.ibd
cp score.ibd score_bak.ibd
cp stu.ibd stu_bak.ibd

（3）修改权限： chown -R mysql.mysql /application/mysql/data/world
（4）启动数据库
（5）以以下语句为例
alter table world.city_bak import tablespace;
将所有bak的表进行import操作

SELECT CONCAT('alter table ',table_schema,'.',table_name,' import tablespace;')
FROM information_schema.tables
WHERE table_schema='world' AND table_name LIKE '%bak'
INTO OUTFILE '/tmp/alter1.sql';