MySQL: Repair & Optimize all Tables in all Databases
By Tim Quax on 08 june 2010
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.
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.
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
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!
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 Friday 18-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 :)
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.
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
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
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 :)
@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 :)
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
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.
Mori on Thursday 06-10-2011
Hi!
Why not simple and short:
mysqlcheck -Aor -u root -p ?
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
@Mori,
That would work too :) For the ones that don't know what the arguments stand for:
A = all databases
o = optimize
r = repair
Huzoor Bux Panhwar on Tuesday 03-01-2012
I need to check only one data base please suggest how to.. :)
I need to check only one data base please suggest how to.. :)
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,
Hi,
I'm here just to thank you ,
your solution was perfect and helped us to fix our database.
Regards,
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
@Huzoor,
Just replace the argument --all-databases with your databasename, e.g.:
mysqlcheck -u root -p --auto-repair --check --optimize your_db
React on this article
Tags
Relevant howto's
| 22-01 | MySQL Optimization using MysQLTuner |
| 19-10 | Access your MySQL server through SSH |
| 02-09 | MySQL database backup from the Linux Console |
| 02-09 | Use Public Keys for SSH sessions |
Site Links
Partner Sites
Community
Subscribe
