MySQL数据库备份和恢复常见错误及解决方案

“没有数据就没有一切，数据库备份是一种防范灾难于未然的强力手段。”，尽管采取了一些管理措施来保证数据库的安全，但是不确定的意外情况总是有可能造成数据的损失。保证数据安全最重要的一个措施是确保对数据进行定期备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复，这样就尽可能地降低了意外原因导致的损失。MySQL提供了多种方法对数据进行备份和恢复，例如: MySQL自带的备份语句、第三方备份工具或者图形化管理工具等。本文列举MySQL数据库备份和恢复常见错误及解决方案。

错误14-1 导出失败

【问题描述】

在使用SELECT INTO OUTFILE语句导出“tb_class”数据表时，如图14.39所示命令提示符报错“ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement”。

■ 图14.39 导出失败错误图

【解决方案】

目标目录不符合secure_file_priv 参数，需要设置该参数。在MySQL中使用MySQL命令“SHOW VARIABLES LIKE '%secure_file_priv%';”查询可存储目录。查询效果如图14. 40所示。

■ 图14.40 查询结果

在“Value”中可见存储的位置应为“C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\”目录下。所以有两个修改方式：

方式一：将目标目录改为“C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\tb_class.txt”。

方式二：在配置文件my.ini修改“secure_file_priv”参数的值。

在“C:\ProgramData\MySQL\MySQL Server 8.0”目录下使用文本查看器打开my.ini配置文件搜索查找到如图14.41 所示“secure_file_priv”参数。

■ 图14.41 secure_file_priv参数

将“secure_file_priv”后的参数修改为空字符串“''”，效果如图14.42所示。

■ 图14.42 修改后效果

再次在MySQL中使用MySQL命令“SHOW VARIABLES LIKE '%secure_file_priv%';”查询可存储目录会发现目录为空，此时可以导出至任意位置，效果如图14.43所示。

■ 图14.43重新查询结果

错误14-2 删除表格失败

【问题描述】

在执行删除表格数据操作时，如图14.44所示命令提示符报错“ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db_study`.`tb_student`, CONSTRAINT `fk_class_id1` FOREIGN KEY (`class_id`) REFERENCES `tb_class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)”。

■ 图14.44删除表格失败错误图

【解决方案】

由于数据表结构严谨，表与表之间有着各种约束所以导致删除失败。在MySQL中执行“SET FOREIGN_KEY_CHECKS=0;”语句，使数据库忽略外键约束，然后再执行删除语句“DELETE FROM tb_class;”，如图14.45 所示则表示操作成功。

■ 图14.45删除成功

操作成功后，为保证数据库严谨性，需要使用MySQL命令“SET FOREIGN_KEY_CHECKS=1;”如图14.46所示将约束再次打开。

■ 图14.46打开约束

错误14-3 迁移失败情况一

【问题描述】

在进行MySQL数据库迁移时，如图14.47所示命令提示符报错“ERROR 1046 (3D000) at line 22: No database selected”。

■ 图14.47迁移失败错误图

【解决方案】

指令中没有输入接收数据库数据的目标数据库，需要在后面加上指定的目标数据库。

错误14-4 迁移失败情况二

【问题描述】

在进行MySQL数据库迁移时，如图14.48所示命令提示符报错“ERROR 1142 (42000) at line 22: DROP command denied to user 'Test'@'DESKTOP-2QVR6V8' for table 'tb_class'”。

■ 图14.48迁移失败错误图

【解决方案】

因为MySQL数据库迁移数据原理是先删除后插入，在这个过程中接收方如果没有对传输发方进行操作授权的话就无法进行，授权操作请参考“第13章数据库的安全性”。

错误14-5 迁移失败情况三

【问题描述】

在进行MySQL数据库迁移时，如图14.49所示命令提示符报错“mysqldump: Got error: 1045: Access denied for user 'root'@'DESKTOP-2QVR6V8' (using password: YES) when trying to connect”。

■ 图14.49迁移失败

【解决方案】

系统提示访问被拒绝，原因是主机访问没有权限，查看MySQL数据库中的mysql数据库，查询user数据表如图14.50所示，Host和User字段可以发现，root用户的Host主机名是localhost。

■ 图14.50 查询user数据表

所以在传输过程中输入IP地址会报错，可以将迁移指令中的Host改为与mysql数据库中的user数据表对应的Host参数“localhost”。也可以将root用户对应的Host参数修改为通配符“%”，通配任意N个字符。在MySQL中输入SQL语句“ UPDATE user SET Host='localhost' WHERE User='root';”并使用SQL语句“FLUSH PRIVILEGES;”刷新权限后再次查询，如图14.51所示操作成功。