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
Pharmk322 on Wednesday 24-01-2018

Hello!indian viagra
canadian_viagra on Thursday 01-02-2018

Hello!
canadian viagra , canadian cialis , buy cialis online , cheap viagra online , generic viagra ,
cialis_cheap on Monday 05-02-2018

Hello!
buy cialis , buy cialis , buy cheap cialis , cialis cheap , cheap viagra ,
cialis_online on Monday 12-02-2018

Hello!
cheap viagra , cialis cheap , cialis online , cialis online , cialis cheap ,
viagra_cheap on Monday 12-02-2018

Hello!
cialis , buy cialis , buy cialis , viagra cheap , buy cialis ,
buy_viagra on Tuesday 20-02-2018

Hello!
buy cialis , buy cheap cialis , buy generic viagra , buy viagra , buy cialis ,
viagra_online on Friday 23-02-2018

Hello!
cialis canada , viagra online , cheap viagra , generic cialis online , buy generic viagra ,
cialis_cheap on Monday 26-02-2018

Hello!
generic cialis , viagra online , cialis cheap , buy viagra , viagra ,
cialis_online on Tuesday 06-03-2018

Hello!
cialis cheap , cialis cheap , cialis online , cialis online , generic cialis ,
cialis on Friday 09-03-2018

Hello!
buy cialis , viagra cheap , cialis , cialis , viagra price ,
generic on Monday 12-03-2018

Hello!
viagra , buy cheap cialis , canadian cialis , buy generic viagra , buy cheap cialis ,
cheap_viagra on Thursday 22-03-2018

Hello!
viagra online pharmacy , loans for bad credit , cheap viagra , viagra cheap , cialis generic ,
generic_cialis on Wednesday 28-03-2018

Hello!
buy generic viagra , generic cialis , cialis cheap , cheap cialis , online cialis ,
buy_viagra on Friday 30-03-2018

Hello!
cialis , buy viagra , sildenafil online , buy viagra , buy viagra ,
generic_viagra on Wednesday 04-04-2018

Hello!
generic viagra , buy generic viagra , payday loans online , online payday loans , buy cheap viagra ,
cheap_cialis on Tuesday 10-04-2018

Hello!
generic cialis , cheap cialis , cialis online , generic viagra online , online viagra ,
cheap on Wednesday 11-04-2018

Hello!
viagra cheap , cialis , buy viagra , buy cheap cialis , canadian cialis ,
buy_cialis on Monday 23-04-2018

Hello!
buy generic viagra , buy cialis , buy cialis , generic cialis online , generic cialis online ,
cialis_canada on Friday 27-04-2018

Hello!
cialis canada , cialis generic , generic viagra , generic cialis online , cheap viagra ,
buy_viagra on Friday 04-05-2018

Hello!
viagra cheap , payday loans online , buy viagra , viagra , buy viagra ,
cialis_online on Wednesday 16-05-2018

Hello!
purchase cialis , buy viagra online , buy viagra online , viagra , cialis online ,
buy_cialis on Saturday 02-06-2018

Hello!
buy viagra , buy viagra , buy viagra , buy cialis , buy viagra ,
cialis_cheap on Monday 04-06-2018

Hello!
viagra , cialis online , cialis cheap , viagra ,
viagra on Tuesday 12-06-2018

Hello!
cialis , buy viagra online , viagra canada , cialis , buy viagra online ,
viagra on Friday 15-06-2018

Hello!
viagra , viagra , viagra cheap , viagra , cialis cheap ,
cialis on Tuesday 19-06-2018

Hello!
cialis , buy viagra , cialis , buy viagra , cialis ,
online_viagra on Wednesday 27-06-2018

Hello!
buy cialis , online viagra , generic cialis , online viagra , viagra cheap ,
viagra_online on Friday 29-06-2018

Hello!
generic cialis , viagra online , generic cialis , buy viagra online , generic cialis ,
viagra_online on Friday 06-07-2018

Hello!
viagra online , viagra online , generic cialis online , payday loan application , viagra online ,
cialis on Tuesday 17-07-2018

Hello!
faxless payday loan , cialis , buy viagra online , buy viagra , cialis ,
viagra on Tuesday 24-07-2018

