MySQL tables can crash quite easily, especially when your server unexpectedly shuts down or crashed, or when you're forced to copy data around when it's still being accessed.
Luckily, there's a simple command to automatically check, repair and optimize all tables in all databases when you're running a MySQL server on Linux / Unix / BSD.
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
That's all there is to it!
mysqlcheck is available since MySQL 3.23.38; if you haven't slept under a rock so long you might step onto your own beard, you can probably use it on your server.
Steffen on Tuesday 09-11-2010
Thanks a lot for sharing your experience, and saving me time. Hope, your entry will shift further up in google search results.
My situation, where it worked for:
- MySQL-Error 126: Error in index-table in file /tmp/blabla.myi
No Ops in Mysql were able and the above mentioned file didn't exist. Manually repair via phpmyadmin didn't succeed.
Copy and Pasing your command did it at once.
Thx
Steffen
James on Friday 28-01-2011
This saved my skin too, a firmware/driver conflict on the server caused it to blue screen and reboot. Our application had lots of database errors once it was back online and I started running the "REPAIR TABLE" command on tables I identfied were causing a problem. I thought that there must be a better way to check all db's and tables in one go and came across this. Worked perfectly thank you!
Tim Quax on Thursday 17-02-2011
I'm glad I could help out! Awesome that you got you're databases working again - corrupt data is a true hell.
Good luck with your further database administrating :)
SEOHelper on Monday 28-02-2011
Good post. Being able to optimize all mySQL databases at once (aka a single time) is extremely helpful for performance. Also, being able to repair all mySQL databases with the above command is very useful. Thanks for teaching us how to use mysql to repair all databases and to optimize all tables and optimize all databases. Resolved solution is very helpful.
Chadd on Friday 22-07-2011
Agreed. Optimize all mySQL databases. Helpful for performance. Repair all mySQL databases is useful. Learn how to use mysql to repair all databases and to optimize all tables and optimize all databases by reading this post.
Hope that gives you a bump.
C
Aivan on Thursday 22-09-2011
When I execute this.. I get
mysqlcheck doesn't support multiple contradicting commands.
please help
Tim Quax on Thursday 29-09-2011
@Aivan,
A new version of mysqlcheck declared certain switches cannot be used in combination with certain other switches.
As a result, one of the switches is likely to be mutually exclusive to one another.
Try this:
mysqlcheck -u root -p --auto-repair --optimize --all-databases
Please let me know if this works, so I can update the article :)
Mori on Wednesday 05-10-2011
Hi!
Why not simple and short:
mysqlcheck -Aor -u root -p ?
Tim Quax on Wednesday 12-10-2011
@Mori,
That would work too :) For the ones that don't know what the arguments stand for:
A = all databases
o = optimize
r = repair
shammeri on Saturday 24-12-2011
Hi,
I'm here just to thank you ,
your solution was perfect and helped us to fix our database.
Regards,
Huzoor Bux Panhwar on Tuesday 03-01-2012
I need to check only one data base please suggest how to.. :)
Austin Siewert on Thursday 12-01-2012
@Tim
I was having the same error Aivan and used the update command. Worked perfect!
I have this setup as a cron job to run weekly. Thanks for sharing.
Tim Quax on Monday 16-01-2012
@Huzoor,
Just replace the argument --all-databases with your databasename, e.g.:
mysqlcheck -u root -p --auto-repair --check --optimize your_db
Luke Rehmann on Sunday 11-03-2012
Thanks for the great article, running MySQL 5.5, I had to run the following command to get it to work...
mysqlcheck -u root -p --auto-repair --optimize --all-databases
Rolf Jansson on Wednesday 14-03-2012
Removing the --check worked for me.
Majdi on Thursday 05-04-2012
Hello
It says :
#1064 - 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 \'mysqlcheck -u root -p --auto-repair --check --optimize --all-databases\' at line 1
Can any one help me??
Tim Quax on Tuesday 10-04-2012
You can try removing the "--check" part like Rolf suggested. If that doesn't work, please post your MySQL version so we'll have something to work with.
Majdi on Saturday 05-05-2012
Hello Tim
have 2 servers both have the same error and both is MySQL 5.1
i am running this command by opening phpMyAdmin then click on SQL Run SQL query/queries on server "localhost":
is this correct ?
Regards
Majdi
Tim Quax on Tuesday 22-05-2012
Hi Majdi,
It's not a MySQL Query. It's a command you can enter in your terminal. If you don't have terminal access, but you do have PHPMyAdmin, you can try this:
Go to PHPMyAdmin, and select the databases you want repaired. Tick the tables you wish to repair, or simply click on "check all". And then click the "with selected" box, and select "repair table".
Hope this helps!
Henny Savenije on Sunday 27-05-2012
In Pleks 10 the following worked for me
mysqlcheck -uadmin -p`cat /etc/psa/.psa.shadow` --auto-repair --optimize --all-databases
Majdi on Wednesday 06-06-2012
Thanks Tim
throw SSH PuTTy i runn the command mysqlcheck -u root -p --auto-repair --check --optimize --all-databases and it works for all dbs
Thanks for your tips
Regards
Majdi
valdoorf on Wednesday 05-09-2012
long time looked for so easy way....
Thanx !
John Park on Wednesday 12-09-2012
Hi,
Its not possible to repair all the databases in one time. You need to repair each and every database separately. http://exchangeserver.tumblr.com/post/27540224026/repairs-mysql-server-database
Most common error numbers that indicate a problem with a table:
shell> perror 126 127 132 134 135 136 141 144 145
Use myisamchk -r tbl_name (-r means “recovery mode”). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.
Luis Antonio on Tuesday 18-09-2012
Thanks a lot, mate! :)
I don't know if it was posted here already, but if anyone wants to optimize a particular database use this:
mysqlcheck -u user -p --auto-repair --check --optimize database
Then enter password. That worked for me.
Tim Quax on Saturday 22-09-2012
@John,
You don't make any sense. Of course it's possible, the article explains very clearly what you have to do in order for it to work.
Have you read anything at all?
Anil on Thursday 08-11-2012
select concat('repair table ', table_name, ';') from information_schema.tables
where table_schema='mydatabase';
repairs all tables in mydatabase
Tim Quax on Wednesday 14-11-2012
Thanks for your input Anil :) That's a nice way of repairing one database as well.
Anil on Thursday 15-11-2012
Another simpler way to repair or optimize tables is to do it from inside phpmyadmin.
To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize.
A list with all the database's tables will appear. Tick the tables you wish to optimize, or simply click [Check All] to select all tables.
From the [With selected:] drop-down menu choose Optimize or Repair table
siddharth on Thursday 28-02-2013
its really helpful
siddharth on Thursday 28-02-2013
its really helpful thanking you
Alex on Friday 08-03-2013
This is great help indeed! Just one question:
How can I set the password here if I want to run this in a cron job, or is it not smart to run this in a cron job?
Liam on Sunday 31-03-2013
I have entered all the code examples on this page and they don't run. Am using MAMP 2.1.4 with PHP Version 5.4.10 and MySQL 5.5.9 and keep getting this error and others... any help appreciated?
ERROR 1064 (42000): 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 'mysqlcheck -u root -p --auto-repair --optimize --all-databases' at line 1
Juraj Puchký on Thursday 25-07-2013
Prepared this tutorial for you.
http://www.devtech.cz/knowledge-base/paid-kb/advanced-mysql/optimize-all-mysql-tables-by-shell
Wasi Masi on Tuesday 31-12-2013
Repair your MYSQL database and optimize all tables with Kernel for MYSQL database repair tool to repair MYSQL database and support with all version of windows.
Prem on Tuesday 30-12-2014
Thanks Tim,
I ran this command for repair the database and all done, but still appears "Error establishing a database connection"
am using Webmin 1.660 (Debain Linux 6.0)
asdasd on Friday 04-12-2015
asdasdasdasd
Smithe755 on Wednesday 24-01-2018
whoah this weblog is wonderful i really like studying your posts. Keep up the great paintings! You already know, lots of individuals are searching around for this information, you can help them greatly. begdcdafdebccfbc