How to restore a Mysql database _ MySQL

How to restore Mysql database details bitsCN.com

Because I plan to re-install the Mysql database on a test machine, the process may be miserable because I simply unmount the Mysql database and I didn't back up the Mysql database used by Discuz and Redmine.

Fortunately, the Mysql program is uninstalled, and all the data files still exist.

The following is the process of restoring the database.

1. Discuz database

The recovery of the Discuz database is very smooth. after the new Mysql version is installed, copy the original database file to the new data directory and restart mysql to see the recovered database.

2. Redmine database

I plan to directly use the above experience to view all the tables, but the error "the table does not exist" is always reported during query ".

Later I checked some information and found that the reason should be that the Mysql engine used by Discuz and Redmine is different.

Discuz uses MyISAM, while Redmine uses InnoDB.

The solution is,

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

Take Table as an example: if the type is MyISAM, the data file uses Table. frm "Table. MYD "Table. "MYI" "files are stored in the"/data/$ databasename/"directory. for example, if the data type is InnoDB, the data files are stored in the ibdata1 file in "$ innodb_data_home_dir/" (generally), and the structure files are stored in table_name.frm. mySQL database files can be directly copied, but that is a table of the "MyISAM" type. MySQL-Front is used to directly create a table. the default value is "InnoDB". a table of this type corresponds to only one "*. frm file, unlike MyISAM. MYD ,*. MYI file. MyISAM tables can be directly copied to another database, but InnoDB tables cannot. The solution is:

Copy the "*. frm" file of the innodb database table and the innodb data "ibdata1" file to the appropriate location. To start MySQL Windows services, because of the mixed data format such as MySQL, it is easy for users to forget to back up InnoDB during backup, resulting in the above error.

This means that when the database engine type is InnoDB, ibdata1 needs to be copied while copying the data file. Therefore, ibdata1 is also copied to overwrite the data file and the mysql service is stopped, delete all ib_logfile * files in the directory and restart the mysql service.

Happy, so I summarized it a little. I hope that the same problem will be solved quickly in the future.

1. during mysql database backup or migration, back up the required data as much as possible;

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

3. when backing up a database, it is best to back up or export SQL files using related tools to avoid wasting time on database recovery.

4. different versions of msyql or backup tools may also cause data recovery problems.

Practice has proved that the above problems exist, and the solution is feasible. haha, I wrote this blog essay for convenience in the future. I hope Daniel will not despise it and welcome to shoot bricks.

1: MyISAM data files can be copied in different operating systems. this is very important and convenient to deploy. (You only need to copy the files under the database name folder so that the database is fully copied)

2: For the InnoDB type, pay attention to copying ibdata1 more. instead of copying folders directly, use SQL to import and export data.

BitsCN.com