How to Ward off Table Corruption Problems in MySQL

Database corruption is a problem that may be lurking around the corner and will only come to your notice once you hit the corrupt block. As any other database, corruption may hit your MySQL databases making millions of corporate data records inaccessible. Finding corruption in MySQL tables is painstaking. You may expect queries to stop working or showing inconsistent outcome. When you start experiencing such issues, you need to check your tables for consistency. In the event of MySQL database corruption, the below given errors may show up intermittently on your screen:

'Record was already deleted (or record file crashed)'

Or

'Tables was marked as crashed and should be repaired'

MySQL provides two inbuilt utilities to check and repair corrupt tables:

I. 'myisamchk'

'myisamchl' is a command-line utility to verify, optimize, and repair MyISAM tables. For checking tables using this command, you need to specify the full table name along with the extension as follows:

$ myisamchk /usr/local/mysql/data/db2/payroll.myi

In case the tool finds errors with the table, you need to go for a repair. Before starting the repair, you need to back up your table file and shut down MySQL server using mysqladmin SHUTDOWN command. To perform a table repair, use 'myisamchk' with the '--recover' option as shown below:

$ myisamchk --recover /usr/local/mysql/data/db2/payroll.myi

The command removes all the invalid or previously deleted records from your table and then rebuilds the table index. If you fail in this attempt, use the '--safe--recover' option. It explicitly scans all records one by one. This process takes significant time and is slower than normal repair operation.

II. CHECK TABLE and REPAIR TABLE

These commands require the server to be running and are executed from a MySQL prompt. In order to prevent table modifications by other users, you can shut down and restart mysqld using '--skip-networking' option.

To check a table named 'Employee' using CHECK TABLE, run the following command at the MySQL prompt:

'mysql> CHECK TABLE Employee'

You can add 'FAST', 'MEDIUM', or 'EXTENDED' options after the command to perform a quick check, check by calculating checksum for indexes, or to check by verifying data in each table record.

For repairing a table using REPAIR TABLE, run the following:

'mysql> REPAIR TABLE Emplyee'

If the above inbuilt utilities fail to fix MySQL table corruption, you should take help of professional MySQL recovery software. These advanced tools effectively repair corrupt InnoDB tables and MyISAM tables of your MySQL database. Using these software, you can safely restore all inaccessible database objects, including tables, keys, indexes, etc.