数据库表损坏，如何修复？ (数据库表损坏怎么修复)
数据库表的损坏并不是一个难以预料的事情，也不是一个特别罕见的事情，尤其是在操作系统或者存储提供者出现故障时。在这种情况下，损坏的表可能会极大地影响您的业务，甚至导致数据丢失。为了保障您的业务，及时修复数据库表非常重要。
本文将会详细阐述如何修复损坏的数据库表。备份是修复损坏的数据库表的关键。避免数据损坏的更好方法之一是创建定期的备份，以便可以在需要时使用。在数据损坏的情况下，备份可以作为最后的手段进行修复。
1. 检查表损坏
在数据库运行时，如果遇到如下错误，则表可能已经受损了。
“Error 1034: The table ‘table name’ is marked as crashed and should be repred”
“Error 144: Table ‘table name’ is marked as crashed and last (automatic?) repr fled”
这些错误通常是由操作系统或存储设备的问题导致的。在Table修复被执行之前，我们需要进行一些检查。
我们需要确认表是否已经受到损坏。在命令行执行以下命令：
mysqlcheck –all-databases
如果输出为空，表格就没有受到损坏。
如果输出为“OK”，则数据库表可能没有损坏。如果输出为“Warning”或“Error”，存储引擎可能已经损坏，并且需要修复。
如果您已经确定表格损坏，接下来我们需要修复它。
2. 修复表
如果数据表损坏，您可以使用以下命令来修复它：
mysqlcheck -r database_name table_name
一旦您输入上述命令，MySQL就会尝试修复表。这个过程可以花费一段时间，具体时间取决于表格的大小和损坏的程度。
如果MySQL成功地修复了数据表，`mysqlcheck`命令的输出将是以下内容：
database_name.table_name repr status okay
如果MySQL无法修复数据表，命令的输出将与之前的警告一样。
3. 恢复数据
如果使用备份进行修复，考虑你需要覆盖损坏的数据表，并且删除无用的数据表。在MySQL中，您可以使用以下命令来恢复数据：
mysql –u[username] –p[password] [database_name]
##重要提醒##
在使用该命令导入数据之前，一定要备份数据库。您可以使用以下命令进行备份：
mysqldump –u[username] –p[password] [database_name]> [backup_filename].sql
导入备份文件后，您必须使用以下命令指定您想要使用的数据表：
USE database_name;
RENAME TABLE table_name TO tmp_table_name;
RENAME TABLE backup_table_name TO table_name;
DROP TABLE tmp_table_name;
这个操作将备份的表重命名为需要修复的表。输出的结果将是：
‘database_name.table_name’ was renamed to ‘database_name.tmp_table_name’
‘database_name.backup_table_name’ was renamed to ‘database_name.table_name’
这时，通过删除旧表并重命名备份表，您可以进行数据库恢复。使用以下命令删除旧表：
DROP TABLE tmp_table_name;
通过使用这个过程，您应该可以成功地修复数据表。但是，如果以上方式不能解决问题，请考虑联系专业数据库修复公司。在关键时刻，数据是最宝贵的财产。因此，在像数据表损坏这样的问题时，为自己的数据保障备份、恢复和修复都是非常重要的。
相关问题拓展阅读：
怎样修复损坏了的innodb 表
InnoDB表损坏
InnoDB拥有内部恢复机制，假如数据库崩溃了，InnoDB通过从最后一个时间戳开始运行日志文件毕蠢兄，来尝试修复数据库。
大多数情况下会修复成功，而且整个过程是透明的。
假如InnoDB自行修复失败，那么数据库将不能启动。
在继续操作前，先浏览下MySQL的日志文件，确定数据库是因为InnoDB表的损坏而崩溃。
有一种方法是更新InnoDB的日志文件计数器以跳过引起崩溃的查询，这种情况下，手袭将造成数据的不一致性而且会经常使主从复制中断。
一旦确定MySQL因为InnoDB表损坏无法启动时，就可以按照以下5步进行修复：
1.添加如下配置到/etc/my.cnf文件中
innodb_force_recovery = 4
2.这时就可以重新启动数据库了，在innodb_force_recovery配置的作用，所有的插入与更新操作将被忽略;
3.导出所有的数据表;
4.关闭数据档闭库并删除所有数据表文件及目录，再运行 mysql_install_db来创建MySQL默认数据表;
5.在/etc/my.cnf中删除innodb_force_recovery这一行，再启动MySQL（这时MySQL正常启动）;
6.从第3步备份的文件中恢复所有的数据。
– 恢复策略
前面说到未提交的事务和回滚了的事务也会记录Redo Log，因此在进行恢复时,这些事务要进行特殊的的处理.有2中不同的恢复策略：
A. 进行恢复时，只重做已经提交了的事务。
B. 进行恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些未提交的事务。
– InnoDB存储引擎的恢复机制
MySQL数据库InnoDB存储引擎使用了B策略, InnoDB存储引擎中的恢复机制有几个特点：
A. 在重做Redo Log时，并不关心事务性。 恢复时，没有BEGIN，也没有COMMIT,ROLLBACK的行为。也不关心每个日志是哪个事务的。尽管事务ID等事务相关的内容会记入胡基Redo Log，这些内容只是被当作要操作的数据的一部分。
B. 使用B策略就必须要将Undo Log持久化，而且必须要在写Redo Log之前将对应的Undo Log写入磁盘。Undo和Redo Log的这种关联，使得持久化变得复杂起来。为了降裤键谨低复杂度，InnoDB将Undo Log看作数据，因此记录Undo Log的操作也会记录到redo log中。这样undo log就可以象数据一样缓存起来，而不用在redo log之前写入磁盘了。
包含Undo Log操作的Redo Log，看起来是这样的：
记录1: >
记录2:
记录3: >
记录4:
记录5: >
记录6:
C. 到这里，还有一个问题没有弄清楚。既然Redo没有事务性，那岂不是会重新执行被回滚了的事务？确实是这样。同时Innodb也会将事务回滚时的操作也记录到redo log中。回滚操作本质上也是对数据进行修改，因此回滚时对数据的操作也会记录到Redo Log中。
一个回滚了的事务的Redo Log，看起来是这样的：
记录1: >
记录2:
记录3: >
记录4:
记录5: >
记录6:
记录7:
记录8: 亮岩
记录9:
一个被回滚了的事务在恢复时的操作就是先redo再undo，因此不会破坏数据的一致性.
– InnoDB存储引擎中相关的函数
Redo: recv_recovery_from_checkpoint_start()
Undo: recv_recovery_rollback_active()
Undo Log的Redo Log: trx_undof_page_add_undo_rec_log()
ssume you’re running MySQL with Innodb tables and you’ve got crappy hardware, driver bug, kernel bug, unlucky power failure or some rare MySQL bug and some pages in Innodb tablespace got corrupted. In such cases Innodb will typically print something like this:
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
:46:16 InnoDB: Page dump in ascii and hex (16384 bytes):
… A LOT OF HEX AND BINARY DATA…
:46:16 InnoDB: Page checksum, prior-to-4.0.14-form checksum
InnoDB: stored checksum, prior-to-4.0.14-form stored checksum
InnoDB: Page lsn, low 4 bytes of lsn at page end
InnoDB: Page number (if stored to page already) 7,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 6353
InnoDB: Page may be an index page where index id is
InnoDB: (index “PRIMARY” of table “test”.”test”)
InnoDB: Database page corruption on disk or a failed
and crash with assertion failure.
So what can you do to recover such a table ?
There are multiple things which can get corrupted and I will be looking in details on the simple one in this article – when page in clustered key index is corrupted. It is worse compared to having data corrupted in secondary indexes, in which case simple OPTIMIZE TABLE could be enough to rebuild it, but it is much better compared to table dictionary corruption when it may be much harder to recover the table.
In this example I actually went ahead and manually edited test.ibd file replacing few bytes so corruption is mild.
First I should note CHECK TABLE in INNODB is pretty useless. For my manually corrupted table I am getting:
mysql> CHECK TABLE test;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
mysql> CHECK TABLE test;
+++++
| TABLE | Op | Msg_type | Msg_text
+++++
| test.test | CHECK | STATUS | OK
+++++
1 row IN SET (0.69 sec)
First run is check table in normal operation mode – in which case Innodb simply crashes if there is checksum error (even if we’re running CHECK operation). In second case I’m running withinnodb_force_recovery=1 and as you can see even though I get the message in the log file about checksum failing CHECK TABLE says table is OK. This means You Can’t Trust CHECK TABLE in Innodb to be sure your tables are good.
In this simple corruption was only in the data portion of pages so once you started Innodb withinnodb_force_recovery=1 you can do the following:
mysql> CREATE TABLE `test2` (
-> `c` char(255) DEFAULT NULL,
-> `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test2 SELECT * FROM test;
Query OK,rows affected (0.91 sec)
Records:Duplicates: 0 Warnings: 0
Now you got all your data in MyISAM table so all you have to do is to drop old table and convert new table back to Innodb after restarting without innodb_force_recovery option. You can also rename the old table in case you will need to look into it more later. Another alternative is to dump table with MySQLDump and load it back. It is all pretty much the same stuff. I’m using MyISAM table for the reason you’ll see later.
You may think why do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode Innodb becomes read only for data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables):
mysql> OPTIMIZE TABLE test;
++++——+
| TABLE | Op| Msg_type | Msg_text
++++——+
| test.test | OPTIMIZE | error | Got error -1 FROM storage engine
| test.test | OPTIMIZE | STATUS | Operation failed
++++——+
2 rows IN SET, 2 warnings (0.09 sec)
That was easy, right ?
I also thought so, so I went ahead and edited test.ibd a little more wiping one of the page headers completely. Now CHECK TABLE would crash even with innodb_force_recovery=1
:22:53 InnoDB: Assertion failure in threadin file btr/btr0btr.c line 3235
InnoDB: Failing assertion: page_get_n_recs(page) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index))
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
InnoDB: If you get repeated assertion failures or crashes, even
If you get such assertion failures most likely higher innodb_force_recovery values would not help you – they are helpful in case there is corruption in various system areas but they can’t really change anything in a way Innodb processes page data.
The next comes trial and error approach:
mysql> INSERT INTO test2 SELECT * FROM test;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
You may think will will scan the table until first corrupted row and get result in MyISAM table ? Unfortunately test2 ended up to be empty after the run. At the same time I saw some data could be selected. The problem is there is some buffering taking place and as MySQL crashes it does not store all data it could recover to MyISAM table.
Using series of queries with LIMIT can be handly if you recover manually:
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 10;
Query OK, 10 rows affected (0.00 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 20;
Query OK, 10 rows affected (0.00 sec)
Records: 20 Duplicates: 10 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 100;
Query OK, 80 rows affected (0.00 sec)
Records: 100 Duplicates: 20 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 200;
Query OK, 100 rows affected (1.47 sec)
Records: 200 Duplicates: 100 Warnings: 0
mysql> INSERT IGNORE INTO test2 SELECT * FROM test LIMIT 300;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
As you can see I can get rows from the table in the new one until we finally touch the row which crashes MySQL. In this case we can expect this is the row between 200 and 300 and we can do bunch of similar statements to find exact number doing “binary search”
Note even if you do not use MyISAM table but fetch data to the script instead make sure to use LIMIT or PK Rangers when MySQL crashes you will not get all data in the network packet you potentially could get due to buffering.
So now we found there is corrupted data in the table and we need to somehow skip over it. To do it we would need to find max PK which could be recovered and try some higher values
mysql> SELECT max(id) FROM test2;
++
| max(id)
++
|
++
1 row IN SET (0.00 sec)
mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>250;
ERROR 2023 (HY000): Lost connection TO MySQL server during query
mysql> INSERT IGNORE INTO test2 SELECT * FROM test WHERE id>300;
Query OK,rows affected (7.79 sec)
Records:Duplicates: 0 Warnings: 0
So we tried to skip 30 rows and it was too little while skipping 80 rows was OK. Again using binary search you can find out how many rows do you need to skip exactly to recover as much data as possible. Row size can be good help to you. In this case we have about 280 bytes per row so we get about 50 rows per page so not a big surprise 30 rows was not enough – typically if page directory is corrupted you would need to skip at least whole page. If page is corrupted at higher level in REE you may need to skip a lot of pages (whole subtree) to use this recovery method.
It is also well possible you will need to skip over few bad pages rather than one as in this example.
Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make sure indexes are not corrupted.
So we looked at how to get your data back from simple Innodb Table Corruption. In more complex cases you may need to use higher innodb_force_recovery modes to block purging activity, insert buffer merge or recovery from transactional logs all together. Though the lower recovery mode you can run your recovery process with better data you’re likely to get.
In some cases such as if data dictionary or “root page” for clustered index is corrupted this method will not work well – in this case you may wish to use Innodb Recovery Toolkit which is also helpful in cases you’ve want to recover deleted rows or dropped table.
I should also mention at Percona we offer assistance in MySQL Recovery, including recovery from Innodb corruptions and deleted data.
SQL SERVER中一张表损坏了，没有备份文件，如何将这张表修复一下呢？在线等待
–先检查是否有报错
dbcc checktable(‘表名’)
–有报错，就修复租困
use master
GO
— sp_dboption 用于显示或更改数弊虚念据库选项。当为 single user 选项为 true 时，每次只能有一个用户访问数据库。
sp_dboption ‘数据库名’,’single user’,’true’
DBCC CHECKDB(‘数据库名’誉薯,REPAIR_ALLOW_DATA_LOSS)
sp_dboption ‘数据库名’,’single user’,’false’
从你的出错来看拿氏“ 0 个分配错误和 1 个一致性错误”可能是索引问题，一致性出错多数情况是索引问题，你可以drop 所有索引，再检查一下表。
另数据库比较保守的修复方法是
DBCC CHECKDB(‘数据库名’,REPAIR_REBUILD )
因为弊闭REPAIR_ALLOW_DATA_LOSS
尝试修复报告的所有错误。这些修复可能会导致一些租敏裂数据丢失。
REPAIR_REBUILD
执行不会丢失数据的修复。这包括快速修复（如修复非聚集索引中缺少的行）以及更耗时的修复（如重新生成索引）。
试下能不能把表的数据导出来唯友select * into temp from table,然後重御庆新该表，再将会数据导回。镇山握
数据库表损坏怎么修复的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于数据库表损坏怎么修复,数据库表损坏，如何修复？,怎样修复损坏了的innodb 表,SQL SERVER中一张表损坏了，没有备份文件，如何将这张表修复一下呢？在线等待的信息别忘了在本站进行查找喔。
