Mysql – Need some clarification of another post’s method for restoring database with frm and ibd files

MySQL

I want to ask a question in this one post at Restoring MySQL Tables from .ibd, .frm and mysqllogbin files but unfortunately my reputation isn't at 50 yet, so the system won't let me ask in that post.

So I ask here:

She figured out how to get her database restored through some trial and error. Her step #2 said:

Go into your command/terminal to open the MySQL utility, mysqlfrm, and
use it to find the structure of your table that you need to restore.
How I did that, was I cd'ed into the file location of mysqlfrm, then
entered "mysqlfrm –server=user:pwd@localhost –port=3307
"path_to_.frm_file" > table_name.txt". The .txt file should be saved
in the same folder as where your utilities are saved if you didn't
specify where you wanted it to go.

My question is this: I assume that I put the path to my frm file in that area she puts "path_to_.frm_file" so I'd do something like this: C:\MySQL_5.6.12\data\wordpress\mysqlfrm but I'm not understanding the "table_name.txt" part. Will I need to put the name of my table in the "table_name" part and am I correct in understanding that this will create a .txt file with my information and it will be saved where I have mysqlfrm and the other MySQL Utilities?

Other question is: she attempted this using WampServer 2.4 and MySQL 5.6.12 on Windows 2003 Server. I'm doing this in WAMP on MySQL 5.6.12 on a laptop running Windows 8. Is her method okay to do on Windows 8 or am I not in the same ballpark. I'd like to know before I would try her method.

Best Answer

table_name.txt is just the name of the file you're creating to save the CREATE statements in. By default, it should go into the same folder as the utilities, but if you'd rather it go somewhere else, you can do path_to_where\you_want_it_saved\table_name.txt and it should go there instead.

Related Solutions

MySQL Replication – Temp Table Still Exists on Slave

I hope I can answer this adequately because I hate temp tables that use InnoDB. Here is why

InnoDB Architecture

In this diagram, the data dictionary has that temp table as an entry. Once the temp table is done being used for ALTER TABLE, the data dictionary entry should disappear.

Here are your questions and my answer for them ...

1.. When alter has been successfully done then why this temp table still exist and not deleted.

Under normal circumstances, when a DB Connection terminates, all open temp tables are supposed to be dropped. The only reason a temp table will linger after an ALTER TABLE is done is if the DB Connection running the ALTER TABLE terminates before the temp table can be dropped. It is possible that the rollback segments (See Diagram) has the original info of the table before the ALTER TABLE was issued.

Since you mentioned this as happening on the Slave, the only suspect (or person of interest) for this problem would be the SQL thread for MySQL Replication. Why? It is the owner of the temp tables need to complete the SQL being executed from the relay logs. If you kill a SQL thread in a manner other than STOP SLAVE; while it is performing an ALTER TABLE, it may not drop temp tables in an orderly manner. Thus, you will have a lingering temp table regardless of the storage engine.

2.. Now how can I delete this temp table and what will be its impact.

You could go ahead and delete the .frm and .ibd. I only say this because it is temp table. The table should have disappeared. The impact ? The data dictionary will still have the temp table registered. Just a few bytes get wasted.

Don't worry about the temp table name being used again. Look at the name of the temp table

#sql-7a87_230c32.ibd

There are 10 hexadecimal digits. The chances of reusing that temp table name is 1 in ₁₆10 or ₂40 which 1,099,511,627,776. I think you will be OK in this.

Don't delete them just yet. Look at the your third question ...

3.. May I read this temp file or know by any command that for which table this temp table was created.

I understand the need for this question. Evidently, you want to know what the table was that was having the ALTER TABLE done. Unfortunately, you cannot read any temp table through standard SQL.

However, you could probably rename the .frm and use a utility to read the table structure.

Here is an old post of mine to do this with a .frm in the Windows environment : How can extract the table schema from just the .frm file?

EPILOGUE

Even though the temp table no longer has a useless purpose at this point, deleting the InnoDB temp table there is that 1 in trillion chance that InnoDB be behave erratically.

Your last resort when this happens is to perform a fully InnoDB Cleanup. See my post Howto: Clean a mysql InnoDB storage engine? in StackOverflow

UPDATE 2014-06-10 08:36 EDT

It should be evident from the file timestamps and the commentary in your second update that a shutdown must have taken place in the middle of an ALTER TABLE. All transactional information for MVCC regarding the InnoDB table was held in a state of suspended animation in one of the following places:

Transaction Logs (a.k.a. Redo Logs, which are the files ib_logfile0 and ib_logfile1),
Rollback Segments & Undo Space (located in the system tablespace file ibdata1)
Double Write Buffer (located in the system tablespace file ibdata1)

When you started mysql back up, the MVCC for the table was replayed to bring the original table into a consistent state. That may result in either the table being rolled back to the original state before the ALTER TABLE or if everything passed through the Redo Logs, the table now existing in the new design. The fact that the .ibd file has a later timestamp show that MVCC must have been applied to it on the startup of mysql (which includes crash recovery).

Once the shutdown happened, the InnoDB table lost its identity as a temp table and became a regular table in the eyes of mysqld. Thus, you should be able to delete the temp files with impunity.

MySQL – How to Restore Tables from .ibd, .frm, and mysqllogbin Files

I finally figured out and resolved my problem through a lot of trial and error. For those who do not have their original ibdata1 file, and only have their .frm and .ibd files, here's how I restored my data.

Download and install the MySQL utilities at -> http://dev.mysql.com/downloads/utilities.
Go into your command/terminal to open the MySQL utility, mysqlfrm, and use it to find the structure of your table that you need to restore. How I did that, was I cd'ed into the file location of mysqlfrm, then entered "mysqlfrm --server=user:pwd@localhost --port=3307 "path_to_.frm_file" > table_name.txt". The .txt file should be saved in the same folder as where your utilities are saved if you didn't specify where you wanted it to go.
In the text file, you'll see the CREATE TABLE statements, that include all the columns and info (basically, the original structure) of your table. Copy that CREATE statement with all that info.
In your MySQL Command, create a new database (CREATE DATABASE database_name). Make it the same name as your original database name.
Create a new table inside the new database--it doesn't have to be the same name as the folder. You can create the new table inside the command prompt, but I created my table in PhpMyAdmin, a free software tool that handles the administration of MySQL over the web. I simply clicked on the database on PhpMyAdmin, then the SQL table, and pasted the table structure from #3. (As a side note, I always received errors if I named my tables "table" in my command prompt, so try to avoid that name).
On your MySQL Command, go into your database, and enter "ALTER TABLE table_name DISCARD TABLESPACE", which will essentially remove this table's .ibd file.
Copy your original table (the table you want to restore)'s .ibd file into the newly-created table to replace the .ibd file that you just removed. Change your initial .ibd file to the newly created table's name. This will mimic the old .ibd file that you just deleted. You can find this folder in the MySQL data folder, under the newly-created database folder on your computer.
Go back to your MySQL Command, go into your database, and enter "ALTER TABLE table_Name IMPORT TABLESPACE." You'll receive a "warning" (1) error-type statement, but just ignore that.
And done! if you try to access your new table, it should contain all the data from your old table.

I hope this helped, and let me know if you have any questions or comments! Also, check out http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file for other details.