How can I restore/replicate the INNODB data table by copying the frm file directly?

Source: Internet
Author: User
Tags types of tables

How can I restore/replicate the INNODB data table by copying the frm file directly?
in the use of MySQL5.0, often there are some strange strange problems, resulting in the service can not start, had to reinstall MySQL, but so the original database will be lost.
I heard that MySQL database files can be used directly, but according to the query, it refers to the original "MyISAM" type of table.
I usually use Mysql-front to create a table directly, the default is "InnoDB" type, this type of table on disk as if only one "*.frm" file, not as MyISAM "*. myd,*. MYI "file.
MyISAM Types of Tables I tried, as if I could directly copy to another database, but the InnoDB type of table was not.
There are many ways to search the Internet:
a file that says copying "ibdata1" at the same time, but this causes the new database to fail to start.
a command that appears to say "TRUNCATE table name" is used to restore the table structure and is unsuccessful
there is said to use what "Mysqldump.exe", but the premise of this method seems to need the database is good, and can start running normally, and I am now the database is broken, only the table file ...

by this problem is really stirred headache, hope that experienced master can give a thorough solution:
That is, how can I restore a table to a new database if I have only a innodb type of frm table file on hand now? And it's better to recover the data!


Probably found a solution, just like the first method of searching online, as I mentioned in my question:
Also copy the InnoDB database table "*.frm" File and InnoDB data "ibdata1" file to the appropriate location. Start the MySQL Windows service, if not successful, see the Data folder has a "*.err" error log file, which will be the reason for the startup failure is described. For example, I have encountered two wrong reasons.
One is an error message similar to this:
[Code=inifile] InnoDB:Error:log file. \ib_logfile0 is of different size 0 10485760 bytes
Innodb:than specified in the. cnf file 0 25165824 bytes! [/code]
This is because the log file size configured in the MySQL configuration file does not match the actual.
The workaround is to delete the old "Ib_logfile0" log files directly, after restarting MySQL will automatically generate a new log file.
The other one is this error message
[Code=inifile] Innodb:operating System error number 5 in a file operation.
Innodb:the error means mysqld does not has the access rights to
Innodb:the directory. It may also is created a subdirectory
Innodb:of the same name as a data file.
Innodb:file name. \ibdata1
Innodb:file operation call: ' Open '.
Innodb:cannot continue operation. [/code]
It was checked that the "ibdata1" file was added to the read-only property somehow during the copy process.
The workaround is to remove the read-only property of the "Ibdata1" file.

The above method seems to apply only to copying the old InnoDB database into a new database that does not have innodb data.
If the target database already has the data "ibdata1" and other files, do not use what mysqldump, I do not know there is no easy way to merge old and new data?


How can I restore/replicate the INNODB data table by copying the frm file directly?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.