在mysql中删除了ibdata1,我遇到了巨大的麻烦:((LINUX操作系统)

deleted ibdata1 in mysql and I am in a huge trouble :( (LINUX)

提问人:Bachar ELkarni 提问时间:6/22/2023 更新时间:6/23/2023 访问量:311

问:

我知道我很笨,但这是一个生产数据库,我遇到了巨大的麻烦, 我想访问数据库,但我忘记了mysql密码,一件事导致我到另一件事, 直到我删除了 ibdata1(感谢 ChatGPT),现在我处于紧要关头,MySQL 无法启动,我无法访问任何内容。 我只需要启动数据库,这样我就可以在可能的情况下导出数据。 请帮帮我。

root@localhost:~# systemctl start mysql
Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details.
root@localhost:~# systemctl status mysql.service
× mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Thu 2023-06-22 12:07:18 UTC; 14s ago
    Process: 1641 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
    Process: 1649 ExecStart=/usr/sbin/mysqld (code=exited, status=3)
   Main PID: 1649 (code=exited, status=3)
     Status: "Server startup in progress"
        CPU: 294ms

Jun 22 12:07:18 localhost systemd[1]: mysql.service: Main process exited, code=exited, status=3/NOTIMPLEM>
Jun 22 12:07:18 localhost systemd[1]: mysql.service: Failed with result 'exit-code'.
Jun 22 12:07:18 localhost systemd[1]: Failed to start MySQL Community Server.
Jun 22 12:07:18 localhost systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
Jun 22 12:07:18 localhost systemd[1]: Stopped MySQL Community Server.
Jun 22 12:07:18 localhost systemd[1]: mysql.service: Start request repeated too quickly.
Jun 22 12:07:18 localhost systemd[1]: mysql.service: Failed with result 'exit-code'.
Jun 22 12:07:18 localhost systemd[1]: Failed to start MySQL Community Server.
root@localhost:~# sudo tail -n 50 /var/log/mysql/error.log
2023-06-22T11:45:30.331779Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T11:45:30.932065Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1347
2023-06-22T11:45:30.937894Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T11:45:31.003681Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T11:45:31.003698Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T11:45:31.003703Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T11:45:31.003709Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T11:45:31.003717Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T11:45:31.443411Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1369
2023-06-22T11:45:31.448765Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T11:45:31.507675Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T11:45:31.507693Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T11:45:31.507698Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T11:45:31.507713Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T11:45:31.507718Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T12:07:15.741932Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1564
2023-06-22T12:07:15.750132Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T12:07:15.815802Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T12:07:15.815828Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T12:07:15.815834Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T12:07:15.815845Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T12:07:15.815851Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T12:07:16.445381Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1586
2023-06-22T12:07:16.452038Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T12:07:16.511688Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T12:07:16.511711Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T12:07:16.511717Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T12:07:16.511728Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T12:07:16.511733Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T12:07:16.992627Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1607
2023-06-22T12:07:16.999282Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T12:07:17.071744Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T12:07:17.071769Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T12:07:17.071775Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T12:07:17.071780Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T12:07:17.071786Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T12:07:17.738538Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1628
2023-06-22T12:07:17.744771Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T12:07:17.807827Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T12:07:17.807847Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T12:07:17.807853Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T12:07:17.807861Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T12:07:17.807866Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2023-06-22T12:07:18.184843Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.33-0ubuntu0.22.04.2) starting as process 1649
2023-06-22T12:07:18.190614Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-06-22T12:07:18.251677Z 1 [ERROR] [MY-012592] [InnoDB] Operating system error number 2 in a file operation.
2023-06-22T12:07:18.251696Z 1 [ERROR] [MY-012593] [InnoDB] The error means the system cannot find the path specified.
2023-06-22T12:07:18.251710Z 1 [ERROR] [MY-012594] [InnoDB] If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them.
2023-06-22T12:07:18.251716Z 1 [ERROR] [MY-012646] [InnoDB] File ./ibdata1: 'open' returned OS error 71. Cannot continue operation
2023-06-22T12:07:18.251721Z 1 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
MySQL Linux linode

评论

0赞 Bill Karwin 6/22/2023
在 stackoverflowdba.stackexchange.com 上有许多与您类似的问题。你读过吗?
0赞 Bill Karwin 6/22/2023
简短的回答是:您现在必须恢复最新的备份。如果您没有备份,那么这是一个关于保留备份重要性的代价高昂的教训。
0赞 Bill Karwin 6/22/2023
另一个教训是:不要相信ChatGPT。它什么都不知道,也找不到答案,它只预测哪些单词似乎可以组合成合理的句子。它无法区分事实和虚构,所以如果你用它来做你自己不了解的事情,那么它就是盲人牵着瞎子。

答:

1赞 Bachar ELkarni 6/23/2023 #1

好吧,首先感谢所有为我的问题付出时间的人,我愚蠢到忘记了 ab 进行备份,但无论如何我都拿回了我的数据。 我通过 sftp 将 .ibd 文件发送到 mysql 运行良好的 Windows 中,然后创建了一个新数据库,从 github 中提取了我的项目,应用了迁移,然后用我导入的文件更改了 .ibd 文件。最后,我使用 SQL Alter 语句一一导入了所有表的表空间,这让我的数据回到了:) 对于阅读本文的任何人,请备份您的数据库 rn。你真的不想感受到那种痛苦