restore table from .frm and .ibd file?

  • 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.

    This quote from Apr 23, 12 by Rolando is still valid today. 'Just copying the .frm and .ibd files from one location to another is asking for trouble.' Use alternative such as mysqldump to get your old data into a form that can be loaded as planned years ago. Also known as a backup.

  • 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.

    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 ?

    Your post on `How to Recover an InnoDB table whose files were moved around` literally saved my life. Thank you very much.

  • 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.

    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 glad it's still helping!

    getting error 1146 table doesn't exist when doing the dump

    You can also use PHPMyAdmin to do the export (instead of `mysqldump` from Termina).

  • 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:[email protected]: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.

    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ānisElmeris > `Manually adding ROW_FORMAT=compact at the end of the CREATE statement did the trick.` That worked for me too. Thanks!

  • 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

  • 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.

  • 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).

  • 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

    This looks suspiciously like a link-only answer.

    Because all the links are from our own site, they aren't likely to disappear, so I'm okay with this @mustaccio

    This doesn't seem to add much value, given most of these also appear in the "Related" list few pixels to the right.

    @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.

    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.

    if you think that it didnt add any value, then I have to delete my above comment, where I praised you for some aspects (though, my words doesnt change anything you know)

  • I just want to add one more thing for macos El Capitan users. MySQL utilities is not supported for this version, so mysqlfrm command is not helpful. What I did is recovering my table structures with dbsake as shown in this link: https://www.percona.com/blog/2015/12/16/recovering-table-structure-from-frm-files-using-dbsake/

    All you need to do is install dbsake:

    # curl -s http://get.dbsake.net > dbsake
    # chmod u+x dbsake
    

    then use frmdump command and provide path to your .frm file:

    # ./dbsake frmdump /var/lib/mysql/sakila/staff.frm
    

    you will get the create statement. Once I've done this, I simply followed the steps 2 to 5 already mentioned by @Ecd. Hope it helps someone.

  • I really appreciate Ecd. What worked for me:

    1.- I had a backup of the base a few months ago this helped me to lift this backup in xampp in Windows 10 and create the tables to have the structure (configuration: Windows 10, xampp-windows-x64-7.1.30-5-VC14) mysql my.ini configuration file at the end

    NOTE: Some tables did not have ROW_FORMAT = COMPACT, so I went to operations on each 
        table and changed it manually.
        (If I did not do that, an error appeared and I did not let the import).
    
    NOTE2: I had the backup of months ago but it should also work by first recovering 
        the structure of the .frm files in case of not having a backup at hand.
        (You can try this link:
        https://www.percona.com/blog/2014/01/02/recover-table-structure-frm-files-mysql- 
        utilities/)
    

    2.- Having the old database up, I proceeded to execute alter table xxx discard tablespace for each table in the database that I wanted to recover, then the .ibd files of the data folder in C: / xampp / mysql / data / system had been deleted (in this case it is this path)

    3.- I proceeded to copy the .ibd files from the database I wanted to recover to the xampp folder of the old database

    4.- Having the files copied, run: alter table xxx import tablespace For each table in the database, a warning will appear but we will ignore it, the data will be loaded in the table and can be exported later.

    5.- Export the entire database into an sql file and proceed to build it in production and success!

    # Example MySQL config file for small systems.
    #
    # This is for a system with little memory (<= 64M) where MySQL is only used
    # from time to time and it's important that the mysqld daemon
    # doesn't use much resources.
    #
    # You can copy this file to
    # C:/xampp/mysql/bin/my.cnf to set global options,
    # mysql-data-dir/my.cnf to set server-specific options (in this
    # installation this directory is C:/xampp/mysql/data) or
    # ~/.my.cnf to set user-specific options.
    #
    # In this file, you can use all long options that a program supports.
    # If you want to know which options a program supports, run the program
    # with the "--help" option.
    
    # The following options will be passed to all MySQL clients
    [client] 
    # password       = your_password 
    port            = 3306 
    socket          = "C:/xampp/mysql/mysql.sock"
    
    
    # Here follows entries for some specific programs 
    
    # The MySQL server
    [mysqld]
    port= 3306
    socket = "C:/xampp/mysql/mysql.sock"
    basedir = "C:/xampp/mysql" 
    tmpdir = "C:/xampp/tmp" 
    datadir = "C:/xampp/mysql/data"
    pid_file = "mysql.pid"
    # enable-named-pipe
    key_buffer = 160M
    max_allowed_packet = 300M
    sort_buffer_size = 1204K
    net_buffer_length = 80K
    read_buffer_size = 512K
    read_rnd_buffer_size = 1024K
    myisam_sort_buffer_size = 8M
    log_error = "mysql_error.log"
    
    # Change here for bind listening
    # bind-address="127.0.0.1" 
    # bind-address = ::1          # for ipv6
    
    # Where do all the plugins live
    plugin_dir = "C:/xampp/mysql/lib/plugin/" 
    
    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    # 
    # commented in by lampp security
    #skip-networking
    #skip-federated
    
    # Replication Master Server (default)
    # binary logging is required for replication
    # log-bin deactivated by default since XAMPP 1.4.11
    #log-bin=mysql-bin
    
    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id   = 1
    
    # Replication Slave (comment out master section to use this)
    #
    # To configure this host as a replication slave, you can choose between
    # two methods :
    #
    # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
    #    the syntax is:
    #
    #    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
    #    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
    #
    #    where you replace <host>, <user>, <password> by quoted strings and
    #    <port> by the master's port number (3306 by default).
    #
    #    Example:
    #
    #    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
    #    MASTER_USER='joe', MASTER_PASSWORD='secret';
    #
    # OR
    #
    # 2) Set the variables below. However, in case you choose this method, then
    #    start replication for the first time (even unsuccessfully, for example
    #    if you mistyped the password in master-password and the slave fails to
    #    connect), the slave will create a master.info file, and any later
    #    change in this file to the variables' values below will be ignored and
    #    overridden by the content of the master.info file, unless you shutdown
    #    the slave server, delete master.info and restart the slaver server.
    #    For that reason, you may want to leave the lines below untouched
    #    (commented) and instead use CHANGE MASTER TO (see above)
    #
    # required unique id between 2 and 2^32 - 1
    # (and different from the master)
    # defaults to 2 if master-host is set
    # but will not function as a slave if omitted
    #server-id       = 2
    #
    # The replication master for this slave - required
    #master-host     =   <hostname>
    #
    # The username the slave will use for authentication when connecting
    # to the master - required
    #master-user     =   <username>
    #
    # The password the slave will authenticate with when connecting to
    # the master - required
    #master-password =   <password>
    #
    # The port the master is listening on.
    # optional - defaults to 3306
    #master-port     =  <port>
    #
    # binary logging - not required for slaves, but recommended
    #log-bin=mysql-bin
    
    
    # Point the following paths to different dedicated disks
    #tmpdir = "C:/xampp/tmp"
    #log-update = /path-to-dedicated-directory/hostname
    
    # Uncomment the following if you are using BDB tables
    #bdb_cache_size = 40M
    #bdb_max_lock = 10000
    
    # Comment the following if you are using InnoDB tables
    #skip-innodb
    innodb_data_home_dir = "C:/xampp/mysql/data"
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = "C:/xampp/mysql/data"
    #innodb_log_arch_dir = "C:/xampp/mysql/data"
    ## You can set .._buffer_pool_size up to 50 - 80 %
    ## of RAM but beware of setting memory usage too high
    innodb_buffer_pool_size = 16M
    ## Set .._log_file_size to 25 % of buffer pool size
    innodb_log_file_size = 50M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 600
    
    ## UTF 8 Settings
    #init-connect=\'SET NAMES utf8\'
    #collation_server=utf8_unicode_ci
    #character_set_server=utf8
    #skip-character-set-client-handshake
    #character_sets-dir="C:/xampp/mysql/share/charsets"
    sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
    log_bin_trust_function_creators = 1
    
    [mysqldump]
    quick
    max_allowed_packet = 160M
    
    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates
    
    [isamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [myisamchk]
    key_buffer = 20M
    sort_buffer_size = 20M
    read_buffer = 2M
    write_buffer = 2M
    
    [mysqlhotcopy]
    interactive-timeout
    
    

    I hope it helps someone who has this situation, regards.

    English provided by Google

  • try running this with mysql utilities

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

    command/shell promt

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM