MySQL database repair

by

If you need help, you can simply ask me via the contact form. I would be happy to repair or check you database/tables.

MyISAM tables

Check

The client mysqlcheck is used to check your MySQL database. This checks, repairs, optimizes and analyzes tables. The mysqlcheck utility can be run while the MySQL server is still running.

The client provides the SQL statements CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE.

To check, run the following command in your console window:

mysqlcheck -u <loginuser> -p --all-databases --check

Just replace <loginuser> with your MySQL user. The parameter –all-databases checks all databases for errors and –check checks the tables for errors. You just need to enter your password.

Repair

With the mysqlcheck client, you have the option of repairing a defective database. The MySQL server can also run here.

mysqlcheck -u <loginuser> -p --auto-repair --all-databases

Just replace it with your MySQL user. The parameter –all-databases checks all databases for errors and –auto-repair automatically repairs damaged database tables.

If you want to force all tables to repair, just run the following command:

mysqlcheck -u <loginuser> -p --repair --all-databases

InnoDB tables

Check

If you use MySQL with InnoDB tables, the following error messages indicate a faulty database:

InnoDB: (index "PRIMARY" of table "database"."table")
InnoDB: Database page corruption on disk or a failed

To check all tables in your database for errors, use the following command:

mysqlcheck -u <loginuser> -p --check --all-databases

Just replace it with your MySQL user. The parameter –all-databases checks all databases for errors and –check checks the tables for errors. You just need to enter your password.

Repair

The OPTIMIZE TABLE command attempts to restore the table and its indexes. Here, too, it is possible to use the command globally or on individual tables:

mysqlcheck -u <loginuser> -p --optimize --all-databases

Just replace it with your MySQL user. The parameter –all-databases checks all databases for errors and –optimize optimize all tables in the database. You just need to enter your password.

If the warning “Table does not support optimize, doing recreate + analyze instead” occurs, this is not a problem. The OPTIMIZE command works differently with InnoDB tables than with MyISAM tables.

Leave a Comment