在使用MyISAM的MySQL的REPAIR TABLE期间，什么会导致数据丢失? [英] What would cause data loss during a REPAIR TABLE in MySQL using MyISAM?
问题描述
修复表的MySQL文档
最好对表进行备份 进行桌子维修之前 手术;在某些情况下 该操作可能会导致数据丢失. 可能的原因包括但不是 仅限文件系统错误.
除了文件系统错误之外，我想知道是否还有其他导致数据丢失的原因.有人看到过这种情况在野外发生吗?如果没有文件系统错误，修复将丢失数据的可能性有多大?
我的具体情况如下.我有运行Solaris 10(SPARC)的Sun T5120服务器，正在使用MySQL 5.1.30.我有一个使用MyISAM引擎的表，该表有时会损坏.有时表已损坏是由于我们没有UPS的开发系统发生意外断电所致.我不确定是否所有损坏都是由于断电引起的，所以可能有其他一些原因导致这种情况发生.例如此处列出的原因. /p>
我想设置一个自动修复解决方案，以防在生产系统中发生这些可疑的其他原因"，或者生产UPS发生故障.我可以按照建议的那样，设置cron作业以运行mysqlcheck --auto-recover答案，或者我可以修改插入该表中的进程以在检测到损坏时立即执行REPAIR TABLE EXTENDED命令.但是，这两种方法都使用REPAIR TABLE，因此很容易丢失数据.
如文档所示，我可以在尝试修复之前备份表，但是表很大，我不确定是否有足够的空间用于备份.我已经进行了一些搜索，但是除了文档中提到的内容外，还没有找到任何解释为什么REPAIR TABLE会导致数据丢失的原因.那么，当您使用完善的文件系统时，维修是否有可能会丢失数据，或者仅仅是出于谨慎考虑?
MySQL手册中列出的可能原因之一是软件中的错误.您应该阅读版本说明中的发行说明/更改历史记录，以查看修复表代码中的所有错误是否已得到修复，并且还应阅读新版本发行说明.
出于兴趣，您的插入数据的过程如何检测到损坏?
您可以采取的另一种防止数据丢失的措施是进行备份并启用复制日志.万一发生故障，您可以从备份中还原，然后使用复制日志将数据库恢复到崩溃时的状态.
最终，如果这是一个生产系统，那么您确实需要挑选出良好的电源，并且还要有第二台机器充当副本.
The MySQL documentation for REPAIR TABLE states that
It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to file system errors.
I'd like to know if there are any other causes of data loss besides file system errors. Has anyone seen this happen in the wild? How likely is it that the repair will lose data if there are no file system errors?
My specific situation is as follows. I have Sun T5120 server running Solaris 10 (SPARC) and am using MySQL 5.1.30. I have a table which uses the MyISAM engine which occasionally becomes corrupted. Some of the times that the table has been corrupted have been due to unexpected power outages on our development system which didn't have an UPS. I'm not sure that all corruption was due to power outages, so there may be some additional reasons that this is happening. Such as the causes listed here.
I'd like to setup an automatic repair solution in case these suspected "additional reasons" happen in the production system, or the production UPS fails. I could set a cron job to run mysqlcheck --auto-recover as is suggested in this answer, or I could modify my process that is inserting into that table to instantly perform the REPAIR TABLE EXTENDED command when it detects corruption. However, both of those approaches use REPAIR TABLE and are, thus, susceptible to data loss.
I could backup the table before attempting the repair, as the documentation suggests, but the table is rather large and I'm not sure I will have space available for the backup. I've done some searching, but haven't found any explanation of why REPAIR TABLE would cause data loss besides what is mentioned in the documentation. So is it likely that the repair will lose data when you have a sound file system, or is the documentation just being cautious?
One of the listed possible causes in the MySQL manual is a bug in the software. You should read the release notes / change history from your version forwards to see if any bugs in the repair table code have been fixed, and also read the release notes for new versions as they come out.
Out of interest, how does your process that inserts data detect corruption ?
An additional measure you can take to protect against data loss is to take backups and enable the replication log. In the event of failure, you can restore from the backup and then use the replication logs to bring your database back to the state it was in when it crashed.
Ultimately, if this is a production system, you really need to sort out a good power supply, and also have a second machine acting as a replica.
这篇关于在使用MyISAM的MySQL的REPAIR TABLE期间，什么会导致数据丢失?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
