Summary: Table corruption is one of the key reasons for MySQL database corruption and downtime. It takes considerable time to repair MySQL database tables, which prolongs business downtime and causes major inconvenience to the users.
Contents
Thankfully, there are several simple and effective methods to fix corrupted MySQL database, without downtime. Let us explore some of these methods.
MySQL is an open-source RDBMS, which runs on all major operating systems like Windows, UNIX, and Linux. It is mostly used in web applications and online businesses. Many leading platforms like Facebook, YouTube, Twitter, and several other organizations use MySQL database.
But sometimes MySQL database gets corrupted due to underlying database table corruption. This can cause a lot of issues for businesses. Performing MySQL database table repair is an arduous task which needs time and efforts.
However, through this we?re exploring some easy ways to repair MySQL database tables and bring the database back online.
Let us start with the reasons for MySQL table corruption.
Reasons for MySQL Database Table Corruption
MySQL database tables can become corrupted due to one or more of the following reasons:
- Due to problems in the underlying platform used by MySQL to store and retrieve data. It includes controllers, drivers, disk subsystem, firmware, and communication channels.
- Due to abrupt restart or crash, which can cause the database tables to remain stuck in the middle of the transaction.
- Due to program files missing from MySQL directory.
- Use of unverified and incompatible third party software to access the MySQL database
- Use of ?killall -9 MySQLd? commands to shut down the server, which can also kill the ongoing MySQL processes
- Due to virus or malware attack on the system
In addition to understanding the above reasons for MySQL table corruption, it is also important to practice preventive measures to avoid database corruption.
MySQL Table Corruption Prevention tips
Here are some ways to prevent corruption of MySQL database tables:
- It is always advised to test MySQL kernel by using MySQLid command. This command runs in the background and starts the MySQL server on your machine.
- Never use a computer without UPS support. A power outage may interrupt your ongoing operation resulting in database table corruption.
- Always keep an up-to-date backup of your MySQL database. It is recommended to take the database backup at least once in a week.
- Use an anti-virus on your machine to stop viruses and malware from corrupting your database tables.
The above listed tips will help you protect database tables from corruption. In case MySQL database tables still get corrupted, you can use the following methods to repair MySQL database tables without downtime.
Methods to repair corrupt MySQL database tables Without Downtime
Method 1: Repair the table online
Repairing the corrupted database table online can save your users from unwanted database downtime. You can follow the below mentioned steps:
- First, you need to login to the MySQL server with the help of SSH (Secure Shell).
- Go to the command line and type in the below mentioned command providing your actual username and without using brackets
mysql -u [username] -p
- Now provide the password.
- Once you are logged in, type the following command providing your actual database name without using brackets
use [databasename];
- Now type the following command to see a list of all the tables present in your database
show tables;
- One thing you need to note here is that there is no way to repair multiple tables at once using a single command. Hence, you need to use the following commands for every table which is corrupted.
- To check the table for errors, type this command providing your actual table name without using brackets
check table [yourtablename];
- Now to perform the repair procedure, type this command providing your actual table name without using brackets
repair table [yourtablename];
- Enter quit in the command prompt to exit and the table will now be repaired automatically.
Method 2: Repair the table offline
In this method, instead of repairing the table online, you can easily move the files related to the table in another folder and then perform the repair process there.
For instance, in order to repair a table named database.mytable, you can use the commands shown below from the folder /var/lib/mysql:
REPAIR_OPTION=”-r”
DB_NAME=mydb
TABLE_NAME=mytable
FRM=${TABLE_NAME}.frm
MYD=${TABLE_NAME}.MYD
MYI=${TABLE_NAME}.MYI
cd /var/lib/MySQL/${DB_NAME}
mv ${FRM} ..
mv ${MYD} ..
mv ${MYI} ..
cd ..
myisamchk -${REPAIR_OPTION} ${MYD}
myisamchk -${REPAIR_OPTION} ${MYI}
mv ${FRM} /var/lib/MySQL/${DB_NAME}
mv ${MYD} /var/lib/MySQL/${DB_NAME}
mv ${MYI} /var/lib/MySQL/${DB_NAME}
If you find that ?r is not working in the ?REPAIR_OPTION? command you can run the same command using the ?o option.
Method 3: Repairing MyISAM tables with myisamchk
Please note that this method will only work for the tables which are using MyISAM storage engine. You can follow the below steps to repair the corrupted database table.
- In an SSH prompt, type the command: service mysql stop
- Now type the command: cd /var/lib/mysql
- Now you have to change the directory location to where the database is actually located. For example, if the name of the database is mydatabase, then type the following command:
cd mydatabase
- Now type this command providing your actual table name without using brackets
myisamchk [TABLE]
- Now to repair the table, type the following command providing your actual table name without using brackets
myisamchk –recover [TABLE]
- Now restart the MySQL server using the following command
service mysql start
- You can check the repaired tables and they should work fine.
Method 4: Repair the corrupted database with Stellar Repair for MySQL
If none of the above mentioned methods works to repair the tables in question, switch your efforts to a method which will work for sure. Download Stellar Repair for MySQL and fix your database. This advanced MySQL repair software provides an effective solution to fix MySQL database corruption, and restores all inaccessible objects like primary keys, triggers, views, etc. quickly. The tool repairs InnoDB and MyISAM tables, recovers table properties, and performs many other advanced operations.
Conclusion
Don?t let your users suffer from downtime due to critical MySQL database table corruption. Try out one of the manual methods mentioned above, to fix database table swiftly. And if you find the manual methods too difficult or not working, try out Stellar Repair for MySQL to perform MySQL database table repair.
Do let us know your views and queries via posting the comments below.
