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 statusG #查看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、information_schema.tables视图的结构说明
information_schema.tables视图常用列属性
DESC information_schema.TABLES TABLE_SCHEMA ---->所有数据库的库名 TABLE_NAME ---->所有表的表名 ENGINE ---->引擎 TABLE_ROWS ---->表的行数 AVG_ROW_LENGTH ---->表中行的平均行（字节） INDEX_LENGTH ---->索引的占用空间大小（字节）
2、information_schema.tables视图的案例说明
SELECT TABLE_SCHEMA,GROUP_CONCAT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;
2）统计所有库下的表个数
SELECT TABLE_SCHEMA,COUNT(TABLE_NAME)
FROM information_schema.tables
GROUP BY TABLE_SCHEMA;
3)查询所有innodb引擎的表及所在的库
SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE ENGINE='innodb';
数据库下表所占用空间的计算
表中的平均行（字节）*表的行数+索引的占用空间大小 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
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;
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
注意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]> 重启数据库
执行结果
[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)
[root@vm01 ~]# ll /tmp/bak.sh -rw-rw-rw- 1 mysql mysql 2837 Nov 12 06:00 /tmp/bak.sh
执行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
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';