When MySQL encounters disk space issues or unexpected shutdowns, you might see the dreaded "table is marked as crashed" error. This typically occurs with MyISAM tables (though InnoDB has its own recovery mechanisms). The error message clearly indicates which table needs attention:

mysqldump: Got error: 145: Table './myDBname/myTable1' is marked as crashed and should be repaired

For MyISAM tables, MySQL provides built-in repair tools. Here's how to fix it:

-- Connect to MySQL
mysql -u root -p

-- Repair the specific table
REPAIR TABLE myDBname.myTable1;

-- For multiple tables (more efficient)
REPAIR TABLE myDBname.myTable1, myDBname.myTable2;

If the standard repair doesn't work, try the extended option:

REPAIR TABLE myDBname.myTable1 EXTENDED;

For severe corruption or when the MySQL client isn't accessible:

# Use the myisamchk utility (stop MySQL first)
sudo service mysql stop
myisamchk -r /var/lib/mysql/myDBname/myTable1.MYI
sudo service mysql start

For multiple tables in a database:

myisamchk -r /var/lib/mysql/myDBname/*.MYI

After recovery, implement these preventive measures:

-- Schedule regular table checks
CREATE EVENT check_tables
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
  CHECK TABLE myDBname.myTable1, myDBname.myTable2;
END;

-- Consider converting to InnoDB (if appropriate)
ALTER TABLE myDBname.myTable1 ENGINE=InnoDB;

Here's a bash script to automate the repair process:

#!/bin/bash
DB_NAME="myDBname"
MYSQL_USER="root"
MYSQL_PASS="yourpassword"

# Get list of corrupted tables
TABLES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '$DB_NAME' AND ENGINE = 'MyISAM';" | grep -v "TABLE_NAME")

for table in $TABLES; do
  echo "Checking $table..."
  CHECK_RESULT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -e "CHECK TABLE $DB_NAME.$table" | grep -i "error\|crashed")
  if [ ! -z "$CHECK_RESULT" ]; then
    echo "Repairing $table..."
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "REPAIR TABLE $DB_NAME.$table"
  fi
done

When you encounter the error "Table './myDBname/myTable1' is marked as crashed and should be repaired" after a disk space issue, it typically indicates corruption in MySQL's MyISAM storage engine tables. This occurs when:

• The server crashed during write operations
• Disk space was exhausted during table operations
• Hardware failures occurred
• The MySQL process was killed abruptly

For MyISAM tables (the most common scenario for this error), use the REPAIR TABLE command:

mysql> REPAIR TABLE myDBname.myTable1;
+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| myDBname.myTable1    | repair | status   | OK       |
+----------------------+--------+----------+----------+

If the above doesn't work, try these approaches:

Using mysqlcheck utility:

mysqlcheck --repair --databases myDBname -u root -p

Manual repair with myisamchk (requires MySQL server to be stopped):

# Stop MySQL first
service mysql stop

# Repair the specific table
myisamchk -r /var/lib/mysql/myDBname/myTable1.MYI

# For severe corruption:
myisamchk --safe-recover /var/lib/mysql/myDBname/myTable1.MYI

Consider these best practices:

• Convert critical tables to InnoDB: ALTER TABLE myTable1 ENGINE=InnoDB;
• Set up proper monitoring for disk space
• Implement regular maintenance with OPTIMIZE TABLE
• Use CHECK TABLE periodically to detect early signs of corruption

If repairs fail, restore from backup:

mysql -u root -p myDBname < /path/to/backup.sql