mysql查询数据库大小常用语句

1，查询一个实例的所有库的数据的大小总和（其实真实情况没人这么看，都会去看库文件大小）
mysql> select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.tables;

+----------+

| data |

+----------+

| 135.92GB |

+----------+

1 row in set, 2 warnings (6.38 sec)
2，查询指定数据库的大小，例如：数据库kiradb
mysql> select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.tables where table_schema='kiradb';

+----------+

| data |

+----------+

| 124.74GB |

+----------+

1 row in set (4.07 sec)
3，查看指定数据库的表的大小，比如说：数据库kiradb，表job
mysql> select concat(round(sum(DATA_LENGTH/1024/1024/1024),2),'GB') as data from information_schema.tables where table_schema='kiradb' and table_name='job';

+--------+

| data |

+--------+

| 7.45GB |
4，查询一个实例的所有库的数据和索引的大小之和
mysql> select concat(round(sum((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'MB') as data from information_schema.TABLES;
5，查询TOP20大表
select table_schema,table_name,(data_length + index_length) / 1024 / 1024 /1024 as total_GB from information_schema.tables order by total_GB desc limit 20;
6，查询整个库的表数【估算】
select count(table_name),table_schema from information_schema.tables group by table_schema;
7，检查user及权限
root@localhost:t2 10:42:56>select user,host from mysql.user;
root@localhost:t2 10:42:56>select concat(  'show grants for ''', user, '''@''', host, ''';' ) as query from mysql.user;
| SHOW GRANTS FOR 'dbauser'@'%';      |
| SHOW GRANTS FOR 'root'@'%';         |
root@localhost:t2 10:42:56>show grants for 'dbauser'@'%';
8， 查看连接上服务器上数据库的用户
1）从数据库中看
root@localhost:(none) 11:00:19>show processlist;
2）从系统中查看
[root@pmos ~]# mysqladmin -uroot -proot -hlocalhost processlist

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31447263/viewspace-2144211/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31447263/viewspace-2144211/