Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can you repair all tables in all databases from the MySQL command prompt when MYI file is corrupted or missing?

Tags:

mysql

When dealing with MySQL database corruption, if the MYI index file is missing or if its header is corrupted you can't use a myisamchk command:

myisamchk --safe-recover --force --sort_buffer_size=2G --key_buffer_size=2G /var/lib/mysql/*/*.MYI

You have to do the repair from the MySQL command prompt with the use_frm option:

repair tbl_name use_frm;

Per MySQL documentation's on repairing tables

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

With myisamchk, you can easily drop into each database folder and repair every table by using asterisks at the end of command:

/var/lib/mysql/*/*.MYI

You can't do anything similar from the MySQL command prompt.

There's a StackOverflow question with an answer that explains how to repair all tables within one specific database from the MySQL command prompt with a procedure:

CREATE DEFINER = 'root'@'localhost'
PROCEDURE MYDATABASE.repair_all()
BEGIN
  DECLARE endloop INT DEFAULT 0;
  DECLARE tableName char(100);
  DECLARE rCursor CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE();
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endloop=1;

  OPEN rCursor;
  FETCH rCursor INTO tableName;

  WHILE endloop = 0 DO
    SET @sql = CONCAT("REPAIR TABLE `", tableName, "`");
    PREPARE statement FROM @sql;
    EXECUTE statement;

    FETCH rCursor INTO tableName;
  END WHILE;

  CLOSE rCursor;
END

Is it possible to modify a procedure like this to loop through all your MySQL databases and repair every table within those databases?

I think this could be useful for anyone who has a large number of databases and runs into serious corruption.

like image 279
Drew Avatar asked Aug 16 '11 17:08

Drew


People also ask

How repair corrupted MySQL table Myisamchk?

First, try myisamchk -r -q tbl_name ( -r -q means “quick recovery mode”). This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed.

How do I recover a corrupted table?

This involves regaining access to the corrupted table, using the mysqldump utility to create a logical backup of the table, which will retain the table structure and the data within it, and then reloading the table back into the database.


2 Answers

mysqlcheck is a more convenient command-line interface to the MySQL CHECK, REPAIR, ANALYZE and OPTIMIZE statements.

mysqlcheck --repair --use-frm --all-databases
like image 115
John Flatness Avatar answered Sep 20 '22 15:09

John Flatness


Here's my solution for when I had to fix all of the MyISAM files in my DB:

find ./ -name "*.MYI" -exec myisamchk -r {} \;

It traverses all of the databases.

like image 31
Shawn DeWolfe Avatar answered Sep 18 '22 15:09

Shawn DeWolfe