删除了mysql中的ibdata1,我陷入了巨大的麻烦 :((LINUX)

huangapple go评论118阅读模式
英文:

deleted ibdata1 in mysql and I am in a huge trouble 删除了mysql中的ibdata1,我陷入了巨大的麻烦 :((LINUX) (LINUX)

问题

抱歉,我看到你的请求了,下面是你提供的文本的翻译部分:

我知道我很愚蠢,但这是一个生产数据库,我遇到了巨大的麻烦,我想访问数据库,但我忘记了MySQL密码,一件事导致了另一件事,直到我删除了ibdata1(多亏了chatgpt),现在我陷入了困境,MySQL无法启动,我无法访问任何内容。我只需要启动数据库,如果可能的话,我可以导出数据。请帮帮我。

root@localhost:~# systemctl start mysql
mysql.service的作业失败,因为控制进程以错误代码退出。
有关详细信息,请参见“systemctl status mysql.service”和“journalctl -xeu mysql.service”。

root@localhost:~# systemctl status mysql.service
× mysql.service - MySQL Community Server
     已加载: 已加载 (/lib/systemd/system/mysql.service; 已启用; 厂商预设: 已启用)
     活跃状态: 失败 (结果: 退出码) since 2023-06-22 12:07:18 UTC; 14秒前
    进程: 1641 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (退出码=已退出,状态=0/成功)
    进程: 1649 ExecStart=/usr/sbin/mysqld (退出码=已退出,状态=3)
   主进程: 1649 (退出码=已退出,状态=3)
     状态: "Server startup in progress"
        CPU: 294ms

2023-06-22 12:07:18 localhost systemd[1]: mysql.service: Main process exited, code=exited, status=3/NOTIMPLEM>
2023-06-22 12:07:18 localhost systemd[1]: mysql.service: Failed with result 'exit-code'.
2023-06-22 12:07:18 localhost systemd[1]: Failed to start MySQL Community Server.
2023-06-22 12:07:18 localhost systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.
2023-06-22 12:07:18 localhost systemd[1]: Stopped MySQL Community Server.
2023-06-22 12:07:18 localhost systemd[1]: mysql.service: Start request repeated too quickly.
2023-06-22 12:07:18 localhost systemd[1]: mysql.service: Failed with result 'exit-code'.
2023-06-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] 无法继续操作。
2023-06-22T11:45:30.932065Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld(mysqld 8.0.33-0ubuntu0.22.04.2)正在启动,作为进程1347。
2023-06-22T11:45:30.937894Z 1 [System] [MY-013576] [InnoDB] InnoDB 初始化已启动。
2023-06-22T11:45:31.003681Z 1 [ERROR] [MY-012592] [InnoDB] 在文件操作中操作系统错误号为2。
2023-06-22T11:45:31.003698Z 1 [ERROR] [MY-012593] [InnoDB] 此错误表示系统无法找到指定的路径。
2023-06-22T11:45:31.003703Z 1 [ERROR] [MY-012594] [InnoDB] 如果您正在安装InnoDB,请记住您必须自己创建目录,InnoDB 不会创建它们。
2023-06-22T11:45:31.003709Z 1 [ERROR] [MY-012646] [InnoDB] 文件./ibdata1:'open' 返回操作系统错误71。无法继续操作。
2023-06-22T11:45:31.003717Z 1 [ERROR] [MY-012981] [InnoDB] 无法继续操作。
2023-06-22T11:45:31.443411Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld(mysqld 8.0.33-0ubuntu0.22.04.2)正在启动,作为进程1369。
2023-06-22T11:45:31.448765Z 1 [System] [MY-013576] [InnoDB] InnoDB 初始化已启动。
2023-06-22T11:45:31.507675Z 1 [ERROR] [MY-012592] [InnoDB] 在文件操作中操作系统错误号为2。
2023-06-22T11:45:31.507693Z 1 [ERROR] [MY-012593] [InnoDB] 此错误表示系统无法找到指定的路径。
2023-06-22T11:45:31.507698Z 1 [ERROR] [MY-012594] [InnoDB] 如果您正在安装InnoDB,请记住您必须自己创建目录,InnoDB 不会创建它们。
2023-06-22T11:45:31.507713Z 1 [ERROR] [MY-012646] [InnoDB] 文件./ibdata1:'open' 返回操作系统错误71。无法继续操作。
2023-06-22T11:45:31.507718Z 1 [ERROR] [MY-012981] [InnoDB] 无法继续操作。
2023-06-22T12:07:15.741932Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld(mysqld 8.0.33-0ubuntu0.22.04.2)正在启动,作为进程1564。
2023-06-22T12:07:15.750132Z 1 [System] [MY-013576] [InnoDB] InnoDB 初始化已启动。
2023-06-22T12:07:15.815802Z 1 [ERROR] [MY-012592] [InnoDB] 在文件操作中操作

<details>
<summary>英文:</summary>

I know i am dumb, but it&#39;s a production database and i am in huge trouble,
I wanted to access the db and i forgot the mysql password, one thing led me to another,
till i deleted the ibdata1 (thanks to chatgpt) and now i am in a pinch, mysql doesn&#39;t start and i can&#39;t access anything.
I just need to start the database so i can export the data if it&#39;s possible.
Please help me.

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.


</details>
# 答案1
**得分**: 1
首先,感谢所有为我的问题提供帮助的人,我愚蠢到忘记制作备份,但无论如何,我已经找回了我的数据。
我通过sftp将.ibd文件发送到Windows上,mysql正常运行,然后创建了一个新的数据库,从github拉取了我的项目,应用了迁移,然后将导入的.ibd文件替换了原来的文件。最后,我使用SQL Alter语句逐个导入了所有表的表空间,这样我就找回了我的数据:)
对于阅读这个的任何人,请立即备份你的数据库。你真的不想感受到那种痛苦。
<details>
<summary>英文:</summary>
Well first of all thanks to everyone who gave time for my issue, I was dumb enough to forget ab making a backup but anyways I got my data back.
I sent the .ibd files via sftp into windows where mysql is working well, then created a new database, pulled my project from github, applied the migrations then changed the .ibd files with the ones that I imported. finally I Imported the tablespaces of all the tables one by one using the SQL Alter statement, which got my data back :) 
For anyone reading this please backup your databases rn. you really don&#39;t wanna feel that pain
</details>

huangapple
  • 本文由 发表于 2023年6月22日 20:10:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/76531749.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定