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;