一、命令查表
1、查看 - 所有数据库的磁盘占用
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;
2、查看指定数据库内 - 所有数据表的磁盘占用
select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'your_db_name'
group by TABLE_NAME
order by data_length desc;
如果报 sql_mode 错误:this is incompatible with sql_mode=only_full_group_by(select 选择的字段必须包含在 group by 字段中),可以查询并修改 sql_mode,从选项中去掉 ONLY_FULL_GROUP_BY 模式。也可以不用 group_by,使用下面语句。
select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),' MB') as data_size,
concat(truncate(index_length/1024/1024,2),' MB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'your_db_name'
order by data_length desc;
二、查看磁盘文件
1、查看磁盘文件位置
(1)命令查看
show global variables like "%datadir%";
(2)通过配置文件查看
vim /etc/my.cof
2、查看文件大小
cd /var/lib/mysql
ll -h
每个数据库会在 datadir 下创建一个文件夹,文件夹下存放在库下面对应的数据表文件。