How to Free Up MySQL Disk Space When Data_free Stalls: A Step‑by‑Step Guide

When disk usage reaches 100% with only a few megabytes left, typical attempts like backing up databases, dropping tables, or restarting MySQL may not free space.

Problem : data_free occupies a large portion of the tablespace, as shown by the following query.

SELECT CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size,
       CONCAT(TRUNCATE(SUM(max_data_length)/1024/1024,2),'MB') AS max_data_size,
       CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free,
       CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size
FROM information_schema.tables
WHERE TABLE_NAME = 'datainfo';

The result showed data_free of about 20 GB.

Typical fix (when InnoDB is supported):

ALTER TABLE datainfo ENGINE=InnoDB;
ANALYZE TABLE datainfo;
OPTIMIZE TABLE datainfo;

However, the MySQL version (5.5.62) does not support InnoDB, and the table was later deleted without freeing space, so the usual fragmentation cleanup failed.

Effective workaround :

Locate MySQL binary and configuration:

mysql 1118 945 0 14:28 ? 00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql ...

Stop MySQL: service mysql stop Delete ibdata1, ib_logfile0, ib_logfile1 in the data directory.

Move the original my.cnf aside: mv /etc/my.cnf ./abc Restart MySQL: service mysql start. Disk space is released.

After space is reclaimed, the database can be restored using Navicat:

Backup the database to a .psc file.

Create a new instance with the desired name and charset.

Import the backup file; adjust the restore configuration to avoid transaction abort on errors.

Root causes of MySQL fragmentation :

Deleted rows leave empty space; massive deletions can make free space larger than used space.

Insert operations may not reuse small free gaps, creating fragmentation.

Scanning operations read the table’s maximum capacity, exposing unused regions.

Benefits of cleaning fragmentation : lower I/O, improved performance, and reduced disk usage.

Precautions :

Do not run OPTIMIZE TABLE too frequently; weekly or monthly is sufficient.

The operation locks the table, so schedule it during low‑traffic periods.

Example: optimizing a 1.05 million‑row table takes about 37 seconds locally.

Self‑test : run SHOW TABLE STATUS FROM db_name; to inspect Data_free values.