MySQL Copy database table mode Backup, after restore prompt table xxx ' doesn ' t exist

MySQL is very powerful, support direct copy database file Quick Backup, where is the database file?

Open the MySQL configuration file My.iniand locate the datadir node, as

Datadir= "D:/program files/mysql/mysql Server 5.1/data"

Enter the above folder, you can see the new database folder in MySQL, each folder is named after the database name, you want to back up which database, the folder copy away. To restore the database, copy it to the data directory, it is so simple!

However, after a copy of a database is backed up on a MySQL server today, after restoring on another server, restart the MySQL service and use the navicate for MySQL tool to view a table in the database "XXX Databases" and the results show " MySQL table ' xxx table ' doesn ' t exist'.

Viewed the next MySQL data file plus the file, found the win-4fa0wlp5f0v.err and win-4fa0wlp5f0v.pid two files, and then looked at the specific error content as follows:

120622 12:00:36 [Note] Plugin ' Federated ' is disabled.120622 12:00:37 innodb:initializing buffer pool, size = 300.0m1206 12:00:37 innodb:completed initialization of buffer poolinnodb:the first specified data file. \ibdata1 did not exist: INNODB:A new database to is created!120622 12:00:37 innodb:setting file. \ibdata1 size to Mbinnodb:database Physica Lly writes the file full:wait ... 120622 12:00:37 innodb:log file. \ib_logfile0 did not exist:new-be createdinnodb:setting Log file. \ib_logfile0 siz E to Mbinnodb:database physically writes the file full:wait ... 120622 12:00:37 innodb:log file. \ib_logfile1 did not exist:new-be createdinnodb:setting Log file. \ib_logfile1 siz E to Mbinnodb:database physically writes the file full:wait ... Innodb:doublewrite Buffer not found:creating newinnodb:doublewrite buffer createdinnodb:creating foreign key constrain T system tablesinnodb:foreign key constraint system tables created120622 12:00:39 innodb:started;Log sequence number 0 0120622 12:00:39 [note] Event scheduler:loaded 0 events120622 12:00:39 [note] D:\Program files\mysq L\mysql Server 5.1\bin\mysqld:ready for connections. Version: ' 5.1.55-community ' socket: ' port:3306 MySQL Community Server (GPL) 120622 12:02:49 [ERROR] cannot find or op En table magento/catalog_product_bundle_selection_price fromthe Internal data Dictionary of InnoDB though the. frm file fo R thetable exists. Maybe you has deleted and recreated InnoDB Datafiles but has forgotten to delete the corresponding. frm filesof InnoDB t Ables, or you are moved. frm files to another database?or, the table contains indexes the this version of the Enginedoes N ' t support. See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.htmlhow You can resolve the problem.

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 "Table.frm", "Table.myd", "Table.myi" three files are stored in the "/data/$databasename/" directory.

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. Starting the MySQL Windows service, because of the data promiscuous form of MySQL, it is often easy for users to forget the backup "ibdata1", 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 coverage, found still a bit of a problem, so stop the MySQL service, the directory under the ib_ logfile* files are all removed, restart the MySQL service, yes.

Summary:

1, if all are MyISAM engine, copy the original database file "*.frm", "*" directly. MYD "," *. MYI "Can, if the original database engine is InnoDB, remember to copy ibdata1 files;
2, back up the database, it is best to use professional tools to backup or export SQL files, so as not to waste time on the database recovery.

MySQL Copy database table mode Backup, after restore prompt table xxx ' doesn ' t exist