How to recover or recreate MYI file of a Table in MySQL DB

Category : Advanced

Hello,

Today we will tell you about MyISAM and MYI file and how to recover or recreate MYI file for Table in MySQL Database.

MySQL relational database management system prior versions to 5.5 were using MyISAM as a default storage system for databases. MyISAM is old but it has many useful extensions till date. MyISAM table has a ‘.MYI’ file for table’s indexes.

The .MYI file consist of two parts: the header information and the key values. If the table indexes are not properly organised then Database management software has to struggle and it uses extra resources to perform the task on the database. Sometimes situation arises that user messes up the database and deletes the .MYI file or wants to recreate the .MYI for any table in the MySQL database.

So we can follow below steps to recover or recreate the .MYI file

    1. Locate the database stored on your server. Basically its under /var/lib/mysql/ path
      cd /var/lib/mysql/database_name
    2. Lookout for your table and check if .MYI file is present there or not. If its there and you need to recreate a fresh .MYI for particular table in respected database then delete the .MYI.
    3. Run these commands given belowmyisamchk -r -q  table_namemyisamchk --safe-recover table_namemysqlcheck --user=database_user --password=user_password --repair --use-frm database_name table_name

      sudo service mysql stop

      myisamchk -r -f -o /var/lib/mysql/database_name/table_name

      sudo service mysql start

      sudo service mysql status

    4. After running above commands your fresh indexes of the respected table .MYI file will be recreated which in other sense means it has been recovered too.