information_schema

　　是虚拟库，以及一些固定好的视图。

视图：一条查询语句的别名

元数据

----> “基表”（无法直接查询和修改的）
----> DDL 进行元数据修改
----> show ,desc(show),information_schema(全局类的统计和查询)

use information_schema
desc tables; #tables表中存储着整个数据库所有表的元数据信息

TABLE_SCHEMA     表所在的库
TABLE_NAME       表名
ENGINE           表的存储引擎
TABLE_ROWS       表的行数
AVG_ROW_LENGTH   平均行长度
INDEX_LENGTH     索引的长度

示例

--- 查询整个数据库中所有的库对应的表名
SELECT table_schema,table_name 
FROM information_schema.tables;

--- 查询world和school库下的所有表名
SELECT table_schema,table_name 
FROM information_schema.tables
WHERE table_schema='world'
UNION ALL 
SELECT table_schema,table_name 
FROM information_schema.tables
WHERE table_schema='school';

--- 查询整个数据库中所有的库对应的表名，每个库显示成一行
SELECT table_schema,GROUP_CONCAT(table_name) 
FROM information_schema.tables
GROUP BY  table_schema;

--- 统计一下每个库下的表的个数
SELECT table_schema,COUNT(table_name) 
FROM information_schema.tables
GROUP BY  table_schema;

--- 统计一下每个库的真实数据量 
每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT table_schema,count(table_name),SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.TABLES 
GROUP BY table_schema;

--- 统计一下所有库加起来的真实数据量 
SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.TABLES 

面试问题

### 公司的数据量多大？
最大的表分别是3000W、600W、400W表，大约是40GB左右。
备份时间通过mysqldump大约需要20分钟。

concat()

按照指定的格式显示

 SELECT CONCAT(USER,"@","'",HOST,"'") FROM mysql.user;

备份命令

mysqldump -uroot -p123  world city >/tmp/world_city.sql

批量备份所有表：模仿以上命令，对整个数据库下的1000张表进行单独备份,排除sys，performance，information_schema,存放到/tmp下

# 添加安全目录
vim /etc/my.cnf 
secure-file-priv=/tmp

/etc/init.d/mysqld restart

# 生成备份脚本
SELECT CONCAT("mysqldump -uroot -p123  ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")  
FROM information_schema.tables 
WHERE table_schema NOT IN('sys','performance','information_schema')
INTO OUTFILE '/tmp/bak.sh';

# 查看备份脚本
[root@localhost ~]# cat /tmp/bak.sh | tail -5
mysqldump -uroot -p123  school student >/tmp/school_student.sql
mysqldump -uroot -p123  school teacher >/tmp/school_teacher.sql
mysqldump -uroot -p123  world city >/tmp/world_city.sql
mysqldump -uroot -p123  world country >/tmp/world_country.sql
mysqldump -uroot -p123  world countrylanguage >/tmp/world_countrylanguage.sql

# 进行备份
[root@localhost ~]# sh /tmp/bak.sh 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.

模仿以下语句，批量实现world下所有表的操作如下sql语句：alter table world.city discard tablespace;

select concat("alter table ",table_schema,".",table_name,"discard tablespace;") 
from information_schema.tables 
where table_schema='world'
into outfile '/tmp/discard.sql';