How to recover MySQL database

Here is the MySQL recovery database, which refers to how to recover a database without a normal backup of the data files saved through MySQL.

Since the MySQL database is intended to be reinstalled on a test machine, the process can be tragic due to the simple and brutal direct uninstallation of the MySQL database that is not backed up by the company Discuz and Redmine.

Fortunately, just uninstall the MySQL program, all the data files are still there.

The following is the process of recovering a database

1. Discuz Database

Discuz database recovery is very smooth, after installing the new version of MySQL, copy the original database files directly to the new data directory, restart MySQL, you can see the restored database

2. Redmine Database

I intend to use the above experience directly, but also can see all the tables, but is to execute the query, always error "table does not exist."

Later looked up some information, found that the reason should be discuz and redmine use of the MySQL engine is not the same result.

Discuz is using MyISAM, and Redmine is using InnoDB.

The solution is to

In addition to the copy data directory, remember to overwrite the ibdata1 file.

Here is the transfer from http://www.cnblogs.com/joeylee/archive/2012/09/27/2705685.html

Take 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. Starting 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.

The meaning is that when the database engine type is InnoDB, copy the data file at the same time also need to copy ibdata1, so the ibdata1 also copy the past cover, found still a bit of a problem, so stop the MySQL service, the directory of the ib_logfile* files are removed, 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

How to recover MySQL database