十一、Mysql常见的show命令及information_schema.tables视图说明

十一、Mysql常见的show命令及information_schema.tables视图说明

一、常见的show命令

show  databases;                          #查看所有数据库
show tables;                              #查看当前库的所有表
SHOW TABLES FROM                          #查看某个指定库下的表
show create database world                #查看建库语句
show create table world.city              #查看建表语句
show  grants for  root@'localhost'        #查看用户的权限信息
show  charset；                           #查看字符集
show collation                            #查看校对规则
show processlist;                         #查看数据库连接情况
show index from                           #表的索引情况
show status                               #数据库状态查看
SHOW STATUS LIKE '%lock%';                #模糊查询数据库某些状态
SHOW VARIABLES                            #查看所有配置信息
SHOW variables LIKE '%lock%';             #查看部分配置信息
show engines                              #查看支持的所有的存储引擎
show engine innodb status\G               #查看InnoDB引擎相关的状态信息
show binary logs                          #列举所有的二进制日志
show master status                        #查看数据库的日志位置信息
show binlog evnets in                     #查看二进制日志事件
show slave status \G                      #查看从库状态
SHOW RELAYLOG EVENTS                      #查看从库relaylog事件信息
desc  (show colums from city)             #查看表的列定义信息
http://dev.mysql.com/doc/refman/5.7/en/show.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

二、information_schema.tables视图说明

1、information_schema.tables视图的结构说明

information_schema.tables视图常用列属性

DESC information_schema.TABLES
TABLE_SCHEMA    ---->所有数据库的库名
TABLE_NAME      ---->所有表的表名
ENGINE          ---->引擎
TABLE_ROWS      ---->表的行数
AVG_ROW_LENGTH  ---->表中行的平均行（字节）
INDEX_LENGTH    ---->索引的占用空间大小（字节）
1
2
3
4
5
6
7

2、information_schema.tables视图的案例说明

1）查询整个数据库中所有库和所对应的表信息

SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;
1
2
3

2）统计所有库下的表个数

SELECT TABLE_SCHEMA,COUNT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;
1
2
3

3)查询所有innodb引擎的表及所在的库

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE ENGINE='innodb';
1
2
3

4)统计mysql数据库下每张表的磁盘空间占用

数据库下表所占用空间的计算

表中的平均行（字节）*表的行数+索引的占用空间大小
AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
单位为字节
SELECT TABLE_SCHEMA,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,"KB") AS size_KB
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mysql';

#CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,"KB")
#拼接单位KB
1
2
3
4
5
6
7
8
9

5)统计每个数据库所占用的磁盘空间

SELECT
TABLE_SCHEMA,
CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024," KB") AS Total_KB
FROM information_schema.tables
GROUP BY table_schema;
1
2
3
4
5

3、“concat"命令拼接用法及"INTO OUTFILE” 的导出

6）生成整个数据库下的所有表的单独备份语句(不备份’information_schema’,‘performance_schema’,‘sys’)

表的备份语句

mysqldump -uroot -p123 mysql user > /tmp/mysql_user.sql
SELECT CONCAT('mysqldump -uroot -p123456 ',table_schema,' ',table_name,' > /tmp/',table_schema,'_',table_name,'.sql')
FROM information_schema.tables 
WHERE table_schema NOT IN('information_schema','performance_schema','sys')
INTO OUTFILE '/tmp/bak.sh' ;
#INTO OUTFILE '/tmp/bak.sh';
#把拼接的整个数据库下的所有表的单独备份语句导出到/tmp/bak.sh
1
2
3
4
5
6
7

注意INTO OUTFILE导出命令需要在my.cnf中添加secure-file-priv=xxx目录路径，不添加该项执行会报---secure-file-priv错误
INTO OUTFILE后只能接xxx目录路径/xxx文件名
例如：只能导出到/tmp目录
[root@vm01 ~]# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/app/mysql
datadir=/data/mysql
server_id=201
port=3306
socket=/tmp/mysql.sock
log_bin=mysql-bin
log_error=/tmp/mysqld_err.log
secure-file-priv=/tmp
[mysql]
socket=/tmp/mysql.sock
prompt= [\\d]>

重启数据库
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

执行结果

[root@vm01 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[(none)]>SELECT CONCAT('mysqldump -uroot -p123456 ',table_schema,' ',table_name,' > /tmp/',table_schema,'_',table_name,'.sql')
    -> FROM information_schema.tables 
    -> WHERE table_schema NOT IN('information_schema','performance_schema','sys')
    -> INTO OUTFILE '/tmp/bak.sh' ;
Query OK, 39 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

[root@vm01 ~]# ll /tmp/bak.sh
-rw-rw-rw- 1 mysql mysql 2837 Nov 12 06:00 /tmp/bak.sh
1
2

执行bak.sh看看数据库是否备份成功

[root@vm01 ~]# cd /tmp
[root@vm01 tmp]# bash bak.sh 
[root@vm01 tmp]# ls *.sql
mysql_columns_priv.sql   mysql_help_keyword.sql        mysql_proc.sql     mysql_tables_priv.sql                school_sc.sql
mysql_db.sql             mysql_help_relation.sql       mysql_proxies_priv.sql          mysql_time_zone_leap_second.sql      school_stu.sql
mysql_engine_cost.sql    mysql_help_topic.sql          mysql_server_cost.sql           mysql_time_zone_name.sql             school_teacher.sql
mysql_event.sql          mysql_innodb_index_stats.sql  mysql_servers.sql               mysql_time_zone.sql                  world_city.sql
mysql_func.sql           mysql_innodb_table_stats.sql  mysql_slave_master_info.sql     mysql_time_zone_transition.sql       world_countrylanguage.sql
mysql_general_log.sql    mysql_ndb_binlog_index.sql    mysql_slave_relay_log_info.sql  mysql_time_zone_transition_type.sql  world_country.sql
mysql_gtid_executed.sql  mysql_plugin.sql              mysql_slave_worker_info.sql     mysql_user.sql                       ywx_king.sql
mysql_help_category.sql  mysql_procs_priv.sql          mysql_slow_log.sql              school_course.sql
1
2
3
4
5
6
7
8
9
10
11

数据库的表已经全部备份成功

7）107张表，都需要执行以下2条语句

ALTER TABLE world.city DISCARD TABLESPACE;
ALTER TABLE world.city IMPORT TABLESPACE;
SELECT CONCAT("alter table ",table_schema,".",table_name," discard tablespace")
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/dis.sql';
1
2
3
4
5
6