英文:
deleted ibdata1 in mysql and I am in a huge trouble (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'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't start and i can't access anything.
I just need to start the database so i can export the data if it'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't wanna feel that pain
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论