Hello!
cialis online , buy viagra , cialis online , viagra , cialis online ,
super on Friday 27-07-2018

Hello!
cialis super active , canadian cialis , cheap viagra , discount cialis , cheap viagra ,
buy_cialis on Tuesday 31-07-2018

Hello!
viagra cheap , viagra cheap , buy cialis , buy cialis , cheap cialis ,
viagra_canada on Sunday 05-08-2018

Hello!
viagra canada , cialis canada , buy generic cialis , payday loans online , viagra canada ,
buy_cialis on Thursday 09-08-2018

Hello!
viagra , buy cialis , cialis , cialis , buy cialis ,
cialis_generic on Wednesday 22-08-2018

Hello!
buy cialis , cialis generic , online viagra , online payday advance , online viagra ,
generic_viagra on Thursday 23-08-2018

Hello!
buy cialis , generic viagra cananda , payday express , generic viagra , buy viagra ,
cheap_viagra on Wednesday 29-08-2018

Hello!
cheap viagra , payday loans , buy viagra online , cialis , cialis canada ,
cialis_online on Monday 03-09-2018

Hello!
viagra professional , order cialis , small personal loans , cialis online , viagra price ,
cialis_generic on Thursday 06-09-2018

Hello!
buy cialis , viagra coupon , cialis generic , cheap viagra , cialis fast delivery ,
buy_viagra on Friday 14-09-2018

Hello!
buy viagra , no fax payday loans , buy viagra , viagra cheap , no fax payday loans ,
viagra_canada on Monday 17-09-2018

Hello!
viagra canada , cialis super active , buy viagra , payday advances , cheap viagra ,
canadian_cialis on Monday 24-09-2018

Hello!
canadian cialis , buy viagra , buy cialis , buy viagra , buy viagra ,
cialis on Tuesday 25-09-2018

Hello!
buy cialis online , viagra generic , cialis super active , cheap cialis , cialis ,
generic_cialis on Wednesday 26-09-2018

Hello!
viagra cheap , cialis , buy viagra online , generic cialis , generic cialis ,
viagra on Thursday 27-09-2018

Hello!
viagra , buy cialis , generic viagra , buy cialis online , buy viagra online ,
canadian_viagra on Friday 28-09-2018

Hello!
cheap cialis , cheap cialis , viagra professional , purchase cialis , canadian viagra ,
cialis on Sunday 30-09-2018

Hello!
cialis , faxless payday loan , cialis tadalafil , cialis , cialis ,
buy_viagra on Thursday 04-10-2018

Hello!
buy viagra , cheap viagra , cheap viagra , cheap viagra , cheap cialis ,
viagra_cheap on Saturday 06-10-2018

Hello!
buy viagra , viagra cheap , cialis , viagra cheap , cialis ,
buy_viagra on Sunday 07-10-2018

Hello!
buy viagra , buy cialis , online viagra , cialis , online viagra ,
viagra on Monday 08-10-2018

Hello!
viagra , generic viagra , buy viagra online , buy viagra , buy viagra online ,
generic_cialis on Tuesday 09-10-2018

Hello!
generic cialis , cialis online , generic cialis , buy viagra , viagra ,
cialis_cheap on Wednesday 10-10-2018

Hello!
viagra , viagra , cialis cheap , viagra online , buy cialis ,
viagra on Thursday 11-10-2018

Hello!
viagra , cialis online , viagra , viagra , viagra ,
buy_cialis on Friday 12-10-2018

Hello!
buy cialis , canadian cialis , cheap viagra , cheap viagra , buy viagra ,
buy_viagra on Saturday 13-10-2018

Hello!
buy viagra , cheap viagra , buy viagra online , canadian viagra , viagra professional ,
cheap_cialis on Monday 15-10-2018

Hello!
cialis online , cialis online , sildenafil citrate , cheap cialis , buy viagra ,
online_viagra on Monday 15-10-2018

Hello!
online viagra , cheap viagra , canadian cialis , cheap viagra , generic viagra online ,
viagra_online on Tuesday 16-10-2018

Hello!
viagra cheap , viagra online , buy viagra online , generic cialis , buy viagra online ,

React on this article







Enter the code here: