information_schema

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

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

元数据

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

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

1TABLE_SCHEMA     表所在的库
2TABLE_NAME       表名
3ENGINE           表的存储引擎
4TABLE_ROWS       表的行数
5AVG_ROW_LENGTH   平均行长度
6INDEX_LENGTH     索引的长度

示例

 1--- 查询整个数据库中所有的库对应的表名
 2SELECT table_schema,table_name 
 3FROM information_schema.tables;
 4
 5--- 查询world和school库下的所有表名
 6SELECT table_schema,table_name 
 7FROM information_schema.tables
 8WHERE table_schema='world'
 9UNION ALL 
10SELECT table_schema,table_name 
11FROM information_schema.tables
12WHERE table_schema='school';
13
14--- 查询整个数据库中所有的库对应的表名，每个库显示成一行
15SELECT table_schema,GROUP_CONCAT(table_name) 
16FROM information_schema.tables
17GROUP BY  table_schema;
18
19--- 统计一下每个库下的表的个数
20SELECT table_schema,COUNT(table_name) 
21FROM information_schema.tables
22GROUP BY  table_schema;
23
24--- 统计一下每个库的真实数据量 
25每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
26SELECT table_schema,count(table_name),SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
27FROM information_schema.TABLES 
28GROUP BY table_schema;
29
30--- 统计一下所有库加起来的真实数据量 
31SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
32FROM information_schema.TABLES 
33

面试问题

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

concat()

按照指定的格式显示

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

备份命令

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

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

 1# 添加安全目录
 2vim /etc/my.cnf 
 3secure-file-priv=/tmp
 4
 5/etc/init.d/mysqld restart
 6
 7# 生成备份脚本
 8SELECT CONCAT("mysqldump -uroot -p123  ",table_schema," ",table_name," >/tmp/",table_schema,"_",table_name,".sql")  
 9FROM information_schema.tables 
10WHERE table_schema NOT IN('sys','performance','information_schema')
11INTO OUTFILE '/tmp/bak.sh';
12
13# 查看备份脚本
14[root@localhost ~]# cat /tmp/bak.sh | tail -5
15mysqldump -uroot -p123  school student >/tmp/school_student.sql
16mysqldump -uroot -p123  school teacher >/tmp/school_teacher.sql
17mysqldump -uroot -p123  world city >/tmp/world_city.sql
18mysqldump -uroot -p123  world country >/tmp/world_country.sql
19mysqldump -uroot -p123  world countrylanguage >/tmp/world_countrylanguage.sql
20
21# 进行备份
22[root@localhost ~]# sh /tmp/bak.sh 
23mysqldump: [Warning] Using a password on the command line interface can be insecure.
24mysqldump: [Warning] Using a password on the command line interface can be insecure.
25mysqldump: [Warning] Using a password on the command line interface can be insecure.

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

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