How to Repair MySQL Database from Command Line?

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.

repairing mysql database from command line

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.

Select database to be repaired

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.

Preview recoverable database objects

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.

File saving option

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.

Specify repaired database file saving options

Figure 6 – MySQL Database File Saving Options

The repaired database will be saved in the connected MySQL server instance.

Conclusion

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.


Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.