MariaDB & MySQL InnoDB System Tablespace File (ibdata1)

Bottom Line: NEVER delete ibdata1 directly from the file system. Deleting ibdata1 while database tables exist will destroy your MariaDB or MySQL database engine. To shrink ibdata1, you must perform a mysqldump, drop databases, and recreate the tablespace.

Why Does MariaDB ibdata1 Grow So Large?

The ibdata1 file located at /var/lib/mysql/ibdata1 or C:\ProgramData\MySQL\MySQL Server\Data is the default system tablespace for the InnoDB storage engine.

• Primary Content: Stores undo logs, insert buffers, doublewrite buffers, and database dictionary metadata.
• Growth Behavior: By default, ibdata1 expands automatically as transactions run, but never shrinks on its own, even after dropping large tables.
• Storage Impact: Can consume 5 GB to 100 GB+ of server SSD storage.

What Happens If You Delete ibdata1 Directly?

• System Safety: ❌ DO NOT DELETE DIRECTLY. Deleting ibdata1 causes MariaDB/MySQL server boot failure (InnoDB: Error: fetch of persistent statistics failed).
• Correct Shrink Method: Dump databases (mysqldump), drop all user databases, stop service, delete ibdata1, and re-import data.

How to Safely Shrink MariaDB ibdata1

Step 1: Backup all databases

mysqldump --all-databases --single-transaction --quick -u root -p > all_databases.sql

Step 2: Drop all user databases in MySQL prompt

DROP DATABASE my_database;

Step 3: Enable file-per-table in /etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
innodb_file_per_table = 1

Step 4: Stop MariaDB, remove ibdata1 and ib_logfile*, then restart

sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*
sudo systemctl start mariadb

Step 5: Restore databases

mysql -u root -p < all_databases.sql

Frequently Asked Questions (FAQ)

Why didn’t dropping a 20GB table reduce the size of ibdata1?

InnoDB system tablespace pages are kept internally for future allocations. Setting innodb_file_per_table = 1 ensures future tables store data in separate .ibd files that can be reclaimed via OPTIMIZE TABLE.