# ibdata1 Growing? How to Free Up Disk Space in MySQL

> Why the ibdata1 file grows in MySQL, how to check what is taking up space, and how to safely shrink the shared tablespace file.

## Why the ibdata1 File Grows

ibdata1 is InnoDB's system tablespace file. It always stores service data: the data dictionary, the undo log, and, if innodb\_file\_per\_table is disabled, the tables themselves together with their indexes. The file sits in the data directory, usually /var/lib/mysql/ibdata1, and it never shrinks on its own — it only grows.

A quirk of InnoDB is that even deleting rows or entire tables does not return space to the operating system: the engine marks the space as free inside the file itself and reuses it for new data, but the file's size on disk stays the same.

## How to Check What Is Actually Taking Up Space

Before deleting anything, figure out whether ibdata1 is the culprit or the problem lies in individual table files.

```
du -sh /var/lib/mysql/ibdata1
du -sh /var/lib/mysql/*/ | sort -rh | head -20
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024,1) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;
```

If innodb\_file\_per\_table is enabled (the default since version 5.6), each table is stored in its own .ibd file, and ibdata1 keeps only the dictionary and the undo logs — usually a few hundred megabytes at most.

## What Is Stored in ibdata1 vs. Separate Files

| Content | Location with file\_per\_table=ON | Location with file\_per\_table=OFF |
| --- | --- | --- |
| Table data and indexes | separate table\_name.ibd file | inside ibdata1 |
| InnoDB data dictionary | ibdata1 | ibdata1 |
| Transaction undo logs | ibdata1 or separate undo files | ibdata1 |

## How to Enable File-Per-Table for New Tables

The parameter is enabled in the configuration and only applies to tables created after the change — it does not automatically touch old tables inside ibdata1.

```
# /etc/mysql/my.cnf, [mysqld] section
innodb_file_per_table = 1
```

After editing, the configuration is reloaded by restarting the service. More on my.cnf sections and related parameters is covered in the article on [my.cnf configuration](https://www.zevshost.net/en/kb/mysql-my-cnf-optimizatsiya/).

## How to Shrink an Already Bloated ibdata1

There is no built-in command to shrink ibdata1 — the file only gets smaller through a full rebuild of the data directory. This is a planned operation with service downtime, not a quick fix.

- Take a full dump of all databases with [mysqldump](https://www.zevshost.net/en/kb/mysqldump-backup-restore/), including triggers, procedures, and events.
- Stop MySQL and move (not delete right away) the entire data directory to a backup location.
- Enable innodb\_file\_per\_table and initialize a new empty data directory.
- Import the dumps back — new tables will be created in separate .ibd files.

```
systemctl stop mysql
mv /var/lib/mysql /var/lib/mysql.old
mysqld --initialize --datadir=/var/lib/mysql
systemctl start mysql
mysql -u root -p < /root/full_backup.sql
```

## Prevention: What Else Takes Up Disk Space

ibdata1 is not the only source of data directory growth, and several causes often get confused at once.

- Temporary tables for large GROUP BY and ORDER BY are created on disk in ibtmp1, which also does not shrink without a server restart.
- Replication binary logs pile up without rotation — configuring their retention is covered in the article on [binlog and point-in-time recovery](https://www.zevshost.net/en/kb/mysql-binlog-point-in-time/).
- Table fragmentation after bulk DELETE operations is reduced with OPTIMIZE TABLE, but it requires free space equal to the table's size.

## Summary: Disk Space Checklist

- Check the size of ibdata1 and whether innodb\_file\_per\_table is enabled.
- Find the real space consumers through information\_schema.tables.
- Keep file\_per\_table enabled by default for new tables.
- Shrink ibdata1 only through a full dump and data directory rebuild.
- Set up binary log rotation so they do not grow unchecked.

---

Source: https://www.zevshost.net/en/kb/mysql-ibdata-mesto-na-diske/