记一次服务器频繁断电重启之后导致的MySQL数据库故障、数据丢失恢复过程、解决2002/2013/1062等错误代码【测试成功】

艺帆风顺 发布于 2025-04-03 19 次阅读

一、故障背景

    在2023-12月的周末，项目服务器异常断电多次，显示服务器无法启动成功，随后服务器启动成功后，数据库运行异常，业务系统存在多处报错、使用mysql命令行也存在各种报错，逐个排查、定位原因。

    数据库大小14G左右，单次还原2小时左右，单次备份25分钟左右。

二、排查连接过程报错

1、处理MySQL 2002连接报错：

    [root@localhost ~]# mysqldump -uroot -p -d test> test.sql mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) when trying to connect

        解决：netstat -ln | grep mysql

      [root@cheliangweb ~]# netstat -ln | grep mysql unix 2 [ ACC ] STREAM LISTENING 1340055 /home/Yinling/mysql/mysql.sock

          连接参数中加入：--socket=/usr/local/Yinling/mysql/mysql.sock

      2、处理MySQL 2013 报错

          本文内容参考：

        https://www.jianshu.com/p/98c7a63b84c3https://blog.csdn.net/Rice_kil/article/details/105271904?app_version=6.2.4&code=app_1562916241&csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22105271904%22%2C%22source%22%3A%22chen8u88%22%7D&uLinkId=usr1mkqgl919blen&utm_source=app

        三、排查备份过程报错

        1、使用mysqldump导出故障库报错：

          mysqldump: Got error: 1556: “You can‘t use locks with log tables.“ when using LOCK TABLES

          解决：在mysqldump指令后添加--lock-tables=0即可。

          2、使用mysqldump导出提示某个表不存在

          使用--ignore-table参数=vehicle_s2.schedule_reim_detail

          之后即可正常导入。

          示例：

            mysqldump -h127.0.0.1 -u root -pxxx --ignore-table=vehicle_s2.schedule_reim_detail vehicle_s2 > vehicle_s2.sql

            3、MySQL无法创建单表，提示表已存在，但是在数据库中查不到该表，删除.ibd重启mysql无效，依然存在此问题。

              SELECT *FROM information_schema.tablesWHERE table_schema = 'vehicle_s2' AND table_name = 'schedule_reim_detail';

              尝试方法：更改表名为大写，配置不区分大小写，无作用。

                  删除了.ibd文件，重启后又自动生成了这个文件，是因为InnoDB的内部数据字典中仍然存有这个表的信息。
                  root用户无权操作InnoDB的内部数据字典，需要更底层的工具处理，可能会导致数据丢失或者数据库无法正常工作。 该方法放弃。

                  随后将该故障库除开本表之外，全库导出，还原到另一台服务器。

                  使用--ignore-table参数=vehicle_s2.schedule_reim_detail

              四、尝试通过.ibd文件结合innodb_force_recovery修复单表数据

              1、查看mysql表空间位置

                  SHOW VARIABLES LIKE 'datadir';

              *.ibd：每个 InnoDB 表都有一个单独的 .ibd 文件，用于存储表的数据和索引。

              2、创建与原表结构相同的表：

                  如果 .ibd 文件对应的表不存在，需要首先创建一个具有相同结构的空表，以便将数据导入其中。

              3、将 .ibd 文件导入到新表：

                  ALTER TABLE schedule_reim_detail TABLESPACE;

                 该条命令会删除新表的现有 .ibd 文件。

                  然后，将 .ibd 文件复制到 MySQL 数据目录下.ibd文件的正确位置。

                  ALTER TABLE schedule_reim_detail IMPORT TABLESPACE;

              4、验证和修复表

                  CHECK TABLE schedule_reim_detail;
                  REPAIR TABLE schedule_reim_detail;

              备注：使用该方法需要确保.ibd文件本身是正常的，在本次经过实测怀疑.ibd文件本身损坏，尝试另一种办法。

              出现"Tablespace is missing"的错误通常表示InnoDB表空间文件（.ibd文件）丢失或损坏。

                mysql> ALTER TABLE test IMPORT TABLESPACE;ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table `vehicle_s3`.`test` : Data structure corruption

                5、其他：使用InnoDB Force Recovery强制修复数据

                    如果有可用的备份，强烈建议使用备份还原数据库到正常状态。数据结构损坏可能会导致数据不一致性，因此从备份中还原是一种较为安全的选择。

                    在 MySQL 中，可以尝试使用 InnoDB 强制恢复（InnoDB Force Recovery）来修复数据结构。在my.cnf配置文件中添加以下设置：

                    innodb_force_recovery = 1

                 尝试重新导入表空间：ALTER TABLE test IMPORT TABLESPACE;

                  重启MySQL服务再次操作，发现仍然无法正常恢复.ibd文件，该方法放弃。

                五、尝试通过binlog日志文件恢复数据

                1、查看binlog是否开启及路径

                    SHOW VARIABLES LIKE 'log_bin';

                    SHOW VARIABLES LIKE 'log_bin_basename';

                2、导出binlog到sql文件

                    cd /usr/local/Yinling/mysql/binlogs/mysql-bin

                    选择要回退的时间点：

                比如：还原到2023-12-09 12:16的数据，

                则导出mysql-bin.000084为sql，包含了12-07 8:11至12-09 12:16的数据。

                mysqlbinlog /usr/local/Yinling/mysql/binlogs/mysql-bin.000081 > /test/81.sql

                3、通过sql还原到新库

                    新库中应包括需要还原的表的表数据结构，否则无法正常执行导入。

                    只导出Mysql数据表的表结构命令：

                mysqldump -uroot -pYinling_2021 --no-data --single-transaction --socket=/home/Yinling/mysql/mysql.sock vehicle_s1 >vehicle_s1.sql

                    连接MySQL数据库后，使用source执行导入即可，成功后可看到恢复的数据。

                4、使用binlog的SQL导入后提示ERR 1062主键冲突

                  [root@localhost test]# mysql -h 10.18.xx.63 -uroot -pY1 vehicle_s2 mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1062 (23000) at line 38: Duplicate entry 'b36d9dc4c87b4ed383b1693a17cb862f' for key 'PRIMARY'

                  解决：使用--force 选项可以让 MySQL 忽略插入时的主键冲突，而不中止整个导入操作。

                    mysql -h 10.18.10.63 -uroot -pxxx --force vehicle_s2 81.sqlmysql -uroot -pxxx --force kpi 15.sql

                    备注：--force 选项会忽略导入过程中的所有错误，包括其他类型的错误。

                    注意：要恢复的库名应该和源数据库名完全一致。

                    六、MySQL添加函数1418报错

                         导入sql还原后，发现函数没有被导进去，业务系统出现了报错，信息如下：   

                      1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

                      原因：这个错误是因为MySQL的二进制日志（binary logging）已经启用，但是你正在尝试创建或修改一个没有声明DETERMINISTIC、NO SQL或READS SQL DATA选项的存储过程或函数。

                      解决：设置系统变量log_bin_trust_function_creators为1，这样MySQL就会信任所有的存储过程和函数，不需要它们声明DETERMINISTIC、NO SQL或READS SQL DATA选项。

                          SET GLOBAL log_bin_trust_function_creators = 1;

                      注意：设置log_bin_trust_function_creators为1可能会增加数据不一致的风险。

                        create definer = root@`%` function currval() returns varchar(64)BEGINDECLARE VALUE VARCHAR(64);SET VALUE='0';SELECT dict_value INTO VALUEFROM schedule_sys_dict WHERE id = 'seq_id1';RETURN VALUE;END;
                        create definer = root@`%` function nextval() returns varchar(64)BEGINDECLARE VALUE VARCHAR(64);SELECT dict_value INTO VALUE FROM schedule_sys_dict where id = 'seq_id1' for update;UPDATE schedule_sys_dictSET dict_value = VALUE + 1where id = 'seq_id1';return currval();END;