restore table from .frm and .ibd file?How to recover .ibd and .frm files in MySQLRestroring MYSQL databse...

PTIJ: Is all laundering forbidden during the 9 days?

What is better: yes / no radio, or simple checkbox?

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

PTIJ: Mordechai mourning

How to get the first element while continue streaming?

Split a number into equal parts given the number of parts

How do I deal with being envious of my own players?

Quitting employee has privileged access to critical information

What can I do if someone tampers with my SSH public key?

Where is this quote about overcoming the impossible said in "Interstellar"?

How can neutral atoms have exactly zero electric field when there is a difference in the positions of the charges?

Misplaced tyre lever - alternatives?

Giving a talk in my old university, how prominently should I tell students my salary?

How does signal strength relate to bandwidth?

Can a space-faring robot still function over a billion years?

Should I use HTTPS on a domain that will only be used for redirection?

I encountered my boss during an on-site interview at another company. Should I bring it up when seeing him next time?

How does insurance birth control work?

Relationship between the symmetry number of a molecule as used in rotational spectroscopy and point group

If nine coins are tossed, what is the probability that the number of heads is even?

PTIJ: Why can't I sing about soda on certain days?

Draw bounding region by list of points

It doesn't matter the side you see it

How to kill a localhost:8080



restore table from .frm and .ibd file?


How to recover .ibd and .frm files in MySQLRestroring MYSQL databse from .frm and .ibd files (database does not exist error)Restoring MySQL Tables from .ibd, .frm and mysqllogbin filesRecover MySQL database from data folder without ibdata1 from ibd filesHow to Recover an InnoDB table whose files were moved aroundHow can extract the table schema from just the .frm file?ERROR Cannot find or open table?Table compression in InnoDB?Is it possible to restore a MySql database only using files from Data directory?Cannot access MySQL database after moving files in data directoryHow to recover MySQL table structure from FRM filesError When Restoring Backed Up TablesCopying MySQL tables, InnoDB tables not recognizedMySQL backup breakes a table every timeRestore MySQL Database with only .frm filesMySQL crashed, InnoDB fails to recoverTrying to recover a database with just .ibd and .frm files, but no idbata1 fileRestroring MYSQL databse from .frm and .ibd files (database does not exist error)Restoring a WordPress site from WAMP with only .ibd and .frm files, into a new WAMP with … .myd and .myi files?Migrating MySQL from Windows to Linux













31















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.










