Torna alla DocumentazioneDatabase corruption typically happens after server crash, sudden power loss, or disk full during a write. Symptoms are: service won't start, queries fail with "Table is marked as crashed" or InnoDB errors in log.
Initial diagnosis
# Check MySQL/MariaDB error log
tail -100 /var/log/mysql/error.log
# or
journalctl -u mysql -n 100 --no-pager
# Typical corruption signals:
# [ERROR] InnoDB: Database page corruption on disk or a failed file read
# [ERROR] Table './myDb/table' is marked as crashed
# [ERROR] Incorrect key file for table
Quick recovery with mysqlcheck
mysqlcheck works while database is running: it's the first tool to use.
# Check and repair all databases
mysqlcheck -u root -p --all-databases --auto-repair
# Only a specific database
mysqlcheck -u root -p --repair myDatabase
# Only a specific table
mysqlcheck -u root -p --repair myDatabase myTable
# Check without repairing (diagnosis only)
mysqlcheck -u root -p --check --all-databases
mysqlcheck works well on MyISAM tables. For InnoDB (the modern default) the procedure is different: see the dedicated section below.
Recover MyISAM tables
If mysqlcheck isn't enough, use myisamchk directly on files:
# First stop MySQL to prevent further damage
systemctl stop mysql
# Find .MYI files (MyISAM indexes)
find /var/lib/mysql -name "*.MYI"
# Repair a specific table
myisamchk --recover --quick /var/lib/mysql/myDatabase/myTable.MYI
# More aggressive repair (if previous fails)
myisamchk --recover --force /var/lib/mysql/myDatabase/myTable.MYI
# Total repair with index rebuild
myisamchk --safe-recover /var/lib/mysql/myDatabase/myTable.MYI
# Restart MySQL
systemctl start mysql
Recover InnoDB (crash recovery)
InnoDB has a built-in crash recovery mechanism. If MySQL won't start:
Step 1: Force startup in recovery mode
Modify the configuration:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
nano /etc/my.cnf
Add under [mysqld]:
[mysqld]
innodb_force_recovery = 1
Try to start:
systemctl start mysql
If it doesn't start with 1, increment the value up to 6. Each level is more aggressive and permissive:
| Level | What it does |
|---|---|
| 1 | Ignores corrupted pages |
| 2 | Disables background threads |
| 3 | Doesn't execute thread rollback |
| 4 | Disables insert buffer merge |
| 5 | Doesn't verify undo logs |
| 6 | Doesn't execute redo logs |
With high levels (4-6) the database is read-only. Use them only to extract data then restore from backup.
Step 2: Export the data
With MySQL running in recovery mode, export everything immediately:
# Dump all databases
mysqldump -u root -p --all-databases --single-transaction \
> /root/backup_emergency_$(date +%Y%m%d).sql
# If it fails, export database by database
for db in $(mysql -u root -p -e "SHOW DATABASES;" | grep -v Database | grep -v information_schema | grep -v performance_schema); do
mysqldump -u root -p "$db" > /root/backup_${db}.sql 2>/dev/null
echo "Exported: $db"
done
Step 3: Clean restore
# Stop MySQL
systemctl stop mysql
# Remove innodb_force_recovery from configuration
nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Delete the innodb_force_recovery line
# Delete corrupted InnoDB files
rm /var/lib/mysql/ibdata1
rm /var/lib/mysql/ib_logfile*
# Restart (MySQL recreates InnoDB files)
systemctl start mysql
# Reimport the dump
mysql -u root -p < /root/backup_emergency.sql
Database won't start at all
If you can't start MySQL even in recovery mode:
# Check for zombie process blocking socket
ps aux | grep mysql
kill -9 <PID_zombie>
# Remove blocked socket
rm -f /var/run/mysqld/mysqld.sock
rm -f /var/lib/mysql/mysql.sock
# Check permissions
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
# Try to restart
systemctl start mysql
Future prevention
Enable binary log (PITR)
Binary log allows recovery up to the moment of crash, not just the last backup.
[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 604800 # 7 days
max_binlog_size = 100M
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
Automatic backups
# Script for daily backup with 7-day rotation
cat > /usr/local/bin/mysql-backup.sh << 'EOF'
#!/bin/bash
BACKUP_DIR="/root/mysql-backups"
DATE=$(date +%Y%m%d_%H%M)
mkdir -p $BACKUP_DIR
mysqldump -u root -p"YOUR_PASSWORD" --all-databases \
--single-transaction --quick \
> "$BACKUP_DIR/full_$DATE.sql"
gzip "$BACKUP_DIR/full_$DATE.sql"
# Delete backups older than 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
EOF
chmod +x /usr/local/bin/mysql-backup.sh
# Add to cron
echo "0 2 * * * root /usr/local/bin/mysql-backup.sh" >> /etc/cron.d/mysql-backup
Prevent corruption with innodb_flush
[mysqld]
# Maximum durability: write to disk at every commit
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# Better performance but risk on crash (value 2)
# innodb_flush_log_at_trx_commit = 2
Articoli correlati
Locked Out of VPS
Complete guide to recover server access when locked out, with step-by-step instructions from VNC Console
Server Unreachable
What to do when server is not responding or you can't connect via SSH
Website Not Reachable
What to do when website is not responding, shows errors, or is unreachable
DeluxHost, fondata nel 2023, offre soluzioni di hosting di alta qualità per diverse esigenze digitali. Forniamo hosting condiviso, VPS e server dedicati con sicurezza avanzata e datacenter globali.
