MySQL 一键修复所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4582832/
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
Repair all tables in one go
提问by AMD
How to check all the tables in the database in one go?
如何一次性查看数据库中的所有表？
Rather than typing the query check table ''tablename'';for all the tables one by one.
而不是check table ''tablename'';一一键入所有表的查询。
Is there any simple command like check allor anything like that?
有没有类似的简单命令check all或类似的东西？
回答by Gu1234
from command line you can use:
从命令行你可以使用：
mysqlcheck -A --auto-repair
回答by Constantin Galbenu
The command is this:
命令是这样的：
mysqlcheck -u root -p --auto-repair --check --all-databases
You must supply the password when asked,
您必须在询问时提供密码，
or you can run this one but it's not recommended because the password is written in clear text:
或者你可以运行这个，但不推荐这样做，因为密码是用明文写的：
mysqlcheck -u root --password=THEPASSWORD --auto-repair --check --all-databases
回答by SenthilKumar
Use following query to print REPAIRSQL statments for all tables inside a database:
使用以下查询打印REPAIR数据库中所有表的 SQL 语句：
select concat('REPAIR TABLE ', table_name, ';') from information_schema.tables
where table_schema='mydatabase';
After that copy all the queries and execute it on mydatabase.
之后复制所有查询并在 上执行它mydatabase。
Note: replace mydatabasewith desired DB name
注意：替换mydatabase为所需的数据库名称
回答by contactmatt
The following command worked for me using the command prompt (As an Administrator) in Windows:
以下命令在 Windows 中使用命令提示符（以管理员身份）对我有用：
mysqlcheck -u root -p -A --auto-repair
Run mysqlcheck with the root user, prompt for a password, check all databases, and auto-repair any corrupted tables.
以 root 用户运行 mysqlcheck，提示输入密码，检查所有数据库，并自动修复任何损坏的表。
回答by Crazy Serb
No need to type in the password, just use any one of these commands (self explanatory):
无需输入密码，只需使用以下任一命令（不言自明）：
mysqlcheck --all-databases -a #analyze
mysqlcheck --all-databases -r #repair
mysqlcheck --all-databases -o #optimize
回答by St.Woland
There is no default command to do that, but you may create a procedure to do the job.
It will iterate through rows of information_schemaand call REPAIR TABLE 'tablename';for every row. CHECK TABLEis not yet supported for prepared statements. Here's the example (replace MYDATABASE with your database name):
没有默认命令可以做到这一点，但您可以创建一个过程来完成这项工作。它将遍历行information_schema并调用REPAIR TABLE 'tablename';每一行。CHECK TABLE尚不支持准备好的语句。这是示例（将 MYDATABASE 替换为您的数据库名称）：
CREATE DEFINER = 'root'@'localhost'
PROCEDURE MYDATABASE.repair_all()
BEGIN
DECLARE endloop INT DEFAULT 0;
DECLARE tableName char(100);
DECLARE rCursor CURSOR FOR SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`=DATABASE();
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endloop=1;
OPEN rCursor;
FETCH rCursor INTO tableName;
WHILE endloop = 0 DO
SET @sql = CONCAT("REPAIR TABLE `", tableName, "`");
PREPARE statement FROM @sql;
EXECUTE statement;
FETCH rCursor INTO tableName;
END WHILE;
CLOSE rCursor;
END
回答by sjas
for plesk hosts, one of these should do: (both do the same)
对于 plesk 主机，其中一个应该做：（两者都做同样的事情）
mysqlrepair -uadmin -p$(cat /etc/psa/.psa.shadow) -A
# or
mysqlcheck -uadmin -p$(cat /etc/psa/.psa.shadow) --repair -A
回答by Mike Zriel
You may need user name and password:
您可能需要用户名和密码：
mysqlcheck -A --auto-repair -uroot -p
You will be prompted for password.
系统将提示您输入密码。
mysqlcheck -A --auto-repair -uroot -p{{password here}}
If you want to put in cron, BUT your password will be visible in plain text!
如果您想放入 cron，但您的密码将以纯文本形式显示！
回答by Laloi
If corrupted tables remain after
如果损坏的表在
mysqlcheck -A --auto-repair
try
尝试
mysqlcheck -A --auto-repair --use-frm
回答by jerrygarciuh
I like this for a simple check from the shell:
我喜欢这样从 shell 进行简单的检查：
mysql -p<password> -D<database> -B -e "SHOW TABLES LIKE 'User%'" \
| awk 'NR != 1 {print "CHECK TABLE "";"}' \
| mysql -p<password> -D<database>
