Common Issues·4 min lezen

Corrupted Database: MySQL and MariaDB Recovery

MySQL or MariaDB won't start or shows corrupted table errors? Guide to recovery with mysqlcheck, myisamchk, InnoDB crash recovery and emergency backup.

Database 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

bash

# 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.

bash

# 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:

bash

# 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:

bash

nano /etc/mysql/mysql.conf.d/mysqld.cnf
# or
nano /etc/my.cnf

Add under [mysqld]:

ini

[mysqld]
innodb_force_recovery = 1

Try to start:

bash

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:

bash

# 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

bash

# 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:

bash

# 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.

ini

[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

bash

# 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

ini

[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

Locked Out of VPS

Complete guide to recover server access when locked out, with step-by-step instructions from VNC Console

5 min lezen

Common Issues

Server Unreachable

What to do when server is not responding or you can't connect via SSH

3 min lezen

Common Issues

Website Not Reachable

What to do when website is not responding, shows errors, or is unreachable

3 min lezen

Terug naar Documentatie

DeluxHost, opgericht in 2023, biedt hoogwaardige hostingoplossingen voor diverse digitale behoeften. Wij bieden gedeelde hosting, VPS en dedicated servers met geavanceerde beveiliging en wereldwijde datacenters.

Alle systemen operationeel

Animaties inschakelen