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';