30

I have previously saved a copy of /var/lib/mysql/ddms directory ("ddms" is the schema name). Now I installed a new MySQL on a freshly installed Ubuntu 10.04.3 LTS by running apt-get install mysql-server, I believe version 5.1 was installed. After I copy the ddms directory under /var/lib/mysql, some of its tables work fine, these are the tables with an associated set of three files: a .frm file, a .MYD file and a .MYI file.

However, there are two tables with a different set of files: a .frm file and a .ibd file. These two tables didn't show up in the table list in phpMyAdmin. When I look at the error log, it says:

[ERROR] Cannot find or open table ddms/dictionary_item from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.

Please help with restoring these two tables. Thanks.

|improve this question

migrated from stackoverflow.com Apr 23 '12 at 2:40

This question came from our site for professional and enthusiast programmers.

29

InnoDB tables cannot be copied the same way that MyISAM tables can.

Just copying the .frm and .ibd files from one location to another is asking for trouble. Copying the .frm and .ibd file of an InnoDB table is only good if and only if you can guarantee that the tablespace id of the .ibd file matches exactly with the tablespace id entry in the metdata of the ibdata1 file.

I wrote two posts in DBA StackExchange about this tablespace id concept

Here is excellent link on how to reattach any .ibd file to ibdata1 in the event of mismatched tablespace ids : http://www.chriscalender.com/?tag=innodb-error-tablespace-id-in-file. After reading this, you should come to the immediate realization that copying .ibd files is just plain crazy.

You could apply the suggestions from the Chris Calendar link, or you could go back to the old installation of mysql, startup up mysql, and then mysqldump the ddms database. Then, import that mysqldump into your new mysql instance. Trust me, this would be far easier.

|improve this answer
  • So a single table might not be a good idea, but what about a whole database at the time? I had my user table crashing bad and I had to do a --initialize on mysqld. Can I copy the whole InoDB database folder from the data_backup folder ? – FMaz008 Sep 7 '17 at 6:37
  • Rolando, just in case you can help me: serverfault.com/q/908988/224334 – Ionică Bizău Apr 23 '18 at 12:28
15

I recently experienced this same issue. Here are the steps I used to solve it without having to mess around with the tablespace id as RolandoMySQLDBA mentions above. I'm on a Mac and so I used MAMP in order to restore the Database to a point where I could export it in a MySQL dump.

You can read the full blog post about it here: http://www.quora.com/Jordan-Ryan/Web-Dev/How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac

You must have:

-ibdata1

-ib_logfile0

-ib_logfile1

-.FRM files from your mysql_database folder

-Fresh installation of MAMP / MAMP Pro that you are willing to destroy (if need be)

  1. SSH into your web server (dev, production, no difference) and browse to your mysql folder (mine was at /var/lib/mysql for a Plesk installation on Linux)
  2. Compress the mysql folder
  3. Download an archive of mysql folder which should contain all mySQL databases, whether MyISAM or innoDB (you can scp this file, or move this to a downloadable directory, if need be)
  4. Install MAMP (Mac, Apache, MySQL, PHP)
  5. Browse to /Applications/MAMP/db/mysql/
  6. Backup /Applications/MAMP/db/mysql to a zip archive (just in case)

  7. Copy in all folders and files included in the archive of the mysql folder from the production server (mt Plesk environment in my case) EXCEPT DO NOT OVERWRITE:

    -/Applications/MAMP/db/mysql/mysql/

    -/Applications/MAMP/db/mysql/mysql_upgrade_info

    -/Applications/MAMP/db/mysql/performance_schema

  8. And voila, you now should be able to access the databases from phpMyAdmin, what a relief!

But we're not done, you now need to perform a mysqldump in order to restore these files to your production environment, and the phpmyadmin interface times out for large databases. Follow the steps here:

http://nickhardeman.com/308/export-import-large-database-using-mamp-with-terminal/

Copied below for reference. Note that on a default MAMP installation, the password is "root".

How to run mysqldump for MAMP using Terminal

EXPORT DATABASE FROM MAMP[1]

Step One: Open a new terminal window

Step Two: Navigate to the MAMP install by entering the following line in terminal cd /applications/MAMP/library/bin Hit the enter key

Step Three: Write the dump command ./mysqldump -u [USERNAME] -p [DATA_BASENAME] > [PATH_TO_FILE] Hit the enter key

Example:

./mysqldump -u root -p wp_database > /Applications/MAMP/htdocs/symposium10_wp/wp_db_onezero.sql

