How to restore the Mysql database

As I was about to reinstall the Mysql database on one of the test machines, I simply uninstalled it without backing up the Mysql database used by company Discuz and Redmine, which made the process miserable.

The good news is that the Mysql program has been uninstalled, and all the data files still exist.

The following is the process of restoring the database

1. Discuz database

The recovery of Discuz database is very smooth. After installing the new version of Mysql, directly put the original database file copy into the new data directory, restart mysql, and you can see the restored database

2. Redmine database

I intended to use the above experience directly and see all the tables, but when I executed the query, I always reported an error of "table does not exist ".

Later, I checked some information and found that the reason should be the Mysql engines used by Discuz and Redmine.

Discuz USES MyISAM, while Redmine USES InnoDB.

The solution is,

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

Take table "Table" as an example: If the type is MyISAM, the data files are stored as "Table.frm", "Table.MYD", "Table.MYI" in the directory "/data/$databasename/". If the type is InnoDB, the data files are stored in the ibdata1 file "$innodb_data_home_dir/" The structure file exists in table_name.frm. MySQL's database file can be copied directly, but that refers to a table of type "MyISAM". Instead, MySQL-Front is used to create the table directly, and the default is "InnoDB" type. One table of this type only corresponds to one "*.frm" file on disk, instead of "*.MYD,*.MYI" file as MyISAM does. A table of type MyISAM can be used directly by copying it to another database, but a table of type InnoDB cannot. The solution:

At the same time, copy the innodb database table "*.frm" file and innodb data "ibdata1" file to the appropriate location. Starting MySQL's Windows service due to the mixed data form of MySQL, it is often easy for users to forget to backup InnoDB when they backup, resulting in the above error.

This means that when the database engine type is InnoDB, you need to copy ibdata1 as well as the data files, so you copy ibdata1 and overwrite it. You find that there is still a problem, so you stop mysql service, delete all the ib_logfile* files in the directory, and restart mysql service, well done

Happy, so a little summary, hope to encounter the same problem in the future, can be quickly solved.

1. When backup or migration of mysql database, try to backup the required data;

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

3. When backing up the database, it is better to use relevant tools to backup or export sql files, so as not to waste time on database recovery

4. Different versions of msyql or backup tools may cause problems in data recovery.

Practice has proved that the above problems exist, the solution is feasible, haha, for the convenience of the future, wrote this blog essay, I hope Daniel see don't despise, welcome to clap brick.

1: data files of type MyISAM can be used in different operating systems. This is very important. (just copy the file under the database name folder, and you're done copying the database.)

2: for InnoDB type, be aware of multiple copies of ibdata1. It is better not to copy the folder directly, but to import and export with sql