TABLESPACE在哪里

转载

mob6454cc76bc4a 2024-09-12 08:18:28

文章标签 TABLESPACE在哪里 mysql 数据库数据恢复 MySQL 文章分类 架构后端开发

事情的起因

首先前一天晚上我跑了一个not in语法的SQL语句，因为正好是下班了就让它自己跑去了，结果一不小心造成了笛卡尔积，第二天发现空间爆了，于是开始正常标准操作：暂停，查看进程，发现InnoDB正在回滚，因为还有很多工作想让他快一点，我就重启了MySQL。。。于是悲剧就开始了

事情的经过

重启之后提示mysql起不来，查看状态，报错状态码3.查看错误日志

2020-09-28T02:13:01.909970Z 0 [ERROR] InnoDB: Tablespace 1830 was not found at ./area_code_all/t_company_name_out.ibd.
2020-09-28T02:13:01.909975Z 0 [ERROR] InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace.
2020-09-28T02:13:01.911058Z 0 [ERROR] InnoDB: Cannot continue operation.

原来是有个表的表空间找不到了导致无法启动那就按照提示设置一下innodb_force_recovery的参数试着能不能解决问题于是在配置文件里添加innodb_force_recovery=1，同时我还在百度上搜索了相关问题发现大家都在用这个参数。。。以为可以解决的我满心欢喜的再次启动，竟然没报错，但是登陆不上了，Navicat报错是未知错误，于是用控制台登陆发现还是登陆不上，报错信息如下：

XXX@XXXX:~# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

说找不到这个文件，于是我就开始使用find方法找这个文件：find / -name '*.sock'

但是神奇的是！！！！没有找到这个文件o(╥﹏╥)o

不死心的我又打开了日志

2020-09-28T06:53:04.777477Z 0 [Note] InnoDB: At LSN: 966368842507: unable to open file ./area_code_all/t_company_name_out.ibd for tablespace 1830
2020-09-28T06:53:05.024702Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 966372808704
2020-09-28T06:53:05.360232Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 966378051584
2020-09-28T06:53:05.582569Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 966381543104
2020-09-28T06:53:05.583703Z 0 [Note] InnoDB: Database was not shutdown normally!
2020-09-28T06:53:05.583708Z 0 [Note] InnoDB: Starting crash recovery.
2020-09-28T06:53:05.583716Z 0 [Warning] InnoDB: Tablespace 1830 was not found at ./area_code_all/t_company_name_out.ibd, and innodb_force_recovery was set. All redo log for this tablespace will be ignored!

并没有什么有用信息啊，于是我想会不会是innodb_force_recovery的参数不对于是改了2，又改了3，但是还是打不开。。。心灰意冷的我又把关键词扔到百度上查

还真让我查到了，是一个人在dba.stackexchange.com上发的，然后就不贴出来了，反正里面说他/她用innodb_force_recovery=1，然后增加了参数，从1一直试到了6，终于在innodb_force_recovery=6吧数据库打开了。。。（果然祖师爷比较青睐有毅力尝试和有毅力查百度的人）
于是我也一路尝试试到了6。。。数据库终于打开了，简单看看数据并没有丢失，但是知道innodb_force_recovery参数的小伙伴应该清楚，当参数大于4时就是InnoDB类型的数据库就是只读类型了没法建表删表等操作。

于是我备份了所有的库和表将危害降到最小

但是因为我这是大数据集群，很多组件都连着这个MySQL，因为之前不是我建的所以有一些用户密码都不知道，也不知道都有哪些程序是连接这个MySQL的，所以卸载重装很费劲，之后我在别的节点建立备用库，重启Hadoop等组件发现没有数据丢失，然后不死心的我只能接着查看日志。。。

2020-09-28T07:32:15.644299Z 19 [ERROR] InnoDB: Failed to find tablespace for table `area_code_all`.`t_company_name_out` in the cache. Attempting to load the tablespace with space id 1830
2020-09-28T07:32:15.644375Z 19 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-09-28T07:32:15.644389Z 19 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-09-28T07:32:15.644397Z 19 [ERROR] InnoDB: Cannot open datafile for read-only: './area_code_all/t_company_name_out.ibd' OS error: 71
2020-09-28T07:32:15.644406Z 19 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-09-28T07:32:15.644413Z 19 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-09-28T07:32:15.644421Z 19 [ERROR] InnoDB: Could not find a valid tablespace file for `area_code_all/t_company_name_out`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

细心的小伙伴应该发现了里面的网址，竟然是MySQL自己出的问题解决文档，心中的希望再次燃起，看过网址之后里面就说了创建表再删除表然后就正常了，可是我现在只能读啊。。。仔细研究一顿我发现了这个。。。

TABLESPACE在哪里_MySQL

TABLESPACE在哪里_mysql_02

于是我来了一波令人窒息的骚操作，的确最后窒息了。。。我在我本地的mysql里创建了一个同样的数据库和一个同样的数据表，还细心的插入了几条同样的数据，然后把data文件夹里生成的文件复制到我服务器上，并把innodb_force_recovery的参数改回了1，再次启动MySQL，虽然启动起来了遗憾的是登录的时候还是报了之前的错误，再次查看日志。。。

2020-09-28T08:00:09.030551Z 4 [ERROR] InnoDB: In file './area_code_all/t_company_name_out.ibd', tablespace id and flags are 7696 and 0, but in the InnoDB data dictionary they are 1830 and 33. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2020-09-28T08:00:09.030580Z 4 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-09-28T08:00:09.030585Z 4 [ERROR] InnoDB: The error means the system cannot find the path specified.
2020-09-28T08:00:09.030589Z 4 [ERROR] InnoDB: Could not find a valid tablespace file for `area_code_all/t_company_name_out`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2020-09-28T08:00:09.030616Z 4 [Warning] InnoDB: Cannot calculate statistics for table `area_code_all`.`t_company_name_out` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

这次告诉我找到它想要的文件了，但是打不开。。。我心中的有千万匹羊驼飞驰而过，继续看他给的网址寻找解决办法。。。

TABLESPACE在哪里_数据恢复_03