mysql 数据库磁盘快满了

最新推荐文章于 2024-05-02 17:03:47 发布

暴风雨1024

最新推荐文章于 2024-05-02 17:03:47 发布

阅读量187

点赞数

分类专栏： mysql优化

原文链接：https://blog.csdn.net/shangdi1988/article/details/86525727

版权

mysql优化专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文介绍了如何在MySQL中查询数据库表的使用情况，包括表名、行数、数据大小和索引大小，并提供了日志文件路径，如general_log_file、error_log_file和slow_query_log_file。此外，还展示了查询每个数据库的总体数据和索引占用空间的方法。

摘要由CSDN通过智能技术生成

查看数据库表使用情况

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 
    table_rows AS 'Number of Rows', 
    CONCAT(ROUND(data_length/(1024*1024*1024),6),' G') AS 'Data Size', 
    CONCAT(ROUND(index_length/(1024*1024*1024),6),' G') AS 'Index Size' , 
    CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),6),' G') AS'Total'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'platform-product'  ORDER BY TABLE_ROWS desc ;

在这里插入图片描述

登录mysql终端

日志文件路径
mysql> show variables like 'general_log_file';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+------------------------------------+
1 row in set (0.00 sec)
错误日志文件路径
mysql> show variables like 'log_error';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| log_error     | /usr/local/mysql/data/localhost.err |
+---------------+------------------------------------+
1 row in set (0.00 sec)
慢查询日志文件路径
mysql> show variables like 'slow_query_log_file';
+---------------------+-----------------------------------------+
| Variable_name       | Value                                   |
+---------------------+-----------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+-----------------------------------------+
1 row in set (0.01 sec)
————————————————

原文链接：https://blog.csdn.net/shangdi1988/article/details/86525727

2 查看每个库的使用情况



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_length desc;