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 article 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.
Was this article helpful?
