2.1 元数据介绍
基表---->数据字典信息（表列结构，存储在frm文件中）、系统状态、对象状态；
相当于Linux中的Inode
2.2 show 语句(MySQL独家)
show databases;
show tables;
show create database xxx;
show create table xxx;
show grants for xxx;
show charset ;
show collation;
show variables like '%trx%';
show engines;
show processlist;
show index from t1;
show status ;
show engine innodb status\G
show binlog events in ''
show binary logs
show master status
show slave status\G
show relaylog events in ''
SHOW TABLE STATUS ;
help show;
2.3 information_schema虚拟库（试图）
2.3.1 试图
将一些常用的查询需求，分装成试图，后期调用直接查询试图就可以，更加简便、安全
-- 创建试图
CREATE VIEW test AS SELECT
country.name AS co_name,country.SurfaceArea,city.name AS ci_name,city.Population
FROM city JOIN country
ON city.CountryCode=country.code
WHERE city.Population<100;
-- 调用试图
SELECT * FROM test;
2.3.2 TABLES 的作用和结构
1>TABLES 的作用
存储整个数据库中，所有表中的元数据的查询方式.
2>TABLES 的结构
wenjuan[information_schema]>desc TABLES;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.00 sec)
wenjuan[information_schema]>
常用查询列：
TABLE_SCHEMA 表所在的库
TABLE_NAME 表名
ENGINE 表的引擎
TABLE_ROWS 表的行数
AVG_ROW_LENGTH 平均行长度
INDEX_LENGTH 索引长度
3> 举例
（1）查询 world 数据库下的所有表名
wenjuan[(none)]>show tables from world;
（2）查询整个数据库下的所有表名
wenjuan[(none)]>select table_name from information_schema.tables;
（3）查询所有InnoDB引擎的表
wenjuan[(none)]>SELECT table_schema,table_name,ENGINE FROM information_schema.TABLES WHERE ENGINE='innodb';
（4）统计每张表的实际占用空间大小情况(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)
SELECT table_name,AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH FROM information_schema.TABLES
（5）统计每个库的空间使用情况大小情况
SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 FROM information_schema.TABLES GROUP BY table_schema
中小型：300G---500G
中大型：2T------9T
（6）对MySQL的数据库进行分库分表备份（重要）
-- 单表备份
mysqldump -uroot -p123456 world city >/backup/world_city.sql
-- 进行分库分表，并将批量备份语句保存到文件中，之后`sh /tmp/sqlback.sql`执行文件就可以
SELECT CONCAT('mysqldump -uroot -p123456 ',table_schema,' ',table_name,' > /backup_sql/',table_schema,'_',table_name,'.sql')
FROM information_schema.TABLES INTO OUTFILE '/tmp/sqlback.sql'
注意：The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
出现以上的错误是没有设置安全路径，5.7以后才出现的约束
解决：将secure-file-priv=这个参数添加到/etc/my.cnf的服务端里面，可以指定*(所有目录)，也可以指定具体的某个目录，然后重启mysql：/etc/init.d/mysqld restart
（7）模仿模板语句,批量生成对world数据库下的表操作的语句
-- 单表修改
-- atler table world.city engine=innodb;
-- 批量修改
SELECT CONCAT("alter table ",table_schema,".",table_name," engine=innodb;")
FROM information_schema.tables WHERE table_schema='world'
INTO OUTFILE '/tmp/world_innodb.sql';
[root@db01 ~]# cat /tmp/world_innodb.sql
alter table world.city engine=innodb;
alter table world.country engine=innodb;
alter table world.countrylanguage engine=innodb;
[root@db01 ~]#
网友评论