Phase 1: Diagnosis (Don’t Panic, Verify)
Before you run any "fix" commands, you need to confirm two things:
- Is the disk actually full?
- Is InnoDB actually corrupted, or just stopped?
First, check the disk usage. 99% of the time, this is the root cause.
df -h
If you see /dev/xvda1 at 100%, you have your culprit. But here is where most people mess up: Do not just start deleting random files.
You need to find what is taking up space without touching the database files yet. Go to your data directory (usually /var/lib/mysql or /opt/bitnami/mysql/data on AWS images) and check the file sizes:
cd /var/lib/mysql
du -h --max-depth=1 | sort -hr
If you see a massive list of mysql-bin.0000X files, those are binary logs. They are safe to purge later, but if you delete them now while the process is hung, you risk breaking the replication chain or crash recovery.
Next, check the logs to see if InnoDB is screaming for help.
tail -n 100 /var/log/mysql/error.log
You are looking for the "Red Flag" error:
[ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace mysqldb/users uses space ID: 5 at filepath: ./mysqldb/users.ibd
Or the classic:
[ERROR] InnoDB: Page [page id: space=0, page_number=5] log sequence number 123456 is in the future!
If you see these, standard restarts won't work. The database is in a "Crash Loop." It tries to start, reads the corrupted file, panics, and shuts down.
Phase 2: The Surgical Fix (The Scary Part)
If you see those InnoDB corruption errors, standard restarts will fail. You need to force the engine to start in "Read Only" mode so you can extract your data.
Warning: This process involves innodb_force_recovery. It is powerful, but if you use it wrong, you can permanently delete data.
Step 1: Stop everything. Ensure the service is dead.
sudo service mysql stop
Step 2: The Magic Config Open your MySQL configuration file (usually /etc/mysql/my.cnf or /opt/bitnami/mysql/conf/my.cnf).
Add this line under the [mysqld] section:
[mysqld]
innodb_force_recovery = 1
How this works:
- Start with 1. This tells InnoDB to ignore corrupt pages and let you read the rest.
- If it doesn't start, change it to 2, then 3, up to 4.
DANGER ZONE: Levels 5 and 6 are destructive. Do not use them unless you have a snapshot backup and know exactly what you are doing.
Step 3: Start and Dump Start the service again. If it comes up without crashing—congratulations. You are in "Life Support" mode. The database is read-only.
Now, get your data out immediately:
mysqldump -u root -p --all-databases --routines --triggers > /tmp/full_backup.sql
This command exports everything—tables, views, triggers—into a single file. Once this finishes, check the file size to make sure it looks right.
ls -lh /tmp/full_backup.sql
If that file exists and has size, you have successfully saved the company's data.
Phase 3: The Rebuild (Fresh Start)
Now that your data is safe in /tmp/full_backup.sql, it’s time to fix the corrupted engine. We are not going to try to "repair" the files (which rarely works). We are going to nuke them and rebuild.
1. Stop the service again.
sudo service mysql stop
2. Remove the recovery flag. Go back to my.cnf and **remove **or comment out the line we added earlier:
# innodb_force_recovery = 1 <-- Comment this out!
3. Move the corrupted data. Do not rm it yet! Move it to a backup folder just in case.
sudo mv /var/lib/mysql /var/lib/mysql_corrupted_backup
4. Initialize a fresh instance. Create a new, empty data directory.
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql
sudo mysqld --initialize --user=mysql
5. Start and Import. Start the service. It should come up instantly because it's empty. Now, pour your data back in:
mysql -u root -p < /tmp/full_backup.sql
Conclusion: Don't Let It Happen Again
You saved the database, but you haven't fixed the root cause. If you don't set up Log Rotation, that disk will fill up again in a month.
As a quick fix, set your binary logs to expire automatically. In MySQL 8.0, run this inside the SQL prompt:
SET GLOBAL binlog_expire_logs_seconds = 259200; -- 3 Days
Now, go grab a coffee. You earned it.