When you discover a massive ibdata1 file consuming disk space in /var/lib/mysql, you're looking at MySQL's core system tablespace file. This file serves multiple critical functions:

• Stores InnoDB data dictionary (metadata about tables, indexes, and columns)
• Contains the doublewrite buffer (crash recovery mechanism)
• Holds undo logs (for transaction rollbacks)
• Contains all InnoDB tables' data when using the shared tablespace configuration (default before MySQL 5.6)

The most common causes of ibdata1 inflation:

# Check current InnoDB configuration
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'innodb_data_file_path';

If innodb_file_per_table=OFF, all InnoDB tables store data directly in ibdata1. Even if you delete tables, the space isn't reclaimed.

Option 1: Migrate to file-per-table (Recommended)

1. Dump all databases:

   mysqldump -u root -p --all-databases > full_backup.sql

2. Stop MySQL:

   systemctl stop mysql

3. Delete ibdata1 and ib_logfiles:

   rm /var/lib/mysql/ibdata*
   rm /var/lib/mysql/ib_logfile*

4. Enable file-per-table in my.cnf:

   [mysqld]
   innodb_file_per_table=1

5. Restart MySQL and restore data:

   systemctl start mysql
   mysql -u root -p < full_backup.sql

Option 2: Resize Existing ibdata1

For MySQL 5.7+, you can shrink by:

SET GLOBAL innodb_fast_shutdown = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;
FLUSH TABLES WITH READ LOCK;

Then restart MySQL with temporary config:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

• Always enable innodb_file_per_table
• Monitor with:

  SELECT table_schema, table_name, 
         data_length+index_length AS size_bytes
  FROM information_schema.tables
  WHERE engine='InnoDB';

• Set up regular OPTIMIZE TABLE for fragmented tables

Avoid these mistakes:

• Simply deleting ibdata1 without dump/restore (will corrupt DB)
• Changing innodb_file_per_table without rebuilding tables
• Not allocating enough temporary disk space for the migration

Remember that in production environments, always test these procedures on a staging server first and have verified backups.

The ibdata1 file is a critical system tablespace file in MySQL's InnoDB storage engine. It typically contains:

• The InnoDB data dictionary (metadata about tables, indexes, etc.)
• Doublewrite buffer (crash recovery mechanism)
• Insert buffer (for secondary index changes)
• Rollback segments (for transaction rollbacks)

Unlike regular tables which can be shrunk, ibdata1 grows monotonically by default. Common causes of excessive growth include:

# Check current ibdata1 size
ls -lh /var/lib/mysql/ibdata1

# Monitor growth over time
watch -n 60 'du -sh /var/lib/mysql/ibdata1'

Absolutely not! Deleting ibdata1 will corrupt your database. Instead, consider these approaches:

Option 1: Enable file-per-table

Configure MySQL to store each table in separate .ibd files instead of ibdata1:

[mysqld]
innodb_file_per_table=1
innodb_data_file_path=ibdata1:12M:autoextend

Option 2: Clean Up and Rebuild

For existing large ibdata1, the safest method is a controlled rebuild:

# 1. Create full database dump
mysqldump --all-databases --single-transaction > full_backup.sql

# 2. Stop MySQL
sudo systemctl stop mysql

# 3. Remove old files (keep backup!)
mv /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.bak
mv /var/lib/mysql/ib_logfile* /var/lib/mysql/

# 4. Restart MySQL and import
sudo systemctl start mysql
mysql < full_backup.sql

Implement regular monitoring to prevent future issues:

# Set up daily size check
echo "du -sh /var/lib/mysql/ibdata1" >> /etc/cron.daily/mysql_size_check
chmod +x /etc/cron.daily/mysql_size_check

For very large databases, consider:

• Partitioning large tables
• Using MySQL 8.0+ which has improved tablespace management
• Implementing a proper archiving strategy