11-information_schema详解
information_schema
介绍
系统维护过程中记录的信息,管理员可以通过查询元数据，了解数据库运行状态
mysql> use information_schema; #切换到information_schema
mysql> show tables like 'tables'; #查询一个叫tables的表
+---------------------------------------+
| Tables_in_information_schema (TABLES) |
+---------------------------------------+
| TABLES |
+---------------------------------------+
mysql> desc tables; #tables本身一个表
+-----------------+-------------------------------------------+------+
| Field | Type | Null |
+-----------------+-------------------------------------------+------+
| TABLE_CATALOG | varchar(64) | NO |
| TABLE_SCHEMA | varchar(64) | NO |
| TABLE_NAME | varchar(64) | NO |
| TABLE_TYPE | enum('BASE TABLE','VIEW','SYSTEM VIEW') | NO |
| ENGINE | varchar(64) | YES |
| VERSION | int | YES |
| ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES |
| TABLE_ROWS | bigint unsigned | YES |
| AVG_ROW_LENGTH | bigint unsigned | YES |
| DATA_LENGTH | bigint unsigned | YES |
| MAX_DATA_LENGTH | bigint unsigned | YES |
| INDEX_LENGTH | bigint unsigned | YES |
| DATA_FREE | bigint unsigned | YES |
| AUTO_INCREMENT | bigint unsigned | YES |
| CREATE_TIME | timestamp | NO |
| UPDATE_TIME | datetime | YES |
| CHECK_TIME | datetime | YES |
| TABLE_COLLATION | varchar(64) | YES |
| CHECKSUM | bigint | YES |
| CREATE_OPTIONS | varchar(256) | YES |
| TABLE_COMMENT | text | YES |
+-----------------+-------------------------------------------+------+
该TABLES表具有以下列：
https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html
tables表的重要列说明（国家户籍、电话局）
TABLE_SCHEMA 表所在库（库名）
TABLE_NAME 表的名称
ENGINE 表的存储引擎
TABLE_ROWS 表的行数(粗略统计)
AVG_ROW_LENGTH 平均行长度(粗略统计)
DATA_FREE 碎片数(已分配但未使用的字节数。)
TABLE_COMMENT 表注释
INDEX_LENGTH 索引长度(粗略统计)
对于MyISAM,INDEX_LENGTH 是索引文件的长度，以字节为单位。
对于InnoDB，INDEX_LENGTH 为非聚集索引分配的近似空间量，以字节为单位。具体来说，它是非聚集索引大小的总和（以页为单位）乘以 InnoDB页大小。
例1：查sc表属于哪个库？ 去information_schema里的tables表里面查。
use information_schema
例1：查sc表属于哪个库？ 去information_schema里的tables表里面查。
类似：查张三老家地址？ 是不是要去公安局户籍科（户口本、身份登记）
mysql> select TABLE_SCHEMA from tables where TABLE_NAME='sc';
+--------------+
| TABLE_SCHEMA |
+--------------+
| school |
+--------------+
1 row in set (0.00 sec)
mysql> select TABLE_SCHEMA from tables where TABLE_NAME='stu';
+--------------+
| TABLE_SCHEMA |
+--------------+
| oldboy |
+--------------+
1 row in set (0.00 sec)
例2：查stu表有多少行记录
mysql> select TABLE_SCHEMA,engine,table_rows from tables where TABLE_NAME='stu';
+--------------+--------+------------+
| TABLE_SCHEMA | ENGINE | TABLE_ROWS |
+--------------+--------+------------+
| oldboy | InnoDB | 4 |
+--------------+--------+------------+
1 row in set (0.00 sec)
mysql> select * from oldboy.stu;
+----+---------+-----+--------+--------+-------+
| id | sname | age | gender | telnum | state |
+----+---------+-----+--------+--------+-------+
| 1 | oldboy | 28 | M | 111 | 1 |
| 2 | oldgril | 25 | F | 126 | 1 |
| 3 | Jack | 18 | M | 189 | 1 |
| 4 | Tim | 35 | F | 183 | 0 |
+----+---------+-----+--------+--------+-------+
例3. 统计所有库对应的表个数和名字列表
mysql> use school
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course |
| sc |
| student |
| teacher |
+------------------+
库名 表个数 表名
school 4 course sc student teacher
语句：
select table_schema,count(*) ,group_concat(table_name)
from information_schema.tables
group by table_schema;
*************************** 5. row ***************************
TABLE_SCHEMA: school
count(*): 4
group_concat(table_name): course,sc,student,teacher
统计school库对应的表个数和名字列表
mysql> select table_schema,count(*) ,group_concat(table_name)
from information_schema.tables
where table_schema='school'
group by table_schema\G
*************************** 1. row ***************************
TABLE_SCHEMA: school
count(*): 4
group_concat(table_name): course,sc,student,teacher
例4；统计每个库的数据量大小
# 查询每个库库，表的行数乘平均行长度加索引长度除以1024
mysql> select table_schema,sum(TABLE_ROWS*AVG_ROW_LENGTH+index_length)/1024
-> from information_schema.tables # 指定从这里查以上数据
-> group by table_schema; # 去重表所在的库
+--------------------+--------------------------------------------------+
| TABLE_SCHEMA | sum(TABLE_ROWS*AVG_ROW_LENGTH+index_length)/1024 |
+--------------------+--------------------------------------------------+
| mysql | 2350.8057 |
| information_schema | 0.0000 |
| performance_schema | 0.0000 |
| sys | 15.9961 |
| oldboy | 48.0000 |
| world | 779.7744 |
| school | 63.9795 |
+--------------------+--------------------------------------------------+
[root@db01 ~]# du -sh /data/*
190M /data/3306
175M /data/3307
175M /data/3308
工作场景回答mysql数据量：只存放文本数据内容。
几十台服务器 mysql数据量10-20G mysqldump 每日全量
几百台服务器 mysql数据量20-50G xtrabackup 每周全量 每日增量
例5： 查询一下业务数据库中,非InnoDB引擎的表有哪些
拼接语句(理解，会用)
测试数据：
USE oldboy;
CREATE TABLE oldgirl(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=myisam;
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=myisam;
CREATE TABLE student(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
telnum CHAR(15) NOT NULL DEFAULT '0' COMMENT '手机号'
)ENGINE=myisam;
SELECT table_schema,table_name,engine #查询数据库名称，表名，引擎三列
FROM information_schema.tables # 指定数据来源
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema') # 排除这些数据库
AND ENGINE <>'innodb'; # 使用<>条件排除存储引擎为innodb的表，只保留存储引擎不是innodb的表
======================================
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| oldboy | oldgirl | MyISAM |
| oldboy | student | MyISAM |
| oldboy | test | MyISAM |
+--------------+------------+--------+
例6:统计所有系统库中myisam引擎的表.
SELECT table_schema,table_name,engine # 查询库名，表名，存储引擎的三个列
FROM information_schema.tables # 指定查询的数据来源
WHERE table_schema IN ('mysql','sys','information_schema','performance_schema') # 使用IN条件指定数据库列表中的库进行查询
AND ENGINE = 'myisam'; # 使用=条件筛选出存储引擎为MyISAM的表
#Empty set (0.00 sec)
#MySQL8.0彻底放弃myisam。
SELECT table_schema,table_name,engine # 查询库名，表名，存储引擎的三个列
FROM information_schema.tables # 指定查询的数据来源
WHERE table_schema like '%' # 使用like % 来匹配所有数据库
AND ENGINE = 'myisam'; # 使用=条件筛选出存储引擎为MyISAM的表
例7:将myisam表批量替换为InnoDB（5.5以前）
SELECT concat("alter table ",table_schema,".",table_name," engine=innodb;") # concat拼接alter table语句
FROM information_schema.tables # 指定查询的数据来源
WHERE table_schema NOT IN ('mysql','sys','information_schema','performance_schema') # 不查询这些数据库
AND ENGINE ='myisam' # 使用 = 条件筛选出存储引擎为 MyISAM 的表。
into outfile '/tmp/oldboy_alter.sql'; # 将查询结果输出到指定文件
##默认执行报错；
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement--secure-file-priv
查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE, LOAD_FILE()传到哪个指定目录。
secure_file_priv 为NULL时，表示限制mysqld不允许导入或导出。
secure_file_priv 为/tmp时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。
secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。
[root@db01 ~]# vim /etc/my.cnf
#by oldboy weixin:oldboy0102
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
port=3306
secure_file_priv=/tmp ##增加此行
[root@db01 ~]# systemctl restart mysqld
[root@db01 ~]# mysql -uroot -p123
mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /tmp/ |
+--------------------+
结果:
mysql> system cat /tmp/oldboy_alter.sql;
alter table oldboy.oldgirl engine=innodb;
alter table oldboy.student engine=innodb;
alter table oldboy.test engine=innodb;
mysql> source /tmp/oldboy_alter.sql
SELECT table_schema,table_name,engine FROM information_schema.tables
WHERE table_schema='oldboy' AND ENGINE='myisam';
例8:分表备份（备份学完归来看）
SELECT concat # 使用concat拼接字符串
# 使用mysqldump来拼接字符串，使用库名，表名 来构建完整的命令 写入到/opt/库名_表名.sql
("mysqldump -uroot -poldboy123 ",table_schema," ",table_name," >/opt/",table_schema,"_",table_name,".sql")
# 指定查询的数据来源
FROM information_schema.tables
# where过滤这个表中不包含这些库的， into outfile 将查询结构输出到指定文件
WHERE
table_schema NOT IN ('mysql','sys','information_schema','performance_schema') into outfile '/tmp/bak.sh' ;
结果：
mysql> system cat /tmp/bak.sh;
mysqldump -uroot -poldboy123 oldboy stu >/opt/oldboy_stu.sql
mysqldump -uroot -poldboy123 world city >/opt/world_city.sql
mysqldump -uroot -poldboy123 world country >/opt/world_country.sql
mysqldump -uroot -poldboy123 world countrylanguage >/opt/world_countrylanguage.sql
mysqldump -uroot -poldboy123 world a >/opt/world_a.sql
mysqldump -uroot -poldboy123 world b >/opt/world_b.sql
mysqldump -uroot -poldboy123 school course >/opt/school_course.sql
mysqldump -uroot -poldboy123 school sc >/opt/school_sc.sql
mysqldump -uroot -poldboy123 school student >/opt/school_student.sql
mysqldump -uroot -poldboy123 school teacher >/opt/school_teacher.sql
mysqldump -uroot -poldboy123 oldboy oldgirl >/opt/oldboy_oldgirl.sql
mysqldump -uroot -poldboy123 oldboy test >/opt/oldboy_test.sql
mysqldump -uroot -poldboy123 oldboy student >/opt/oldboy_student.sql
浙公网安备 33010602011771号