如何检查 MySQL 的完整性？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4295629/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check MySQL integrity?
提问by qliq
I have a big MySQL 5 Database for a drupal site on an old machine. After backing up and restoring on a fresh database, I got:
我在旧机器上有一个用于 drupal 站点的大型 MySQL 5 数据库。在新数据库上备份和恢复后，我得到：
ERROR 1064 (42000) at line **: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''captcha_success_form_ids|a:1:{s:13:\"user_register\";s:13:\"user_register\";}sp' at line 1
ERROR 1064 (42000) at line **: 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 ''captcha_success_form_ids|a:1:{s:13:\"user_register\";s:13:\"user_register\";}sp' 附近使用的正确语法1号线
I am not sure which table is the origion of this error (I have no table called 'user_register'). So I am wondering how can I have a quick check of the original database for integrity before making another failed backup/restore effort? (I have command line access). Thanks
我不确定哪个表是此错误的来源（我没有名为“user_register”的表）。所以我想知道如何在进行另一次失败的备份/恢复工作之前快速检查原始数据库的完整性？（我有命令行访问权限）。谢谢
采纳答案by symcbean
The fragment quoted in the error message looks nothing like MySQL - it looks like serialised PHP data which makes me think you're not escaping your data properly in the code. Has your PHP installation changed? Particularly the smartquotes? (note smartquotes is now deprecated).
错误消息中引用的片段看起来不像 MySQL - 它看起来像序列化的 PHP 数据，这让我认为您没有在代码中正确地转义数据。你的 PHP 安装改变了吗？特别是智能报价？（注意 smartquotes 现在已弃用）。
回答by cbranch
If you suspect corruption in the original database, you can do the following:
如果您怀疑原始数据库损坏，您可以执行以下操作：
For MyISAM tables:
对于 MyISAM 表：
CHECK TABLE <table_name>;
REPAIR TABLE <table_name>;
For InnoDB tables:
对于 InnoDB 表：
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/
http://www.mysqlperformanceblog.com/2008/07/04/recovering-innodb-table-corruption/
I've personally seen corruption with MyISAM tables on a few rare occasions that CHECK and REPAIR was able to detect and correct. I've never experienced corruption with InnoDB tables, so can't speak from personal experience regarding the info provided in the link.
我个人在少数情况下看到 MyISAM 表损坏，CHECK 和 REPAIR 能够检测和纠正。我从未经历过 InnoDB 表的损坏，因此无法从个人经验中谈论链接中提供的信息。
That said, if I were in your shoes, I would start by taking a closer look at the output file produced by mysqldump to see if I could pinpoint the source of the error. mysqldump usually outputs a single INSERT statement per table with all of the data on a single line, so it's a little tricky to diagnose errors because the line number included with the error message does not help much. So, what I would do is to edit the mysqldump output file and insert newlines between each row. For example:
也就是说，如果我是你的话，我会首先仔细查看 mysqldump 生成的输出文件，看看我是否可以查明错误的来源。mysqldump 通常为每个表输出一个 INSERT 语句，所有数据都在一行上，因此诊断错误有点棘手，因为错误消息中包含的行号没有多大帮助。所以，我要做的是编辑 mysqldump 输出文件并在每行之间插入换行符。例如：
Original:
原来的：
INSERT INTO table VALUES (a,b,c),(d,e,f),(g,h,i), ...
Change To:
改成：
INSERT INTO table VALUES (a,b,c),
(d,e,f),
(g,h,i),
...
Since you're comfortable with the command line, you could probably automate this with sed, etc.
由于您对命令行很熟悉，因此您可以使用 sed 等自动执行此操作。
Then, try to import the modified file. You should get the same error, but this time the line number will help pinpoint the exact row that's causing the problem. At that point, you should be able to diagnose the problem (or post the offending portion of the mysqldump output here and we'll try to help).
然后，尝试导入修改后的文件。您应该得到相同的错误，但这次行号将有助于查明导致问题的确切行。那时，您应该能够诊断问题（或在此处发布 mysqldump 输出的违规部分，我们将尽力提供帮助）。
EDIT:
编辑：
Is the error message you cited happening when you importthe database? Or does the database import successfully, but the application produces the error message when accessing the database? I was assuming the former, but now thinking it may be the latter after re-reading your question.
导入数据库时是否发生了您引用的错误消息？还是数据库导入成功，但是应用程序访问数据库时出现错误信息？我假设是前者，但现在重新阅读您的问题后认为可能是后者。
One more idea: Does your database include stored procs? If so, mysqldump will not include those by default. You need to use the --routinesoption:
还有一个想法：您的数据库是否包含存储过程？如果是这样，默认情况下 mysqldump 将不包括那些。您需要使用--routines选项：
mysqldump --routines -u <user> -p<password> <database> > output
