In many server maintenance scenarios, the problem of MySQL abnormally increasing disk space usage is something almost everyone has experienced. After the database has been running for a period of time, the disk suddenly becomes full, causing the website to be unable to write, service abnormalities, or even the database to crash directly. The cause of this problem is often not a single factor, but rather the result of the accumulation of binlog, error log, slow log, temporary files, fragmented data, and redundant historical data. Therefore, to completely resolve the problem of excessive MySQL disk space usage, a structured investigation, location, and cleanup are required. At the same time, it is essential to ensure that the cleanup process is safe and controllable to avoid accidental deletion that could damage the database.

　　Before actually dealing with the MySQL space usage problem, it is necessary to first check the current disk space status to determine whether the database is causing the full disk. The following command can be used to check disk usage:

df -h

　　　　If the partition where `/var/lib/mysql` or the MySQL data directory is mounted is found to be near 100% usage, it's highly likely that MySQL is the culprit. Further investigation of the MySQL data directory's usage structure is then necessary.

du -sh /var/lib/mysql/*

　　This command can quickly identify which library or file is consuming excessive space. Large files typically include ibdata1, ib_logfile0/1, binlog files, error logs, and .ibd table files. Once the source of the problem is identified, targeted troubleshooting can begin.

　　In cases of a sudden and abnormal increase in MySQL space usage, binlog (Binary Log) bloat is one of the most common causes. With binlog enabled, MySQL records all variable operations; without a deletion policy, the file will accumulate indefinitely. In scenarios with high data write volumes, such as e-commerce, data collection systems, and high-frequency write operations, the binlog can grow to tens or even hundreds of GB in a short period. To check the binlog file size, you can execute:

ls -lh /var/lib/mysql/binlog.*

　　If the quantity is large, you need to check the current MySQL expiration policy:

show variables like 'expire_logs_days';
show variables like 'binlog_expire_logs_seconds';

　　If both values ​​are 0, it means the binlog will never expire, and an expiration policy needs to be set immediately.

set global expire_logs_days = 7;

　　Or based on second-level configuration:

set global binlog_expire_logs_seconds = 604800;

　　You can also clean it manually:

purge binary logs before date_sub(now(), interval 7 day);

　　However, it's crucial to ensure proper master-slave synchronization; otherwise, replication may be interrupted.

　　Another factor contributing to excessive disk usage is the MySQL log files, including the error log, slow query log, and general log. In high-concurrency systems, if verbose logging is enabled but not regularly rotated, the error log can easily reach tens of gigabytes in size. Especially when certain server errors occur, such as recurring errors, connection failures, or disk problems, the log can grow rapidly in a short period. Check the log file size:

ls -lh /var/log/mysql/

　　or：

ls -lh /var/lib/mysql/*.log

　　If the slowlog or error.log is growing rapidly, you need to stop writing to the logs immediately.

set global slow_query_log = 0;

　　Then rotate the logs:

mv /var/log/mysql/error.log /var/log/mysql/error.log.bak
systemctl restart mysql

　　The root cause of the log spike should then be investigated, such as missing indexes leading to too many slow queries, or abnormal business scripts causing connection failures.

　　In the InnoDB storage engine, the ibdata1 file is often the largest disk occupant, containing table metadata, rollback segments, transaction logs, etc. However, InnoDB's space reclamation does not support automatic shrinking; even after deleting a large amount of data, ibdata1 will not shrink. And when frequent large-volume writes/deletes occur, ibdata1 can rapidly expand. To check the ibdata file size:

ls -lh /var/lib/mysql/ibdata1

　　If its size reaches tens of gigabytes, it indicates severe InnoDB bloat. Cleaning up ibdata1 requires a complete rebuild, which is complex and risky, and therefore should be considered a last resort.

　　In some cases, MySQL's temporary table files can also consume a large amount of disk space. When executing complex queries, especially ORDER BY, GROUP BY, JOIN, or large-scale sorting operations, if RAM is insufficient, MySQL will write temporary tables to disk. Temporary files are typically located at /tmp or the tmpdir configured in MySQL. To check temporary files:

ls -lh /tmp/

　　If you see a large number of files starting with #sql and they continue to grow, it indicates an unreasonable SQL query design or that the innodb_buffer_pool is too small. Before cleaning up, you must ensure that the files are not being used by MySQL. You can try restarting:

systemctl restart mysql

　　Restarting will clear temporary files, but the fundamental solution is to optimize SQL or increase memory allocation.

　　As business operations continue for longer periods, the .ibd files of many tables will gradually grow. This could be due to storing large amounts of historical data or fragmentation. To check the table file size of a specific database:

du -sh /var/lib/mysql/dbname/*

　　If you find that some tables are particularly large, but the actual amount of data in your business is not that much, you can execute `OPTIMIZE TABLE` to reclaim fragments.

optimize table table_name;

　　However, please note that this operation will lock the table; use it with caution during periods of high workload.

　　After investigating and cleaning up large files, it's also necessary to ensure that future MySQL operations will not cause another surge in disk usage. Therefore, a reasonable automated management mechanism should be established. For example, regularly cleaning up the binlog:

expire_logs_days = 7

　　Periodically rotate error log:

log_error_verbosity = 2

　　Optimize memory configuration to reduce temporary file writes to disk:

innodb_buffer_pool_size = 4G
tmp_table_size = 512M
max_heap_table_size = 512M

　　And monitor disk usage regularly:

du -sh /var/lib/mysql/

　　For large-scale databases, monitoring systems such as Prometheus + mysqld_exporter or Zabbix should be used to track data directory growth in real time, ensuring early detection of anomalies.

　　During the cleanup process, the most critical principle is: any operation involving file deletion must be backed up first. Many beginners mistakenly delete binlog or ibdata, causing the database to fail to start. To avoid risks, always follow the process of "backup first, then cleanup, and finally verification."

　　Generally speaking, excessive MySQL disk space consumption is often caused by a combination of factors and cannot be solved simply by deleting files. A comprehensive investigation must be conducted, considering binlog, log files, InnoDB tablespaces, temporary files, table fragmentation, and other aspects. Only through scientific optimization and regular maintenance can database bloat be fundamentally prevented, ensuring the continuous and stable operation of business.