MySQL is the Relational Database Management System that is developed by Oracle and used by corporations whether small or big extensively. It is especially used by web developers to handle their web page content and e-commerce websites to manage the traffic on their pages.
It is open-source in nature, and its feature-rich infrastructure is available for creating responsive web applications and complex B2B data-driven services. Many tech giants like Twitter, Facebook, Wikipedia, and YouTube take the services of MySQL database to manage their applications.
Due to its capability to remain compatible with all platforms, it is used extensively to create a large database and saves data in several file formats like .sql, .cnf, .arm, .qbquery, .ddl, etc.
There are different chances that a database file may become corrupt due to malware or spyware corruption. So, Oracle has given a detailed description of how the corrupt database can be repaired and useful to save new data again. First, you should check that how the database starts to show the anomaly-
- The database is downgraded automatically.
- There are unwanted changes to the indexes of data.
- A database table is showing inconsistencies.
Depending on the database state, there can be multiple troubleshooting methods for repairing it-
Check the table for errors
When MySQL is running, then you can run a query inside the table –
Note: Depending on the type of table, there CHECK TABLE command will run separate checks on them.
- For MyISAM tables, the CHECK TABLE will run a MEDIUM CHECK.
- For InnoDB tables, the CHECK TABLE will run a FOR UPGRADE and QUICK scan.
After running the query, the status of the table will be shown in several columns like Table, Op, Msg_type, and Msg_text
If Msg_type shows the status as ‘error’ and Msg_text shows ‘corrupt’, then you should run the next query to repair the table.
After repairing the table, the Msg_type will show an OK status.
Note: If the MySQL server is shut down during the repair process, then do not worry. It will resume after restarting the server. But, you should make sure that you are not running any additional queries on the same table.
Check using mysqlcheck
For users in the Linux terminal, they can run mysqlcheck to repair the database. First, change the directory level to the location of the database file.
- The root user should change the directory to the location where the database is saved.
Sudo su
Cd /var/lib/mysql - Check the entire database file
Mysqlcheck <database-name>
You can also check a table inside the database file
After checking the database for corruption, you can start the repair process for the database. But, first, you should take the backup of the database.
The above command will create the same file with a different name. So, you should save the new file in a different drive or folder.
Repair the database
In an active database, you can start the repair process by writing a query.
The query will reset the database and repair the complete database. Then, its status will be OK.
Repair using mysqlcheck
- In the Linux terminal, you should change the directory to the location of the database file.
cd /var/lib/mysql
- Run mysqlcheck scan with –r parameter. It will run the repair process for the database.
mysqlcheck -r <database-name> <database-table-name>
The command will repair the table of database cleanly.
Conclusion
The tutorial has given the simple methods to scan corrupt Mysql databases like MyISAM and InnoDB. You can scan and later repair the database file, but the methods have already asked you to take a backup of the database as it may become more corrupt or lose already present data.
The backup file taken from a corrupt database may contain traces of corruption from malware or spyware. So, the manual recovery method is not a suitable choice for a thorough scan and recovery of the Mysql database.
A professional Mysql database recovery software is the powerful alternate that can scan a corrupt database and recover all tables. The relationships and dependencies between the tables will be maintained. Some important properties like primary keys, foreign keys, indexes, triggers, and other parts will recover from corruption and become useful again. After recovering the database, you can connect it with the server and run further queries.
