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).

47 comments

  • Comment Link Randi Saturday, 31 March 2018 13:51 posted by Randi

    Canine are social animals, and love company.

  • Comment Link Priscilla Thursday, 08 March 2018 19:05 posted by Priscilla

    If some one needs expert view about blogging and site-building afterward
    i advise him/her to pay a visit this blog, Keep up the nice work.

  • Comment Link Freya Thursday, 08 March 2018 15:47 posted by Freya

    I know this if off topic but I'm looking into starting my own blog and was wondering
    what all is needed to get setup? I'm assuming having
    a blog like yours would cost a pretty penny? I'm not very web savvy so I'm not 100% certain. Any recommendations or advice would be greatly appreciated.
    Kudos

  • Comment Link Kimberly Tuesday, 06 March 2018 22:38 posted by Kimberly

    We didn't discover outcomes for: stair builders.

  • Comment Link Flora Saturday, 03 March 2018 23:30 posted by Flora

    I provide feline and canine pleasant providers.

  • Comment Link Angelica Wednesday, 28 February 2018 07:47 posted by Angelica

    I like it when people get together and share views.
    Great site, keep it up!

  • Comment Link Errol Saturday, 17 February 2018 01:22 posted by Errol

    Most popular tow truck service in Victoria, Texas.

  • Comment Link Mariana Saturday, 17 February 2018 01:15 posted by Mariana

    Tow Truck & Wrecker Towing Service Jersey Village TX.

  • Comment Link Henry Friday, 09 February 2018 21:20 posted by Henry

    What i do not understood is in fact how you're
    now not really much more smartly-preferred than you may be right now.
    You're so intelligent. You already know therefore significantly with
    regards to this topic, made me in my opinion believe it from so many various angles.
    Its like men and women aren't fascinated until it's
    one thing to accomplish with Lady gaga! Your own stuffs outstanding.

    All the time maintain it up!

  • Comment Link Jacklyn Friday, 09 February 2018 14:03 posted by Jacklyn

    Today, I went to the beach with my kids. I found a sea shell and gave it to my 4
    year old daughter and said "You can hear the ocean if you put this to your ear." She placed
    the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear.
    She never wants to go back! LoL I know this is entirely off
    topic but I had to tell someone!

Leave a comment

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