Thursday, 28 May 2015 00:00

A strategy for recovering a corrupted MySQL table

Rate this item
(0 votes)

b2ap3_thumbnail_iStock_000022037685XSmall.jpg

It began one beautiful morningwhen the kernel killed the demon mysqld and mysql_safe had automatically restarted it. All fine and dandy, but the database tables were used by MyISAM. Eventually, I had to resort to myisamcheck but that is another story. During the repairing and checking indices one table got corrupted and it was decided to recover from backups. Just imagine what a relief it was to find out that we do backups daily!

The source data:

• we have a server database with MySQL on board;
• corrupted log (statistics) table of anything, which is constantly filled up and can may not, for example, be used for some period of time;
• daily backup;
• binary logs from the latest daily (full) backup.
The task:

• the server has to be available for work;
• new data have to get into the table;
• to recover the data integrity.

Expected result:

• data from the corrupted table to be recovered without halting the database;
• the table contains all data including the current data.

Notation conventions:

DB-SRV — database server;
ACME_DB — database with ‘lost’ table;
ACME_DB_RECOVERY — database to be recovered, tables or databases;
ACME_DB_INCREMENTAL — databases to be recovered from the binary log;
FAIL_TABLE — corrupted table subject to recovery;
ACME_DB.FAIL_TABLE.BACKUPDATE.sql — the file containing dump of the corrupted table from the latest full backup.


So why have we prepared such a scheme? Because of the DB size and recourse starvation, at the current moment a table-by-table dump is used, and that is why at the moment of backup we cannot tell anything about data integrity.

Instead of a disclaimer:

• The article is written for the purpose of familiarizing the reader with yet another data strategy in the event of table crash. It is highly not recommended to use all ASIS. And of course, you are solely responsible for whatever happens to your data :)


Action plan (RECOVERY):

Make a copy of corrupted table to have a place where to pile up the current data

mysql > CREATE TABLE FAIL_TABLE_NEW LIKE FAIL_TABLE;

If the table has the field AUTO_INCREMENT then make changes to the meter. Increase the value by any number, for example by 1000 from the current value in the table FAIL_TABLE.

mysql> ALTER TABLE FAIL_TABLE_NEW AUTO_INCREMENT = value;

mysql > RENAME FAIL_TABLE TO FAIL_TABLE_OLD, FAIL_TABLE_NEW TO FAIL_TABLE;

Create an auxiliary databases ACME_DB_RECOVERY and ACME_DB_INCREMENTAL

mysql > CREATE DATABASE ACME_DB_RECOVERY; mysql > CREATE DATABASE ACME_DB_INCREMENTAL;

For paranoiacs :) you could make a separate user for each auxiliary database.

mysql > CREATE USER 'recovery'@’localhost' IDENTIFIED BY 'mypass'; mysql > CREATE USER 'increment'@’localhost' IDENTIFIED BY 'mypass'; mysql > GRANTSELECT,INSERT,UPDATE,DELETE,LOCK TABLES,SUPER,INDEX,CREATE ON ACME_DB_RECOVERY TO 'recovery'@'localhost'; mysql > GRANT SELECT,INSERT,UPDATE,DELETE,LOCK TABLES,SUPER,INDEX,CREATE ON ACME_DB_INCREMENTAL TO 'increment'@'localhost'; mysql > FLUSH PRIVILEGES;

Followed by the recovery of corrupted table from the full backup

$ mysql -u recovery -p -h DB-SRV ACME_DB_RECOVERY < ACME_DB.FAIL_TABLE.BACKUPDATE.sql

Then go to datadir of the server and find the files like server_hostname-bin.004324

Find the appropriate binary log starting before the data in backup table and binary log with data before the damage occurred, you can check the contents of binary log by running mysqlbinlog -d ACME_DB.

In order to recovery from the binary log you have to create a full structure of ACME_DB tables

$ mysqldump --no-data -u ACME_USER -p ACME_DB -h DB-SRV | mysql -h DB-SRV -u increment -p ACME_DB_INCREMENTAL

And enter data from binary logs in the order of date and time of creation.

$ mysqlbinlog -d ACME_DB <binary log> | mysql -u increment -p ACME_DB_INCREMENTAL

Then we need to connect dump data and binary logs' data.

Determine the last data in the backup table and from this moment add data from the binary logs table and the original data from the temporary table. Like the following queries:

mysql > USE ACME_DB; mysql > SELECT MIN(id) FROM FAIL_TABLE; # FIRST_ID mysql > SELECT MIN(date) FROM FAIL_TABLE; # FISRT_DATE mysql> USE ACME_DB_RECOVERY; mysql > SELECT MAX(id) FROM FAIL_TABLE; # LAST_ID mysql > SELECT MAX(date) FROM FAIL_TABLE; # LAST_DATE mysql > INSERT INTO FAIL_TABLE (SELECT * FROM ACME_DB_INCREMENTAL.FAIL_TABLE WHERE ID > LAST_ID AND ID < FIRST_ID); #or mysql > INSERT INTO FAIL_TABLE (SELECT * FROM ACME_DB_INCREMENTAL.FAIL_TABLE WHERE DATE >= LAST_DATE AND DATE < FIRST_DATE);

So, now we have recovered the data till the crash occurred and we need to move the data. The example shown below is for the table with the field AUTO_INCREMENT, if you don’t have such, then you can skip the meter part.

Lets check the meter in the table ACME_DB.FAIL_TABLE, then enter a quite higher value for the table ACME_RECOVERY.FAIL_TABLE, it depends on how many entries can be placed in the database, several thousand should be sufficient
mysql > USE ACME_RECOVERY; mysql > ALTER TABLE FAIL_TABLE AUTO_INCREMENT = value; mysql > USE ACME_DB; mysql > RENAME TABLE FAIL_TABLE TO FAIL_TABLE_SMALL, ACME_RECOVERY.FAIL_TABLE TO ACME_DB.FAIL_TABLE; mysql > INSERT INTO FAIL_TABLE (SELECT * FROM FAIL_TABLE_SMALL);

And, of course, deleting temporary data

mysql > DROP DATABASE ACME_DB_INCREMENTAL; mysql > DROP DATABASE ACME_DB_RECOVERY;

Last modified on Thursday, 28 May 2015 16:28
Data Recovery Expert

Viktor S., Ph.D. (Electrical/Computer Engineering), was hired by DataRecoup, the international data recovery corporation, in 2012. Promoted to Engineering Senior Manager in 2010 and then to his current position, as C.I.O. of DataRecoup, in 2014. Responsible for the management of critical, high-priority RAID data recovery cases and the application of his expert, comprehensive knowledge in database data retrieval. He is also responsible for planning and implementing SEO/SEM and other internet-based marketing strategies. Currently, Viktor S., Ph.D., is focusing on the further development and expansion of DataRecoup’s major internet marketing campaign for their already successful proprietary software application “Data Recovery for Windows” (an application which he developed).

Leave a comment

Make sure you enter the (*) required information where indicated. HTML code is not allowed.