MySQL databases can occasionally become corrupted due to unexpected shutdowns, hardware issues, or software bugs. When this happens, tables may become unreadable or throw errors during queries. Fortunately, MySQL provides powerful tools to check and repair corrupted databases and tables, and this can be done efficiently through the command line, whether you're using Linux or Windows.
This guide will walk you through step-by-step instructions to repair MySQL databases via the command line on both operating systems.
Before starting the repair process, ensure the following:
-
You have root access to the system (or administrative rights).
-
The MySQL service is installed and running.
-
You back up your database before performing any repair operation.
Important note: Kindly take the database backup before repairing the MySQL database.
Repair MySQL Database from Windows by Command Line:
Step 1. Log in to your Windows VPS using Remote Desktop. Please refer to how to connect Windows VPS using RDP.
Step 2. Go to Start and locate Command Prompt. Right-click Command Prompt and select the Run as Administrator option.
Step 3. At the Windows command prompt, navigate to the MySQL bin directory as follows.
cd C:\Program Files\MySQL\MySQL Server 8.0\bin.
Replace your MySQL bin directory path here.
Step 4. Run the following command to start the MySQL prompt.
mysql -u root -p
Step 5. Enter the MySQL root password when you are asked.
Step 6. You will see the MySQL prompt appear. Now, to display all databases, type the following command at the MySQL prompt:
show databases;
Step 7. Enter the following command to repair the MySQL database.
mysqlcheck -r [database]
// Replace the database name with your database name.
Repair MySQL Database through Linux Terminal (SSH):
Step 1. Log in to your Linux VPS using Secure Shell (SSH) and connect to MySQL from the command line.
mysql -uUsername -p
Replace Username with your username.
Step 2. Enter the MySQL user password and hit Enter. You will see a MySQL prompt appear.
Step 3. Now, to display all databases, type the following command at the MySQL prompt.
show databases;
Step 4. mysqlcheck enables you to check databases without stopping the entire MySQL service. The -r argument is used to repair the corrupted tables. The mysqlcheck utility efficiently works on both MyISAM and InnoDB database engines. Enter the following command to repair the MySQL database.
mysqlcheck -r [database]
Replace the database name with your database name.
Conclusion:
MySQL corruption issues can be stressful, but they are usually recoverable using built-in tools like mysqlcheck and REPAIR TABLE. Whether you're on Linux or Windows, the command line offers a fast and effective way to detect and fix most table-level problems, especially for MyISAM storage engine tables.