share|improve this question















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


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























    31















    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.










    share|improve this question















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


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





















      31












      31








      31


      18






      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.










      share|improve this question
















      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.







      mysql innodb






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Jan 6 '17 at 20:48









      T.Todua

      134111




      134111










      asked Apr 22 '12 at 3:59









      Tong WangTong Wang

      258135




      258135




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


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









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


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
























          8 Answers
          8






          active

          oldest

          votes


















          30














          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





          • Table compression in InnoDB? (under the heading 'Restoring Databases')

          • How to Recover an InnoDB table whose files were moved around


          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.






          share|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



















          16














          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.






          share|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



















          13














          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:MYt1.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.






          share|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



















          3














          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






          share|improve this answer































            2














            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.






            share|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






              share|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



















              0














              If you're able to restore the the *.ibd file to the original MySQL server, don't forget to restore the file access rights too.
              In my case (MySQL8 on CentOS7), I restored file to /var/lib/mysql/db/tablename.ibd and run:



              chown mysql tablename.ibd
              chgrp mysql tablename.ibd
              chmod 0640 tablename.ibd


              Before fixing access rights, accessing the table resulted in error "2006 MySQL server has gone away".
              After fixing access rights, the table worked (even without restart of mysqld service).





              share








              New contributor




              Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.




























                -3














                try running this with mysql utilities



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


                command/shell promt






                share|improve this answer


























                • ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                  – Kwaadjei
                  Feb 16 '17 at 9:42











                Your Answer








                StackExchange.ready(function() {
                var channelOptions = {
                tags: "".split(" "),
                id: "182"
                };
                initTagRenderer("".split(" "), "".split(" "), channelOptions);

                StackExchange.using("externalEditor", function() {
                // Have to fire editor after snippets, if snippets enabled
                if (StackExchange.settings.snippets.snippetsEnabled) {
                StackExchange.using("snippets", function() {
                createEditor();
                });
                }
                else {
                createEditor();
                }
                });

                function createEditor() {
                StackExchange.prepareEditor({
                heartbeatType: 'answer',
                autoActivateHeartbeat: false,
                convertImagesToLinks: false,
                noModals: true,
                showLowRepImageUploadWarning: true,
                reputationToPostImages: null,
                bindNavPrevention: true,
                postfix: "",
                imageUploader: {
                brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                allowUrls: true
                },
                onDemand: true,
                discardSelector: ".discard-answer"
                ,immediatelyShowMarkdownHelp:true
                });


                }
                });














                draft saved

                draft discarded


















                StackExchange.ready(
                function () {
                StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f16875%2frestore-table-from-frm-and-ibd-file%23new-answer', 'question_page');
                }
                );

                Post as a guest















                Required, but never shown

























                8 Answers
                8






                active

                oldest

                votes








                8 Answers
                8






                active

                oldest

                votes









                active

                oldest

                votes






                active

                oldest

                votes









                30














                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





                • Table compression in InnoDB? (under the heading 'Restoring Databases')

                • How to Recover an InnoDB table whose files were moved around


                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.






                share|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
















                30














                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





                • Table compression in InnoDB? (under the heading 'Restoring Databases')

                • How to Recover an InnoDB table whose files were moved around


                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.






                share|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














                30












                30








                30







                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





                • Table compression in InnoDB? (under the heading 'Restoring Databases')

                • How to Recover an InnoDB table whose files were moved around


                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.






                share|improve this answer















                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





                • Table compression in InnoDB? (under the heading 'Restoring Databases')

                • How to Recover an InnoDB table whose files were moved around


                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.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Jan 30 at 15:20









                Phillip

                53




                53










                answered Apr 23 '12 at 19:33









                RolandoMySQLDBARolandoMySQLDBA

                142k24223379




                142k24223379













                • 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



















                • 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

















                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





                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





                Rolando, just in case you can help me: serverfault.com/q/908988/224334

                – Ionică Bizău
                Apr 23 '18 at 12:28













                16














                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.






                share|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
















                16














                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.






                share|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














                16












                16








                16







                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.






                share|improve this answer













                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.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered May 23 '13 at 17:18









                jordan8037310jordan8037310

                16113




                16113








                • 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














                • 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








                1




                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





                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





                @Bigood glad it's still helping!

                – jordan8037310
                Nov 26 '18 at 8:24











                13














                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:MYt1.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.






                share|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
















                13














                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:MYt1.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.






                share|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














                13












                13








                13







                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:MYt1.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.






                share|improve this answer















                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:MYt1.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.







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Jun 11 '18 at 9:46

























                answered Jan 30 '16 at 17:02









                EcdEcd

                23123




                23123













                • 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



















                • 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

















                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





                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











                3














                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






                share|improve this answer




























                  3














                  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






                  share|improve this answer


























                    3












                    3








                    3







                    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






                    share|improve this answer













                    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







                    share|improve this answer












                    share|improve this answer



                    share|improve this answer










                    answered Aug 24 '14 at 19:38









                    TueTue

                    311




                    311























                        2














                        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.






                        share|improve this answer






























                          2














                          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.






                          share|improve this answer




























                            2












                            2








                            2







                            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.






                            share|improve this answer















                            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.







                            share|improve this answer














                            share|improve this answer



                            share|improve this answer








                            edited Jan 9 '17 at 16:44









                            T.Todua

                            134111




                            134111










                            answered Oct 5 '16 at 5:28









                            Peter WakemanPeter Wakeman

                            211




                            211























                                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






                                share|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
















                                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






                                share|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














                                0












                                0








                                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






                                share|improve this answer















                                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







                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Apr 13 '17 at 12:42









                                Community

                                1




                                1










                                answered Jan 6 '17 at 17:47









                                T.ToduaT.Todua

                                134111




                                134111













                                • 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



















                                • 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

















                                This looks suspiciously like a link-only answer.

                                – mustaccio
                                Jan 6 '17 at 18:28





                                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





                                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




                                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





                                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







                                @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





                                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











                                0














                                If you're able to restore the the *.ibd file to the original MySQL server, don't forget to restore the file access rights too.
                                In my case (MySQL8 on CentOS7), I restored file to /var/lib/mysql/db/tablename.ibd and run:



                                chown mysql tablename.ibd
                                chgrp mysql tablename.ibd
                                chmod 0640 tablename.ibd


                                Before fixing access rights, accessing the table resulted in error "2006 MySQL server has gone away".
                                After fixing access rights, the table worked (even without restart of mysqld service).





                                share








                                New contributor




                                Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                Check out our Code of Conduct.

























                                  0














                                  If you're able to restore the the *.ibd file to the original MySQL server, don't forget to restore the file access rights too.
                                  In my case (MySQL8 on CentOS7), I restored file to /var/lib/mysql/db/tablename.ibd and run:



                                  chown mysql tablename.ibd
                                  chgrp mysql tablename.ibd
                                  chmod 0640 tablename.ibd


                                  Before fixing access rights, accessing the table resulted in error "2006 MySQL server has gone away".
                                  After fixing access rights, the table worked (even without restart of mysqld service).





                                  share








                                  New contributor




                                  Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                  Check out our Code of Conduct.























                                    0












                                    0








                                    0







                                    If you're able to restore the the *.ibd file to the original MySQL server, don't forget to restore the file access rights too.
                                    In my case (MySQL8 on CentOS7), I restored file to /var/lib/mysql/db/tablename.ibd and run:



                                    chown mysql tablename.ibd
                                    chgrp mysql tablename.ibd
                                    chmod 0640 tablename.ibd


                                    Before fixing access rights, accessing the table resulted in error "2006 MySQL server has gone away".
                                    After fixing access rights, the table worked (even without restart of mysqld service).





                                    share








                                    New contributor




                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.










                                    If you're able to restore the the *.ibd file to the original MySQL server, don't forget to restore the file access rights too.
                                    In my case (MySQL8 on CentOS7), I restored file to /var/lib/mysql/db/tablename.ibd and run:



                                    chown mysql tablename.ibd
                                    chgrp mysql tablename.ibd
                                    chmod 0640 tablename.ibd


                                    Before fixing access rights, accessing the table resulted in error "2006 MySQL server has gone away".
                                    After fixing access rights, the table worked (even without restart of mysqld service).






                                    share








                                    New contributor




                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.








                                    share


                                    share






                                    New contributor




                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.









                                    answered 8 mins ago









                                    SamurajSamuraj

                                    1




                                    1




                                    New contributor




                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.





                                    New contributor





                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.






                                    Samuraj is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                    Check out our Code of Conduct.























                                        -3














                                        try running this with mysql utilities



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


                                        command/shell promt






                                        share|improve this answer


























                                        • ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                          – Kwaadjei
                                          Feb 16 '17 at 9:42
















                                        -3














                                        try running this with mysql utilities



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


                                        command/shell promt






                                        share|improve this answer


























                                        • ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                          – Kwaadjei
                                          Feb 16 '17 at 9:42














                                        -3












                                        -3








                                        -3







                                        try running this with mysql utilities



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


                                        command/shell promt






                                        share|improve this answer















                                        try running this with mysql utilities



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


                                        command/shell promt







                                        share|improve this answer














                                        share|improve this answer



                                        share|improve this answer








                                        edited Feb 16 '17 at 10:23









                                        Marco

                                        3,74231724




                                        3,74231724










                                        answered Feb 16 '17 at 9:40









                                        KwaadjeiKwaadjei

                                        1




                                        1













                                        • ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                          – Kwaadjei
                                          Feb 16 '17 at 9:42



















                                        • ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                          – Kwaadjei
                                          Feb 16 '17 at 9:42

















                                        ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                        – Kwaadjei
                                        Feb 16 '17 at 9:42





                                        ref: dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html

                                        – Kwaadjei
                                        Feb 16 '17 at 9:42


















                                        draft saved

                                        draft discarded




















































                                        Thanks for contributing an answer to Database Administrators Stack Exchange!


                                        • Please be sure to answer the question. Provide details and share your research!

                                        But avoid



                                        • Asking for help, clarification, or responding to other answers.

                                        • Making statements based on opinion; back them up with references or personal experience.


                                        To learn more, see our tips on writing great answers.




                                        draft saved


                                        draft discarded














                                        StackExchange.ready(
                                        function () {
                                        StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f16875%2frestore-table-from-frm-and-ibd-file%23new-answer', 'question_page');
                                        }
                                        );

                                        Post as a guest















                                        Required, but never shown





















































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown

































                                        Required, but never shown














                                        Required, but never shown












                                        Required, but never shown







                                        Required, but never shown







                                        Popular posts from this blog

                                        Anexo:Material bélico de la Fuerza Aérea de Chile Índice Aeronaves Defensa...

                                        Always On Availability groups resolving state after failover - Remote harden of transaction...

                                        update json value to null Announcing the arrival of Valued Associate #679: Cesar Manara ...