Recovery strategy for damaged table in MySQL

It all started with the fact that at one point the kernel killed the mysqld daemon and mysql_safe with the machine restarted and everything would be fine, but only the tables in the database were used by MyISAM. In the end, I had to use myisamcheck, but this is another story. In the process of checking and repairing indexes, one table suffered and it was decided to restore from backups, it’s good that they are done once a day.



Initial data:

• we have a database server with MySQL on board;
• a damaged table of logs (statistics) of anything that is constantly being filled in and may for example not be used for some time;
• daily backup;
• binary logs from the last daily (full) backup.

Task:

• the server must be available for work;
• new data should fall into the table;
• restore data integrity.

Expected Result:

the data in the damaged table was restored without stopping the melon database;

the table contains all the data including the current.



Legend:

• DB-SRV - North with database;
• ACME_DB - the database in which the “lost” table;
• ACME_DB_RECOVERY — recovery database, table, or database;
• ACME_DB_INCREMENTAL - database for recovery from binary log;
• FAIL_TABLE - damaged table to be restored;
• ACME_DB.FAIL_TABLE.BACKUPDATE.sql - a file with a dump of the damaged table from the last full backup.

For which such a garden was proud. And all because of the size of the database and the lack of resources, a tabular dump is currently being used, so there is no talk about data integrity at the time of backup.

')

Instead of a disclaimer:

• The article was written with the aim of introducing another data recovery strategy when a table falls. Using all ASIS is strictly not recommended. Well, you yourself are responsible for all that happens with your data :)

Action Plan (Recovery):



Make a copy of the damaged table so that the current data was where to put



mysql > CREATE TABLE FAIL_TABLE_NEW LIKE FAIL_TABLE;



If the table with the field AUTO_INCREMENT, then we make more changes to the counter. We increase the counter value by a little, for example, 1000 from the current value in the FAIL_TABLE 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 auxiliary databases ACME_DB_RECOVERY and ACME_DB_INCREMENTAL



mysql > CREATE DATABASE ACME_DB_RECOVERY;

mysql > CREATE DATABASE ACME_DB_INCREMENTAL;



For paranoids :) you can do for each auxiliary base by user.



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;



We recover the damaged table from a full backup



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



Next we go to the datadir server and find files like server_hostname-bin.004324



We find a suitable binlog that starts earlier than the data in the table from backup and binlog with data until the moment of damage, to see what can be done in the binary log using the command mysqlbin -d ACME_DB.



In order to recover from binlog, you need to create a complete ACME_DB table structure.



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



And we enter data from binary logs in the order of their creation.



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



Next we need to connect the data from the dump and binary logs.



We define the latest data in the table from backup, from this “moment” we will add data from the table recovered from binary logs and the first data from the temporary table. For example, here are such 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);

#

mysql > INSERT INTO FAIL_TABLE (SELECT * FROM ACME_DB_INCREMENTAL.FAIL_TABLE WHERE DATE >= LAST_DATE AND DATE < FIRST_DATE);





So, the data was restored until the moment of the fall, now we are transferring the data for small, an example will be for a table with an AUTO_INCREMENT field, if there is no such thing, you can skip the place with the installation of the counter.



We look at the current counter in the ACME_DB.FAIL_TABLE table, set the counter in the ACME_RECOVERY.FAIL_TABLE table a bit more, it all depends on how many records you have inserted into the database, a few thousand.

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



Delete all temporary data



mysql > DROP DATABASE ACME_DB_INCREMENTAL;

mysql > DROP DATABASE ACME_DB_RECOVERY;



update:

zhirafovod prompted that the binoglog of one database can get data on other changes, for example update ACME2.TABLE ...

To avoid changes, it is necessary to set privileges for new databases and specially created for them users.