MySQL 8 And The FRM Drop… How To Recover Table DDL
Breanna Mangels
If you do have any form of backup, however old, then do you have binary logging turned on via the log-bin option into the MySQL config file (my.ini)? If so they you might be able to recover since last backup.
If innodb_file_per_table is ON the dropped table are on disk partition. Stop MySQL and re-mount it as read-only ASAP. If MySQL was on a root partition (which is not good idea btw) then take an image or take the disk out and plug into another server. Stop all writes in other words.
Here's what I did. In the mysql directory (for Ubuntu this is /var/lib/mysql, for Mac using Homebrew this is /usr/local/var/mysql), I found some files. First I copied the myapp_development/ directory containing the particular schema into my local mysql directory. Then I backed up my local ibdata1 and copied the server's ibdata1 into the mysql directory. Killed mysqld. (ps aux to find the PID, then kill PID). Restarted mysql, it started in crash recovery mode. Then fired up my local mysql client and generated a full dump of the tables I needed.
Depending on the table type you might be able to find an expert who can piece the data back together from what it left on disc but such forensic analysis would be very very very expensive (as it would require relatively uncommon skills) and not at all guaranteed to be truly useful.
Other than that, you can forget about MySQL and are in the same class of problems of "I accidentally deleted some files from my filesystem". There are some tools out there that try to recover files, and there are also companies who do that on a professional basis.
If you have binary logging turned on then you can just recreate a table first if you have schema. Make sure you create schema while you have binlogs turned off. Or you can just skip for the session. Then you can replay the binlogs up until the last statement which was drop table itself.
If not then you can restore using a backup dump if you have any. If you have csv files then you can do load data infile method to recover data. If you are recovering from mysqldump then you may consider restoring a single table from the dump file rather than restoring full database. If data size is too large then you may consider disabling keys before loading this will significantly increase restore process.
Depending on innodb_file_per_table setting the recovery process differs. If innodb_file_per_table is OFF (default up until 5.5) then the dropped table remains in ibdata1. If innodb_file_per_table is ON (default as of 5.5) then the dropped table was in the respective .ibd file. MySQL removes this file when drops the table.
The very first thing to do is to stop any possible writes so your table isn't overwritten. If innodb_file_per_table is OFF it's enough to stop MySQL (kill -9 is even better, but make sure you kill safe_mysqld first). If innodb_file_per_table is ON then umount partition where MySQL stores its data. If the datadir is on the root partition I recommend to shut down server or at least take an image of the disk. Let me repeat, the goal is to prevent overwriting dropped table by MySQL or operating system.
It will scan the file, find the InnoDB pages and sort them by type and index_id. index_id is an identifier that InnoDB uses to refer to an index. A table is stored in index PRIMARY. To find what index_id is your dropped table you need to recover InnoDB dictionary.
Now you can fetch records of the dropped table from InnoDB index_id 376.You need to have the table structure of the dropped table, exactly CREATE TABLE statement which the table was created with. Where you can get it? Either from old backup, or from elsewhere. It's also possible to recover the structure from the InnoDB dictionary, but I won't cover it in this answer. Let's just assume you have it.
Depends on your setup. It is possible to restore if you setup your system correctly. If you have a backup you can restore it. and then apply the binary logs up to the point just before you dropped the table.
I suggest you do this on another server, once you have the table restored you can use mysqldump to extract it and import back to your production server. This will not be a fast restore, but you can recover the data.
If you don't know what you are doing I would suggest openning a support contract with one of the mysql consulting companies (pythian, percona, palamino are probably the best) and have them help you with this.
As you said mysql crashed during a table being truncated so check if that table and its data is available and accessible using select queries, also check .ibd file exist for that table.
if all data is ok then take a dump, delete/move mysql directory, create new mysql structure using mysql_install_db and restore the database.
SQL dump and restore is the safest solution here, though it would indeed be terribly slow for that large data set. If you have the data split over many tables though, you can try with mydumper/myloader tools which are a lot faster then starndard mysqldump. You can find this tool here: [URL] [/URL]
You can also try this: stop mysql, move innodb redo logs to other location and start mysql so that new logs are created. (The InnoDB logs change procedure is described on the bottom of this doc: [url] -data-log-reconfiguration.html[/url])
If I understand correctly, you did drop / recreate that table while innodb-force-recovery mode was active, right? So no more inconsistency in InnoDB table dictionary?
Regarding the LSN in the future messages - those are just warnings as a result of previous InnoDB logs being discarded during recovery mode, and they should stop once the old position will be reached in the future again.
InnoDB is a storage engine of MySQL. It support foreign key (Declarative Referential Integrity). To repair and recover the corrupt table you need a powerful repair tool. MySQL Repair Tool can easily recover any type of corruption in MySQL database. For more in formation:
I had mysql 5.5 in my server windows 2008 R2 which has a data of about 50gb unfortunately i mysql DB has crashed. When i tried to start it shows a error 1067 , server could not be started , stopped unexpectedly. i have tried to reinstall files except the data dircetory but didnt work I have tried to recover by using innodb_force_recovery = 1,3,5,6 .Plz help me how to overcome this situation
If a table is deleted and the GC lifetime is out, the table cannot be recovered with RECOVER TABLE. Execution of RECOVER TABLE in this scenario returns an error like: snapshot is older than GC safe point 2019-07-10 13:45:57 +0800 CST.
The downstream database does not support the RECOVER TABLE statement. An error instance: check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECOVER TABLE table_name'.
This method searches the recent DDL job history and locates the first DDL operation of the DROP TABLE type, and then recovers the deleted table with the name identical to the one table name specified in the RECOVER TABLE statement.
Suppose that you had deleted the table t and created another t, and again you deleted the newly created t. Then, if you want to recover the t deleted in the first place, you must use the method that specifies the DDL JOB ID.
When deleting a table, TiDB only deletes the table metadata, and writes the table data (row data and index data) to be deleted to the mysql.gc_delete_range table. The GC Worker in the TiDB background periodically removes from the mysql.gc_delete_range table the keys that exceed the GC life time.
Therefore, to recover a table, you only need to recover the table metadata and delete the corresponding row record in the mysql.gc_delete_range table before the GC Worker deletes the table data. You can use a snapshot read of TiDB to recover the table metadata. Refer to Read Historical Data for details.
Table recovery is done by TiDB obtaining the table metadata through snapshot read, and then going through the process of table creation similar to CREATE TABLE. Therefore, RECOVER TABLE itself is, in essence, a kind of DDL operation.
To investigate database page corruption, you might dump your tables from the database with SELECT ... INTO OUTFILE. Usually, most of the data obtained in this way is intact. Serious corruption might cause SELECT * FROM tbl_name statements or InnoDB background operations to unexpectedly exit or assert, or even cause InnoDB roll-forward recovery to crash. In such cases, you can use the innodb_force_recovery option to force the InnoDB storage engine to start up while preventing background operations from running, so that you can dump your tables. For example, you can add the following line to the [mysqld] section of your option file before restarting the server:
Only set innodb_force_recovery to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. Before doing so, ensure that you have a backup copy of your database in case you need to recreate it. Values of 4 or greater can permanently corrupt data files. Only use an innodb_force_recovery setting of 4 or greater on a production server instance after you have successfully tested the setting on a separate physical copy of your database. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and only increase the value incrementally, as necessary.
innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery are 1 to 6. A larger value includes the functionality of lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2.
If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
b37509886e