Quick tip: to navigate to a folder quickly you can drag the folder into the terminal window and it will write the location of the folder. It was a great day when someone showed me this.

Step Four: This line of text should appear after you hit enter Enter password: So guess what, type your password, keep in mind that the letters will not appear, but they are there Hit the enter key

Step Five: Check the location of where you stored your file, if it is there, SUCCESS Now you can import the database, which will be outlined next.

Now that you have an export of your mysql database you can import it on the production environment.

|improve this answer
  • 1
    Still working as of 2018. This answer is gold. The important part to me is the #7, those are the files you absolutely got to keep (it's not mentioned anywhere else on similar solutions, thanks for that @jordan). – Bigood May 29 '18 at 0:24
  • @Bigood glad it's still helping! – jordan8037310 Nov 26 '18 at 8:24
12

I have recovered my MySQL 5.5 *.ibd and *.frm files with using MySQL Utilites and MariaDB 10.

1) Generating Create SQLs.
You can get your create sql's from frm file. You must use : https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

shell> mysqlfrm --server=root:pass@localhost:3306 c:\MY\t1.frm --port=3310

Other way you may have your create sql's.

2) Create Your Tables
Create your tables on the database.

3) alter table xxx discard tablespace
Discard your tables which do you want to replace your *.ibd files.

4) Copy your *.ibd files (MySQL Or MariaDB) to MariaDB's data path
First i try to use MySQL 5.5 and 5.6 to restrore, but database crashes and immediately stops about tablespace id broken error. (ERROR 1030 (HY000): Got error -1 from storage engine)
After i have used MariaDB 10.1.8, and i have succesfully recovered my data.

5) alter table xxx import tablespace
When you run this statement, MariaDB warns about file but its not important than to recover your data :) Database still continues and you can see your data.

I hope this information will be helpful for you.

|improve this answer
  • This worked for me. Although mysqlfrm (tried versions 1.3.5 and 1.6.5 with MySQLs 5.6 and 5.7) didn't give correct CREATE definition, even when using MySQL 5.7 (the default ROW_FORMAT changed in MySQL 5.7.9) resulting in Schema mismatch (Expected FSP_SPACE_FLAGS=0x21, .ibd file contains 0x0.) when importing the tablespace. Manually adding ROW_FORMAT=compact at the end of the CREATE statement did the trick. – Jānis Elmeris Jul 26 '17 at 11:30
2

I've had the exact same problem only having the files as backup.

What i did to solve it was to copy the database files into /var/lib/mysql/yourdb and the ibdata1 which is placed in /var/lib/mysql.

I was then able to verify that i could access the tables mysql -u root -p dbname and the querying some of the tables that were previous corrupted.

I made a dump of the database afterward with mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

|improve this answer
1

If you are using MAMP and you cannot get MySQL to start after you copy your files over, I put innodb_force_recovery = 2 inside my.ini and then I was able to get mysql to launch and export my db out.

|improve this answer
0

I've collected posts from the similar topics (Whose answers weren't posted here):

solution 1: https://dba.stackexchange.com/a/59978

solution 2: https://dba.stackexchange.com/a/71785 (+ other post there)

solution 3: recovery kit for tables: https://twindb.com/how-to-recover-innodb-dictionary/

solution 4: Recover MySQL database from data folder without ibdata1 from ibd files

solution 5: using mysqlfrm command

solution 6: https://dba.stackexchange.com/a/159001

solution 7: https://dba.stackexchange.com/a/144573

|improve this answer
  • This looks suspiciously like a link-only answer. – mustaccio Jan 6 '17 at 18:28
  • Because all the links are from our own site, they aren't likely to disappear, so I'm okay with this @mustaccio – jcolebrand♦ Jan 6 '17 at 19:05
  • 2
    This doesn't seem to add much value, given most of these also appear in the "Related" list few pixels to the right. – mustaccio Jan 6 '17 at 19:31
  • @jcolebrand thanks for being light-minded. many people cant see the usefulness even of such "non-direct" answers. They can only detect the breakout of rules. – T.Todua Jan 7 '17 at 14:07
  • No but you must understand he's right. You didn't add any value. I was responding with a rules-judgement, not condoning your answer. It's actually a pretty bad answer. – jcolebrand♦ Jan 7 '17 at 16:18
-3

try running this with mysql utilities 

mysqlfrm --diagnostic /Users//data/tbl_user.frm

command/shell promt

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.