# MySQL ibdata Corruption: Diagnosing and Repairing System Tablespace

MySQL fails to start with InnoDB system tablespace errors:

bash
[ERROR] InnoDB: Corrupt page [page id: seq no=123456, space id: 0] in file ./ibdata1
[ERROR] InnoDB: Page checksum 19548785, stored checksum 24154312
[ERROR] InnoDB: Database page corruption on disk or a failed file read
[ERROR] InnoDB: Could not open or create the system tablespace

The ibdata file contains critical system data: the data dictionary, undo logs, doublewrite buffer, and potentially table data. Corruption here is serious but often recoverable.

Understanding ibdata Structure

The ibdata file contains:

  1. 1.Data Dictionary: Metadata about all tables, columns, indexes
  2. 2.Undo Logs: For transaction rollback
  3. 3.Doublewrite Buffer: Recovery mechanism for partial writes
  4. 4.Change Buffer: For secondary index modifications
  5. 5.Table Data: If innodb_file_per_table is disabled

Step 1: Assess the Corruption

Start MySQL in recovery mode:

bash
# Check error log for details
tail -200 /var/log/mysql/error.log | grep -i "ibdata\|corrupt\|error"

Add recovery mode to configuration:

ini
# /etc/my.cnf
[mysqld]
innodb_force_recovery = 1

Start MySQL:

bash
systemctl start mysqld
mysql -u root -p -e "SELECT 1;"

If level 1 fails, progressively increase:

ini
innodb_force_recovery = 2  # Try if 1 fails
innodb_force_recovery = 3  # More aggressive
innodb_force_recovery = 4  # Skips insert buffer merge
innodb_force_recovery = 5  # Skips undo log scan
innodb_force_recovery = 6  # Skips redo log recovery

Step 2: Check What Data Is Accessible

Once MySQL starts (even in recovery mode):

```sql -- Check InnoDB status SHOW ENGINE INNODB STATUS\G

-- List databases SHOW DATABASES;

-- Check tables in each database USE your_database; SHOW TABLES;

-- Count rows to see what's accessible SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema'); ```

Step 3: Dump All Accessible Data Immediately

This is critical - dump data while MySQL is running:

```bash # Dump all databases mysqldump --all-databases --routines --triggers --events \ --single-transaction --quick \ --user=root --password > ibdata_recovery_backup_$(date +%Y%m%d_%H%M%S).sql

# If single-transaction fails (due to recovery mode) mysqldump --all-databases --routines --triggers --events \ --skip-lock-tables --quick \ --user=root --password > ibdata_recovery_backup.sql

# Dump specific tables if full dump fails for db in $(mysql -N -e "SHOW DATABASES" | grep -v information_schema | grep -v performance_schema); do mysqldump --skip-lock-tables $db > ${db}_backup.sql done ```

Step 4: Check for Specific Table Corruption

```sql -- Check InnoDB table integrity CHECK TABLE important_table EXTENDED;

-- Identify which tables are in ibdata vs individual files SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE ENGINE = 'InnoDB' AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') ORDER BY DATA_LENGTH DESC; ```

Check if tables are file-per-table:

```bash ls -la /var/lib/mysql/your_database/*.ibd

# If .ibd files exist, tables use file-per-table # If no .ibd files, data is in ibdata ```

Step 5: Identify Corruption Location

```sql -- Check system tablespace pages SELECT * FROM information_schema.INNODB_SYS_TABLESPACES WHERE SPACE = 0;

-- Check buffer pool for errors SHOW ENGINE INNODB STATUS\G -- Look for "LATEST FOREIGN KEY ERROR" or "LATEST DETECTED DEADLOCK" ```

Check specific page numbers mentioned in error log:

```bash # Error log might show: "page [page id: seq no=12345, space id: 0]" # This is in the system tablespace (space id: 0 = ibdata)

# View ibdata structure ls -la /var/lib/mysql/ibdata*

# Check file size (default 12MB, can be larger) du -h /var/lib/mysql/ibdata1 ```

Step 6: Attempt Recovery with innodb_force_recovery

At different recovery levels, certain operations are limited:

LevelAllowed Operations
1-2SELECT, CREATE TABLE, DROP TABLE
3SELECT only (no modifications)
4-6SELECT only, limited functionality

```sql -- At level 1-2, try to identify and fix problematic tables -- Create a new table to replace corrupted one CREATE TABLE fixed_table LIKE corrupted_table;

-- Copy what data you can INSERT INTO fixed_table SELECT * FROM corrupted_table;

-- If this fails at a certain row, use LIMIT to find the breaking point SELECT MAX(id) FROM corrupted_table WHERE id < 50000; INSERT INTO fixed_table SELECT * FROM corrupted_table WHERE id <= 49000; ```

Step 7: Rebuild ibdata (Nuclear Option)

If recovery fails and you have a good dump:

