MySQL backup table directly from the date folder, after restoring the database prompt table doesn ' t exist cause and workaround

Supplement: Under normal circumstances, it is recommended that database backup is best backed up by tools, and data migration can be done by copying database tables, and different kinds of unexpected problems will occur in various environments.

Background: Today in the collation of a Web site, the operating system due to automatic system updates led to the system has been a blue screen, alas, tragedy, and then reinstall the system Windows Server Enterprise 32bit.

Details:

After the system installation is complete, reconfigure the previous site, Appache service, after the configuration data is done, the original MySQL folder (C:\Windows.old\ProgramData\MySQL\MySQL Server 5.1\ Data) database folder and "*.frm"

Copy to the newly installed MYQL database folder (C:\ProgramData \mysql\mysql Server 5.1\data), restart the MySQL service, and view the database by using the Navicate for MySQL tool xxdatabase "In a table, the results show" MySQL table ' xxtable ' doesn ' t exist ",

This can give me a nasty, thought this can play big, the website of the data if the problem, the leadership still don't give me to barbecue ah.

So I google ah, Baidu Ah, Bing Ah, StackOverflow ah, and so on a few of the most commonly used search, search out the answer to all can not solve the problem ah, a time to eat the point of the meal, alas, thought to eat first, and then thinking more ideas, haha. So go back to eat a meal, can give me to support ah, nonsense less say, come back and find ah, the result still did not find the right solution, so carefully check the next MySQL data file Plus file, Found the Win-4fa0wlp5f0v.err and win-4fa0wlp5f0v.pid two files, so read the specific error content as follows

The feeling should be the database engine configuration problem, so search the relevant information about InnoDB and MyISAM, the following paragraph is very meaningful

Take the table "table" for example:
If the type is MyISAM, the data file is stored in the/data/$databasename/"directory with" Table.frm "" Table.myd "" Table.myi "" three files.
If the type is InnoDB, the data file is stored in the Ibdata1 file in the "$innodb _data_home_dir/″ (typically), and the structure file exists in table_name.frm.
MySQL database files can be copied directly, but that refers to the "MyISAM" type of table.
Instead of using Mysql-front to create a table directly, the default is "InnoDB", a table of this type that corresponds to only one "*.frm" file on disk, not "*" as MyISAM. myd,*. MYI "file.
MyISAM types of tables are directly copied to another database and can be used directly, but INNODB types of tables are not. The workaround is to:

Also copy the InnoDB database table "*.frm" File and InnoDB data "ibdata1" file to the appropriate location. Start the MySQL Windows service
Because of the data promiscuous form of MySQL, it is often easy for the user to forget the backup InnoDB during the backup, resulting in the above error.

This means that when the database engine type is innodb , Copy the data file and also need to copy the Ibdata1, so the ibdata1 also copied the past cover, found still a little problem, so stop the MySQL service, Remove all ib_logfile* files from the directory , restart the MySQL service, well done, yes.

Happy Ah, so a little summed up, hope to encounter the same problem, can be quickly resolved.

1, when the MySQL database backup or migration, as far as possible to back up the data required to complete;

2, if the original database file "*.frm", "*" is copied directly. MYD "," *. MYI "such as files, if the original database engine is InnoDB, remember to copy the Ibdata1 file

3, when backing up the database, it is best to use the relevant tools to backup or export SQL files , so as not to waste time on the database recovery

Versions of 4,MSYQL or backup tools may cause problems with data recovery.

Practice proves that the above problems exist, the solution is feasible, haha, in order to facilitate later, wrote this blog essay, Hope Daniel saw do not despise, welcome to shoot bricks.

1:myisam types of data files can be copied on different operating systems, which is important and convenient when it comes to deployment. (You only need to copy the files under the database name folder so that the database is copied)

2:innodb type to pay attention to multi-copy ibdata1, it is best not to copy the folder directly, but should be exported with SQL import

Reproduced in: http://www.cnblogs.com/joeylee/archive/2012/09/27/2705685.html