如何修复 InnoDB 表?

发布于 2024-07-06 23:29:34 字数 191 浏览 12 评论 0原文

昨晚我们的 Solaris MySQL 数据库引擎(显然)执行得很差。 至少有一些 InnoDB 表已损坏,事务日志中出现时间戳无序错误,以及有关索引损坏的特定错误。

我们知道可用于 MyISAM 表修复的工具,但找不到任何适用于 InnoDB 的工具。

旁注:尝试表优化(在我尝试重建损坏的索引时)会导致数据库服务器崩溃。

We (apparently) had poorly executed of our Solaris MySQL database engine last night. At least some of the InnoDB tables are corrupted, with timestamp out of order errors in the transaction log, and a specific error about the index being corrupted.

We know about the tools available for MyISAM table repairs, but cannot find anything for InnoDB.

Side note: attempting a table optimize (in my attempt to rebuild the corrupted index) causes the database server to crash.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(9

不离久伴 2024-07-13 23:29:34

首先停止服务器并对光盘进行映像。 只尝试一次是没有意义的。 然后看看这里

First of all stop the server and image the disc. There's no point only having one shot at this. Then take a look here.

墨落成白 2024-07-13 23:29:34

停止您的应用程序...或停止您的从站,以便不会添加新行

create table <new table> like <old table>;
insert <new table> select * from <old table>;
truncate table  <old table>;
insert <old table> select * from <new table>;

重新启动您的服务器或从站

stop your application...or stop your slave so no new rows are being added

create table <new table> like <old table>;
insert <new table> select * from <old table>;
truncate table  <old table>;
insert <old table> select * from <new table>;

restart your server or slave

可爱咩 2024-07-13 23:29:34

以下解决方案的灵感来自上述 Sandro 的提示。

警告:虽然它对我有用,但我不知道它是否对你有用。

我的问题如下:从表(我们称此表损坏)中读取某些特定行会使 MySQL 崩溃。 即使 SELECT COUNT(*) FROM Broken 也会杀死它。 我希望您在此表上有一个PRIMARY KEY(在下面的示例中,它是id)。

  1. 确保您有损坏的 MySQL 服务器的备份或快照(以防万一您想返回步骤 1 并尝试其他操作!)
  2. CREATE TABLEbroken_repair LIKEbroken;
  3. INSERTbroken_repair SELECT * FROM Broken WHERE id NOT IN (SELECT id FROM Broken_repair) LIMIT 1;
  4. 重复步骤 3,直到数据库崩溃(您可以使用 LIMIT 100000,然后使用较低的值,直到使用 < code>LIMIT 1 会使数据库崩溃)。
  5. 查看是否拥有所有内容(您可以将 SELECT MAX(id) FROM Brokenbroken_repair 中的行数进行比较)。
  6. 此时,我显然已经拥有了所有行(除了那些可能被 InnoDB 粗暴截断的行)。 如果您错过了某些行,可以尝试在 LIMIT 中添加 OFFSET

祝你好运!

The following solution was inspired by Sandro's tip above.

Warning: while it worked for me, but I cannot tell if it will work for you.

My problem was the following: reading some specific rows from a table (let's call this table broken) would crash MySQL. Even SELECT COUNT(*) FROM broken would kill it. I hope you have a PRIMARY KEY on this table (in the following sample, it's id).

  1. Make sure you have a backup or snapshot of the broken MySQL server (just in case you want to go back to step 1 and try something else!)
  2. CREATE TABLE broken_repair LIKE broken;
  3. INSERT broken_repair SELECT * FROM broken WHERE id NOT IN (SELECT id FROM broken_repair) LIMIT 1;
  4. Repeat step 3 until it crashes the DB (you can use LIMIT 100000 and then use lower values, until using LIMIT 1 crashes the DB).
  5. See if you have everything (you can compare SELECT MAX(id) FROM broken with the number of rows in broken_repair).
  6. At this point, I apparently had all my rows (except those which were probably savagely truncated by InnoDB). If you miss some rows, you could try adding an OFFSET to the LIMIT.

Good luck!

烂柯人 2024-07-13 23:29:34

在我的例子中,只需停止并重新启动 SQL 服务器就可以了。

Simply stopping and restarting the SQL server worked in my case.

吹泡泡o 2024-07-13 23:29:34

请参阅这篇文章:http://www.unilogica.com/mysql-innodb-recovery/(葡萄牙语)

解释了如何使用innodb_force_recoveryinnodb_file_per_table。 我在需要使用单个 ibdata1 恢复崩溃的数据库后发现了这一点。

使用innodb_file_per_table,InnoDB中的所有表都会创建一个单独的表文件,就像MyISAM一样。

See this article: http://www.unilogica.com/mysql-innodb-recovery/ (It's in portuguese)

Are explained how to use innodb_force_recovery and innodb_file_per_table. I discovered this after need to recovery a crashed database with a single ibdata1.

Using innodb_file_per_table, all tables in InnoDB will create a separated table file, like MyISAM.

心碎无痕… 2024-07-13 23:29:34

注意:如果您的问题是“innodb 索引被标记为损坏”!
然后,简单的解决方案就是删除索引并重新添加它们。 这可以很快解决,不会丢失任何记录,也不会重新启动或将表内容移动到临时表中并返回。

Note: If your issue is, "innodb index is marked as corrupted"!
Then, the simple solution can be, just remove the indexes and add them again. That can solve pretty quickly without losing any records nor restarting or moving table contents into a temporary table and back.

池予 2024-07-13 23:29:34

将表更改为 myisam,然后再次更改为 innodb,它对我来说适用于 1300 万张表(图像来自医院的 PACS)

Alter table to myisam and the alter again to innodb, it works for me with a 13 million table (images from a PACS in a hospital)

左岸枫 2024-07-13 23:29:34

步骤 1.

停止 MySQL 服务器

步骤 2.

将此行添加到 my.cnf(在 Windows 中称为 my.ini)

set-variable=innodb_force_recovery=6

步骤 3.

删除 ib_logfile0ib_logfile1

步骤 4.

启动 MySQL 服务器

步骤 5.

运行此命令:

mysqlcheck --database db_name table_name -uroot -p

成功修复崩溃的 innodb 表后,不要忘记从我的表中删除 #set-variable=innodb_force_recovery=6 .cnf,然后再次重新启动MySQL服务器。

Step 1.

Stop MySQL server

Step 2.

add this line to my.cnf ( In windows it is called my.ini )

set-variable=innodb_force_recovery=6

Step 3.

delete ib_logfile0 and ib_logfile1

Step 4.

Start MySQL server

Step 5.

Run this command:

mysqlcheck --database db_name table_name -uroot -p

After you have successfully fixed the crashed innodb table, don't forget to remove #set-variable=innodb_force_recovery=6 from my.cnf and then restart MySQL server again.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文