How to Repair MySQL Tables using Stellar Repair?

As a MySQL user, you may encounter unexpected errors like Can’t find tablethatshouldbethere.MYI file, Tablethatwasworking.frm is locked, Unexpected end of file, Got error ### from table handler, etc. Due to such errors, the tables in MySQL become inaccessible. Accessing data in MySQL tables may require repairing them. Before proceeding to repair, you may need to check whether the table is actually corrupted or not, the exact name of the inaccessible tables, or the schema of the table to review the structure. To perform this, you can use the commands such as CHECKTABLE, SHOW DATABASES, SELECT DATABASE (), SHOW TABLES, DESCRIBE, etc. These commands can help you diagnose MySQL table errors and issues, and prepare you to resolve them effectively.

In this article, we will be explaining the complete process with stepwise instructions to repair MySQL tables. We’ll also explain a reliable MySQL repair tool to repair/restore tables with complete data integrity and minimal downtime.

Causes of Corruption in MySQL Tables

The tables in MySQL can easily get corrupt due to the following factors:

• Any hardware of the system hosting MySQL has failed
• The system suddenly stops working or shuts down
• The MySQL Server stops in the middle of transaction processing
• Bugs in your MySQL code
• Virus or malware infection in your computer in which the MySQL Server/XAMPP server is installed

Methods to Repair MySQL Tables

As we all know, the tables can be classified according to the storage engines. It’s obvious that the repair methods are also specific to the storage engine. If you have a backup (dump) file, then you can easily restore MySQL database by using:

mysqldump utility

If your database gets corrupted or damaged, you can reload the updated dump file to restore MySQL tables with the help of the mysqldump command. To use it, you must have all the roles and privileges required to execute statements in the dump file. To verify if all the privileges and permissions are granted to your account, you can try the SHOW GRANTS command. In the results, check the following permissions:

• CREATE privilege for objects in the statements
• ALTER privilege for the affected database
• SELECT privilege for dumped tables
• RELOAD or FLUSH TABLE privilege.

Steps to use this utility:

• First, you need to drop and recreate the MySQL database.
• Next, use the following the command to restore the database: <code>mysql -u root -p db_name < dump.sql</code>
  This command requires a running MySQL Server. And restoring large-sized database (beyond 10 GB) using it, is a time-consuming process.

phpMyAdmin

phpMyAdmin is also one of the ways to restore a MySQL backup file. You need to log in, select or create the database, then click on the Import tab. Next, upload the .sql dump file with the correct format. Click Go to begin the restoration. Once complete, phpMyAdmin will recreate tables, data, and structure from the dump file.

Workbench GUI

You can even restore a dump/backup file in MySQL Workbench. To do so, you have to locate and open Data Import/Restore wizard.

• In the Workbench and connect to your database server.
• Go to the Navigator panel> Administration> Data Import/Restore.
• Choose the source to select single dump file or the folder with multiple files.
• Next, select the target schema (create one if needed).
• Click Start Import.
• Wait for the process to finish, then check your tables to confirm the data is restored.

Else, try the following repair methods.

1 – Use ALTER TABLE Command

To repair the InnoDB tables, you can try renaming them using the ALTER TABLE command. This command forces the table to create and rebuild the data in it, and also defragments the table, helping in resolving corruption issues. First check whether you have ALTER, CREATE, and INSERT privileges on the table, and then run the below command to rebuild the table:

ALTER TABLE yourtablename ENGINE=INNODB

Alternatively, you can use the dump and reload method to repair the InnoDB tables. This method may require you to use force recovery by enabling the innodb_force_recovery option from the MySQL configuration file.

2 – Use myisamchk Command

To repair the MyISAM tables or its related files like table definition file (frm), MYD data file (.MYD), or index file (.MYI), you can use the myisamchk command to repair the MyISAM tables in MySQL Server. It can help you recover all the data including partitioned tables from the corrupt MyISAM tables. Here’s how to run the myisamchk command:
Note: First, stop the MySQL Server.

myisamchk –recover TABLE

• Start the MySQL Server.

You can even use the REPAIR TABLE command to repair the MyISAM table. Here’s the command:

REPAIR TABLE your table name;

Note: If the table you’re trying to repair is of large size or highly corrupted, the REPAIR TABLE may not provide effective results.

3 – Use a Professional MySQL Repair Tool

If the above methods do not provide the desired outcome, or the database contains indexes or partitioned tables, then the effective method is to choose a professional MySQL repair tool like Stellar Repair for MySQL. It can repair or restore corrupt tables created in InnoDB and MyISAM storage engines. Also, it supports repairing of the partitioned tables, indexes, foreign keys, primary keys, etc., with complete integrity. The tool supports both Linux and Windows operating systems. Some other highlighting features of the tool are:

• Supports repairing of database created in all MySQL versions
• Supports selective recovery of MyISAM tables
• Saves recovered data in multiple file formats, including SQL Script, CSV, and HTML
• Supports MariaDB database up to 11.6
• Allows to save information of scanned MySQL database files to carry on the process later
• Intuitive and user-friendly GUI
• Supports all Windows versions, including Windows 11

How to use Stellar Repair for MySQL to repair MySQL tables?

Note: Stop the MySQL Service and copy the database to another location.

• Download, install, and launch the Stellar Repair for MySQL.
• After this, software displays Select Data Folder prompt. Click Browse option to select the folder and then click OK

• Next, the tool lists every database it found inside the data folder. Select the required database from that list and then click Repair option.
• The repair process starts. Now you can check the preview of the recovered database as shown below:

• Select the objects you want to restore and then click Save on the File menu. However, you need to purchase the License to save the recovered Data
• In the Save Database window, select MySQL and enter the server credentials and click Save.

• Once the saving process completes then verify and restore the database to Workbench.

How to prevent corruption in MySQL tables?

To prevent damage and corruption in MySQL database and reduce data loss in future, make sure:

• You have created an updated backup/dump file and regularly checking it.
• Avoid using kill-9 command and make sure you shut down the MySQL application correctly.
• Keep an eye on your system disk space, memory and CPU, as they can cause sudden system crashes, which further lead to corruption.
• Make sure your Windows and MySQL application are updated.
• Make a habit of checking MySQL error logs regularly to detect corruption early
• You can create or use transaction-safe InnoDB tables as they are less prone to corruption as compare to MyISAM.

Conclusion

You may fail to access the MySQL tables if they are corrupted. To repair the tables according to your MySQL search engine, follow the methods discussed in this article. The highly effective and quick solution is to use MySQL repair tools like Stellar Repair for MySQL to repair MySQL databases with 100% precision. You can easily repair tables created in any version of MySQL Server. Also, it supports InnoDB and MyISAM tables. You can even restore partitioned tables and indexes from the corrupt tables. You can save the repaired database file in different formats.

About the Author

Monika Dadool is a Senior Content Writer at Stellar with 5+ years of experience in technical writing. She specializes in databases, email recovery, Microsoft 365, data recovery, and file repair. Her expertise also covers accounting software, web technologies, and operating systems. Passionate about emerging technologies, she creates practical content for IT professionals and database administrators. Outside work, she enjoys web series, reading, and experimenting with new recipes.