How to recover or recreate MYI file of a Table in MySQL DB
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
-
- Locate the database stored on your server. Basically its under /var/lib/mysql/ path
cd /var/lib/mysql/database_name
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.
- Run these commands given below
myisamchk -r -q table_name
myisamchk --safe-recover table_name
mysqlcheck --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
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.
- Locate the database stored on your server. Basically its under /var/lib/mysql/ path