Hi, I know there is following article which describes how to restore single table, but it considers table exist and discards the tablespace and then imports it, what if I dropped the table accidentally and need to restore it? possible to be done? how? I don’t know the sheme of table so cant just create it.
Hi @ahmadzadaa ,
A dropped table without backup! hmmm I guess that calls for system level recovery. I’d look out for possible options to recover the specific file from the OS.
Thanks,
K
A dropped table without backup!
I do have full backup taken with xtrabackup, and this is just test env. Point is how I can restore just one table? the link I shared give an example but it only replaces table space.
Ohk what a relief 
So if you have full backup, then you can check this blog to restore single mysql table from Xtrabackup.
Thanks,
K
Hi @ahmadzadaa ,
There is no such tool to give you the DDL of a table at the moment. On 5.7 and previous versions, you could extract that from .frm files as they are now part of DataDictionary (mysql.ibd). You can try to export the Serialized Dictionary Information (SDI) by parsing its JSON output:
$ ibd2sdi --pretty /work/ps/ins/8.0/datadir1/test/table2.ibd | jq '(.[1]) | [.object.dd_object.columns] | (.[]) | (.[]) | (.name, .column_type_utf8)'
"ID"
"int"
"name"
"varchar(50)"
"DB_TRX_ID"
""
"DB_ROLL_PTR"
""
We can see that this table has 2 columns, ID INT and name varchar(50) you can do the same to parse the indexes.
1 Like
@kedarpercona , thank you but at the end I added a condition " I don’t know the sheme of table so cant just create it."
@Marcelo_Altmann can’t this be achieved with something like also taking schema backup during data backup? I wonder if there is already a blog around it.
Hi @ahmadzadaa ,
I admit I misread your previous comment but Marcelo correctly pointed you to the idb2sdi utility.
About logical backups, yes you can take the schema backup:
mysqldump --no-data --all-databases -R -E --triggers --single-transaction > full_schema_backup.sql
Thanks,
K
Hi @ahmadzadaa . Your question on extracting DDL from .ibd comes very often.
So I decided to create a simple parsers of IBD SDI and convert it to DDL.
Have a look GitHub - altmannmarcelo/sdi2ddl: Tool to parse MySQL SDI and convert into DDL .
Please note the tool is in its early stages, if you find any issues like missing data types please raise an issue on github and I will have a look as time allows.
@ahmadzadaa Maybe I didn’t understood your issue very well, but I want to add my 2 cents, hopefully I can help
To my understanding, you already have a xtrabackup with a full backup of the database, but you dropped a table, and don’t remember the database where that table was kept
Why don’t you just look for the name of that table in the data directory that was generated by the xtrabackup tool, in the folder where that table sits, that’s the DB name, you can take then the ibd file and run the sdi2ddl tool that my colleagues referred earlier
Then just create the empty table from the resulting DDL and restore it using the procedure from here