```bash # STOP! Ensure you have a complete backup ls -la ibdata_recovery_backup_*.sql

# Stop MySQL systemctl stop mysqld

# Backup existing data directory mv /var/lib/mysql /var/lib/mysql.corrupted.backup

# Or just backup ibdata cp /var/lib/mysql/ibdata1 /var/lib/mysql/ibdata1.corrupted cp /var/lib/mysql/ib_logfile* /var/lib/mysql/ib_logfile.corrupted/

# Edit my.cnf for clean rebuild vi /etc/my.cnf ```

Configure for optimal ibdata:

```ini [mysqld] # Enable file-per-table (recommended) innodb_file_per_table = 1

# Set appropriate ibdata size innodb_data_file_path = ibdata1:1G:autoextend

# Remove or set to 0 # innodb_force_recovery = 0 ```

Reinitialize MySQL:

```bash # Create new data directory mkdir -p /var/lib/mysql chown mysql:mysql /var/lib/mysql

# Initialize MySQL mysqld --initialize --user=mysql

# Start MySQL systemctl start mysqld

# Get temporary password (MySQL 5.7+) grep 'temporary password' /var/log/mysql/error.log

# Login and change password mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewSecurePassword123!'; ```

Restore from backup:

```bash # Restore databases mysql -u root -p < ibdata_recovery_backup.sql

# Or restore individual databases mysql -u root -p database_name < database_backup.sql ```

Step 8: Handle Partial Recovery

If dump failed for some tables:

```bash # Start old MySQL in recovery mode (separate port) # /etc/my-recovery.cnf [mysqld] port = 3307 datadir = /var/lib/mysql.corrupted.backup innodb_force_recovery = 3

# Start recovery instance mysqld --defaults-file=/etc/my-recovery.cnf &

# Attempt to extract remaining data mysql -P 3307 -u root -p -e "SELECT * FROM problem_table LIMIT 1000;" > recovered_data.sql ```

Step 9: Repair Information Schema Issues

If system tables in ibdata are corrupted:

```sql -- Check mysql system database tables USE mysql;

-- Check system table integrity CHECK TABLE user EXTENDED; CHECK TABLE db EXTENDED; CHECK TABLE tables_priv EXTENDED; CHECK TABLE columns_priv EXTENDED;

-- Repair if needed REPAIR TABLE user; REPAIR TABLE db; ```

For MySQL system table issues:

```bash # Upgrade/repair system tables mysql_upgrade -u root -p

# For MySQL 5.7 and earlier mysql_upgrade --force -u root -p ```

Step 10: Verify Recovery

After restoration:

```sql -- Check all databases exist SHOW DATABASES;

-- Verify table counts SELECT TABLE_SCHEMA, COUNT(*) as table_count FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;

-- Check for orphaned tables SELECT * FROM information_schema.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

-- Verify foreign key constraints SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL;

-- Run full check mysqlcheck --all-databases --check --user=root --password ```

Step 11: Configure for Future Safety

Prevent future ibdata issues:

```ini [mysqld] # Use file-per-table (keeps table data out of ibdata) innodb_file_per_table = 1

# Adequate initial size (avoid autoextend overhead) innodb_data_file_path = ibdata1:1G:autoextend:max:10G

# Larger buffer pool reduces disk writes innodb_buffer_pool_size = 4G # 70-80% of available RAM

# Proper log file size innodb_log_file_size = 512M

# Doublewrite buffer (crash protection) innodb_doublewrite = 1

# Flush method for Linux innodb_flush_method = O_DIRECT

# Crash safety innodb_flush_log_at_trx_commit = 1 ```

Monitoring ibdata Health

```sql -- Check ibdata size SELECT FILE_NAME, TOTAL_EXTENTS, EXTENT_SIZE, TOTAL_EXTENTS * EXTENT_SIZE / 1024 / 1024 AS Size_MB FROM information_schema.FILES WHERE FILE_NAME LIKE '%ibdata%';

-- Monitor for fragmentation SHOW ENGINE INNODB STATUS\G -- Look at "FILE I/O" and "INSERT BUFFER AND ADAPTIVE HASH INDEX" sections ```

Troubleshooting Checklist

  • [ ] Check error log for specific error codes
  • [ ] Start with innodb_force_recovery = 1
  • [ ] Progressively increase recovery level if needed
  • [ ] Dump all accessible data immediately
  • [ ] Identify which tables are corrupted
  • [ ] Determine if tables are in ibdata or .ibd files
  • [ ] Rebuild ibdata if recovery fails
  • [ ] Restore from backup
  • [ ] Configure innodb_file_per_table = 1
  • [ ] Verify data integrity after recovery
  • [ ] Set up monitoring and backups

Warning Signs to Monitor

```bash # Check for growing ibdata watch -n 60 'ls -lh /var/lib/mysql/ibdata1'

# Monitor InnoDB status mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "LOG"

# Check for pending operations mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 10 "PENDING" ```