restore table from .frm and .ibd file?

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.

Answer 1

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.

Answer 2

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.

Answer 3

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://downloads.mysql.com/archives/utilities/

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

Other way you may have your create sql's.

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

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

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

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

I hope this information will be helpful for you.

Answer 4

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

Answer 5

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.

Answer 6

Note: Try to install MySQL same version, if your crashed MySQL version was 5.6, please install MySQL 5.6. (brew install mysql@5.6). If following process fails try the same things with MariaDB. (In my case, by MariaDB, I get back all database)

Process 1

• Before start, must stop MySQL or MariaDB. Use this command (brew services stop mysql), For MariaDB brew services stop mariadb
• ibdata1 file copy from backup mysql folder and paste and replace it inside new installed mysql folder's ibdata1 (usr/local/var/mysql) or mamp/xampp mysql folder location.
• ib_logfile0 and ib_logfile1 no need to replace first. If above process don't work, copy this file from backup mysql folder and replace. (In my case, i did not replace)
• Now copy 1 database for test (for example : testdb) from backup mysql folder and paste it inside new installed mysql folder. In testdb must have .frm and .ibd file.
• Restart mysql/mariadb (brew services start mysql) or (brew services start mariadb)
• Login MySQL -> mysql -u root -p
• If everything follow, mysql login successfully.
• Now check database, SHOW DATABASES;
• USE testdb;
• SHOW TABLES;
• SELECT * FROM testdbtable;
• If there don't show any massage, then its successfully restore.

Process 2

• If process1 not work, you have to install mysqlfrm (https://downloads.mysql.com/archives/utilities/)
• To find database table schema >> mysqlfrm --diagnostic /BACKUP/mysql/testdb > /RESTORE/testdb.sql
• Open testdb.sql in notepad/sublime you will see all database table schema but not data.
• Add ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; end of every CREATE TABLE....ENGINE=InnoDB otherwise table may not created.
• Create new database "newtestdb" and run the command >> mysqldump -uroot -p --databases newtestdb > testdb.sql
• It should create all database tables, Now if you check inside "newtestdb" you will see .frm and .ibd file.
• Now again login mysql >> mysql -u root -p
• SHOW DATABASES; USE newtestdb;
• Run this command one by one for all table, ALTER TABLE tablename DISCARD TABLESPACE; if you check newtestbd .ibd file will disapear.
• Now Copy only .ibd file from backup database testdb folder and paste it in newtestdb.
• Now run ALTER TABLE tablename IMPORT TABLESPACE;
• Hope now you will get back your data.

Answer 7

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

Answer 8

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

Answer 9

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.

Answer 10

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

Answer 11

try running this with mysql utilities

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

command/shell promt