When the MySQL database gets damaged or corrupted, the first option is to look for the backup database file. If you’ve a latest healthy backup, then you can easily restore database from backup file. However, it might happen that the backup file is outdated, not available, or even corrupted.
In such a case, you need to look for solutions to repair the MySQL database without any data loss and within minimum time. In this article, we’ll discuss some easy and effective methods to repair MySQL database without the backup file. But before moving to the solutions, let’s first understand the causes behind MySQL database corruption.
Causes of Corruption in MySQL Database
MySQL databases may get corrupted due to one or more of the following reasons:
- Abnormal System Shutdown: The database may get corrupted if the system is terminated abnormally due to power outage or any other reason.
- Hardware Fault: Hardware issues occurred in the system hosting the MySQL Server and database files.
- Storage Issues: The disk where MySQL database is stored is out of storage space.
- Virus Infection: The system hosting the MySQL Server database is affected by virus.
- Server Shutdown or Crash: Sudden closing or termination of MySQL Server can also corrupt the database.
Methods to Repair and Recover MySQL Database without Backup
Here are some easy and effective methods to repair and recover corrupt MySQL database.
1 – Use MySQL Binary Log to Recover the Database
Note: This method will work, if the binary log option was enabled beforehand.
If you don’t have a backup, then you can use the MySQL binary log for manual recovery of the MySQL database. MySQL generates binary log files in the data directory. The binary log file records database changes and transactions. You can check the listing of all the binary log files by using the below statement:
mysql> SHOW BINARY LOGS;
To check the current binary log file, use the below statement:
mysql> SHOW BINARY LOG STATUS;
Once the current binary log file status is displayed, you can use the mysqlbinlog utility to convert the complex binary log files data into human-readable text. Then, you can use the log file to recover the affected changes in MySQL database.
If this method didn’t help, then follow the next methods to repair the MySQL database.
2 – Use the mysqlcheck Command to Repair the Database
In MySQL, the mysqlcheck command checks, repairs, optimizes, and analyzes the database tables. You can use this command to repair the MySQL database tables. Before repairing, use the CHECK TABLE command to check the tables. If it finds any issues with the table, you can then repair that table. Here’s how to do so:
- Open the command-line terminal on the system hosting the MySQL server.
- Run the CHECK TABLE command as given below to check the table in database:
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option: {
FOR UPGRADE
| QUICK
| FAST
| MEDIUM
| EXTENDED
| CHANGED
}- If it shows any error, then execute the mysqlcheck command with the –repair option (as given below) to repair the table:
mysqlcheck --repair database_name table_name
3 – Use the REPAIR TABLE Command
You can also use the REPAIR TABLE command to repair the damaged/corrupted table in MySQL database. Before running this command, make sure you have the SELECT and INSERT privileges for the table. To check these privileges, you can use the SHOW GRANTS statement. It will show all the privileges assigned to the user profile. If you have the desired permissions, run the REPAIR TABLE query as given below:
REPAIR TABLE table name;
4 – Use Myisamchk Command
You can also use the myisamchk command to repair the tables. It can repair MyISAM tables with .MYI and .MYD extensions. First, stop the MySQL Server and then run the following command:
myisamchk –recover TABLE
After repair, restart the MySQL Server.
Note: The Myisamchk command does not support InnoDB tables. It is specifically designed to check and repair MyISAM tables in MySQL. You can use the “Dump and Reload” method to repair InnoDB table.
5 – Use a Professional MySQL Database Repair Tool
If the above repair methods fail, you can repair the MySQL database using an advanced third-party MySQL database repair tool. Stellar Repair for MySQL is one such tool that can help you repair damaged or corrupt MySQL database, with complete integrity and precision. It can repair tables created in InnoDB/MyISAM storage engine. After repairing, you can save either specific elements or the entire database. You can save the repaired data in MySQL, MariaDB, HTML, CSV, SQL Script, CSV, and XLS formats.
Some prominent features of Stellar Repair for MySQL are:
- Repairs corrupt tables created in InnoDB and MyISAM storage engine.
- Repairs severely corrupted MySQL database with complete integrity.
- Saves repaired MySQL database data in a new database and other formats, like MariaDB, SQL Script, and HTML.
- Repairs database created in MySQL and MariaDB servers.
- Repairs corrupt partitioned tables in MySQL/MariaDB.
- Offers an option to preview the repaired objects.
- Supports Windows and Linux operating systems.
- Supports MySQL version 8.x, 6.x, 5.x, 4.x, and 3
Closure
Above, we have explained the methods to repair corrupt MySQL database without the backup file. You can use the mysqlcheck command, REPAIR TABLE command, or Myisamchk command to repair the corrupt MySQL database tables. If these native repair options fail, then you can use an advanced MySQL database recovery software, such as Stellar Repair for MySQL. It can help you quickly repair corrupt MySQL database. It can restore all the database objects with complete integrity. You can install the demo version of the tool to scan the corrupt database file and preview the recoverable data.
