Summary: This blog outlines some common reasons leading to MySQL database corruption and explains how to repair MySQL database from the command line. The blog also helps explore how using a third-party MySQL repair software, such as Stellar Repair for MySQL can help you repair and restore the database with no or minimal downtime.
MySQL database can become corrupt due to several reasons. Often corruption may occur due to hardware failure, particularly disk-based failure. Database corruption can also happen in the event of MySQL Server reset following a crash, or when there are bugs in the MySQL software. Also, a crashed data file or index file containing corrupt data can damage the MySQL db. Symptoms of a corrupted MySQL database may include failed MySQL login attempts or MySQL server crashing.
Restoring the database from the most up to date backup is the best approach to restore MySQL database to its original form. If the backup is not updated, or the backup is there, but you cannot restore it as there has been a lot of activity on the database, you can still take some actions to attempt recovery. You can try to fix the corrupt MySQL database from the command line.
Steps to Repair MySQL Database from Command Line
Step 1: Click Start on Windows and search for Command Prompt. Right-click on the Command Prompt and click the Run as Administrator option.
Step 2: In the command line window, browse the MySQL bin directory path using the following command:
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
Note: Replace the path with your MySQL bin directory path in the above command.
Step 3: Repair database in MySQL by entering the below command:
mysqlcheck -r [database] -u root –p
IMPORTANT! Sometimes running the above repair command yields an error note “the storage engine for the table doesn’t support repair” (refer to the image below). This means you are running an InnoDB table, though it is a rare scenario when that happens you will need to repair InnoDB table corruption.
Figure 1 – Repairing MySQL Database from Command Line
Use Stellar Repair for MySQL to Repair Corrupt Database
If repairing the MySQL db from the command line does not work or returns unsatisfactory results, use Stellar Repair for MySQL software to repair the database without downtime. The specialized MySQL repair software from Stellar® is purpose-built to fix corruption in MySQL database engines InnoDB as well as MyISAM. It also performs recovery of all the database objects, including tables, table properties, keys, views, data types, etc.
The software helps repair MySQL database on both Windows and Linux systems.
Steps to Repair MySQL Database with Stellar Repair for MySQL Software
Once you have downloaded and installed the software, launch it. The main interface of the software opens with an instruction window. Click OK to exit the instruction window and proceed with the following steps to perform the MySQL database repair process:
Step 1: On the Select Data Folder window, select MySQL version of the database that you want to repair. Next, browse the database file to be repaired.
Figure 2 – Select MySQL Version
Note: You must have MySQL 8.0 ODBC driver 32-bit installed on your system to save MySQL database version 8.
Step 2: When the Browse for Folder dialog box is displayed, choose the folder containing database(s) you want to repair, and then hit the OK button.
Step 3: On Select Database dialog box, choose a specific or all databases that you want to repair, and then click Repair.
Figure 3 – Select Database(s) to be Repaired
Step 4: A preview window with recoverable MySQL database objects is displayed. All the repaired databases, along with recoverable tables, gets displayed on the left side of the preview window. Click on each item to view its content on the right pane.
Figure 4 – Repaired Database Preview
Step 5: After selecting the data you want to recover, click on the Save button on File menu for saving the data.
Figure 5 – Select the Save Option
Step 6: On the Save Database window, select MySQL under Save As, and then enter the connection parameters to export the restored data directly on a running MySQL server instance. Click Save.
Figure 6 – MySQL Database File Saving Options
The repaired database will be saved in the connected MySQL server instance.
When MySQL database gets corrupted, you can try repairing it manually from the command line following the steps discussed in this blog. However, the manual process may fail to repair severely corrupt database or may result in database consistency errors. Use Stellar Repair for MySQL software to retain access to your database and all its data without prolonged downtime. You can also verify the software accuracy by previewing the recoverable objects using the software demo version.
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.