MySQL InnoDB引擎数据库备份注意事项(由拷贝数据库文件备份引起的Table doesn’t exist)

问题描述及原因

以前使用的MySQL数据库表引擎都是MyISAM类型的，备份数据库时就复制MySQL data文件夹下的数据库文件夹。这次采用同样的方法还原wordpress项目数据库时，却出错了，用MySQL命令查询数据表提示#1146 – Table ‘XXX’ doesn’t exist，用phpMyAdmin查看数据表提示#1932 – Table ‘XXX’ doesn’t exist in engine。

phpMyAdmin查看数据表提示错误#1932 – Table ‘XXX’ doesn’t exist in engine

为何会出现这种情况，是因为该wordpress项目数据库采用的是InnoDB引擎，该类型的数据文件有部分内容存在data文件夹下的ibdata1文件中，备份时只复制数据库文件夹是不行的，同时需要复制ibdata1文件。

引擎主要类型及数据文件存储结构

MySQL引擎有DBD，HEAP，ISAM，MERFE，MyISAM，InnoDB和Gemeni7种，其中比较重要的是MyISAM和InnoDB两种。MyISAM适合场景：做很多count的计算；插入不频繁，查询非常频繁；没有事务。InnoDB适合场景：可靠性要求比较高，或者要求事物；表更新和查询都相当频繁，并且行锁定的机会比较大的情况。
MyISAM的数据文件以Table.frm、Table.MYD、Table.MYI三个文件存储于/data/$databasename/目录中。InnoDB的数据文件存储在$innodb_data_home_dir/中的ibdata1文件中(一般情况，默认采用的是共享表空间)，结构文件存在于table_name.frm中。

InnoDB中有共享表空间和独立表空间的概念。共享表空间就是ibdata1，独立表空间放在每个表的.ibd（数据和索引）和.frm（表结构）为后缀的文件中。单独的表空间只存储该表的数据，索引和插入缓冲的BITMAP等信息，其余还放在共享表空间中。更改my.ini文件中innodb_file_per_table = 1，可开启独立表空间。

数据库备份注意事项

MySQL的数据库文件直接复制便可使用，但那是指“MyISAM”类型的表。MyISAM类型的表直接拷到另一个数据库就可以直接使用，但是InnoDB类型的表却不行。InnoDB类型的数据库想通过复制文件使用，就需要同时复制innodb数据库表“*.frm”文件和innodb数据“ibdata1”文件到合适的位置，但这就存在一个问题，那就是“ibdata1”文件包含所有的InnoDB引擎数据库内容，无法单个数据库复制备份，而且还容易出现异常，就算是开启独立表空间，还是有部分数据(比如字典信息和Undo)会存在ibdata1文件中。采用InnoDB引擎的数据库无法直接通过复制进行单个数据库的备份和还原，最好通过mysqldump进行备份与还原或者采用专业工具备份还原。

mysqldump备份还原操作方法

方法如下：
1.备份数据库

mysqldump -u [USERNAME] -p [DATABASE_NAME] > [BACKUP_FILE_NAME]

2.还原数据库（DATABASE_NAME数据库已存在）

mysql -u [USERNAME] -p [DATABASE_NAME] < [BACKUP_FILE_NAME]

3.若该设备没有此数据库，则需执行sql语句，创建新的数据库，然后再还原数据库

mysql> create database [DATABASE_NAME];

总结

1.如果是MyISAM引擎，直接复制数据库文件Table.frm、Table.MYD、Table.MYI即可，如果数据库引擎是InnoDB，切记还需拷贝ibdata1文件，但由于InnoDB存储情况复杂，不建议采用这种方法备份；
2.备份数据库的时候，最好是用专业的工具进行备份或通过mysqldump导出sql文件，以免出现意外，浪费时间在数据库恢复上。

转载请注明：半亩方塘 » MySQL InnoDB引擎数据库备份注意事项(由拷贝数据库文件备份引起的Table doesn’t exist)