MySQL Tip: How To Check, Repair & Optimize All Tables in All Databases

seachen

Verified User
Joined
Feb 3, 2007
Messages
486
Here is a simple command to auto repair, check and optimize all the tables in all databases running on a MySQL server:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
 

smtalk

Administrator
Staff member
Joined
Aug 22, 2006
Messages
8,335
Location
LT, EU
It's better to use the da_admin user with a provided password :) So, the command would be:
Code:
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
 

nulkarp

New member
Joined
Jun 5, 2009
Messages
4
Hi,

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

When using this command, I get error 1045.

What should I do.

Thanks.
 

asking

Verified User
Joined
Jul 8, 2010
Messages
58
what if we want to check database tables only for specific user only... rather than repairing each and every tables.... plz be advice...
 

wattie

Verified User
Joined
May 31, 2008
Messages
992
Location
Bulgaria
For MySQL 5.5 on FreeBSD you have to skip the --check option (giving an error message for contradicting commands?!?). So it should be:


Code:
/usr/local/mysql/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair=TRUE --optimize --all-databases-TRUE
 

wattie

Verified User
Joined
May 31, 2008
Messages
992
Location
Bulgaria
and it is "=TRUE" at the end, not "-TRUE":

Code:
/usr/local/mysql/bin/mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair=TRUE --optimize --all-databases=TRUE
 

ditto

Verified User
Joined
Apr 27, 2009
Messages
2,458
It's better to use the da_admin user with a provided password :) So, the command would be:
Code:
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
I just used that command, and it seems to work fine. However I get error regarding performance_schema, is that normal? I am on MySQL 5.1.61 (about to upgrade to 5.5.x soon). Here is all the output regarding performance_schema error:

Code:
performance_schema.cond_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_current
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_history
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_history_long
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_by_instance
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_by_thread_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_global_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_summary_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_summary_by_instance
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.mutex_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.performance_timers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.rwlock_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_consumers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_instruments
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_timers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.threads
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt

Repairing tables
performance_schema.cond_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_current
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_history
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_history_long
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_by_instance
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_by_thread_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.events_waits_summary_global_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_summary_by_event_name
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.file_summary_by_instance
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.mutex_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.performance_timers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.rwlock_instances
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_consumers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_instruments
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.setup_timers
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
performance_schema.threads
Error    : Unknown table engine 'PERFORMANCE_SCHEMA'
error    : Corrupt
 

ditto

Verified User
Joined
Apr 27, 2009
Messages
2,458
It's better to use the da_admin user with a provided password :) So, the command would be:
Code:
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
I have now upgraded from mysql 5.1.x to mysql 5.5.x, and that commands does not work anymore, but give this error:

Code:
[root@server ~]# mysqlcheck -uda_admin -p`grep "^passwd=REMOVED" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --check --optimize --all-databases
Error:  mysqlcheck doesn't support multiple contradicting commands.
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
13,854
Location
GMT +7.00
Then try to use

Code:
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --optimize --all-databases
or

Code:
mysqlcheck --defaults-extra-file=/usr/local/directadmin/conf/my.cnf --auto-repair --optimize --all-databases
as more secure, as no password will be shown in process lists. Of course you should have /usr/local/directadmin/conf/my.cnf on your box.
 

ditto

Verified User
Joined
Apr 27, 2009
Messages
2,458
Thank you, Alex! I did your second option, and it works great on MySQL 5.5.x
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
13,854
Location
GMT +7.00
SSHing into the server is much more risky than running this command.... unless you've got enough knowledges.
 

mimic

Verified User
Joined
Oct 5, 2007
Messages
43
Location
Utrecht, The Netherlands
Is it a good idea to cron this, so all databases on the server kept beeing optimized? Or are there risks involved that might cause a corrupted database?
 

zEitEr

Super Moderator
Joined
Apr 11, 2005
Messages
13,854
Location
GMT +7.00
I use it in daily cron on some servers, and I've never found an issue with that. So I'd rather say the idea is good.
 

doppelt

New member
Joined
Apr 3, 2014
Messages
1
How to first use MySQLcheck

I need help

I cannot get MySQLcheck to work on my Linux server

I do not use SSH often

but I logged in ,

once I had a prompted in the root

I tried many of the suggested command lines for MySQLcheck, and nothing happened

Once....I got a command to do something, but I got a 1045 error, I think


it asked for a password!

what password?

I had already used the password to access my server
and each of my databases has its own password
so what password were they asking for? (I was using a command to repair and optimize all databases)

-----------

so I have no clue how to do this

I want to first check databases before repairing them

I only want to repair a single database at a time if needed

and are these mySQLcheck commands run from the root of my server?

thanks

Dennis
 

nobaloney

NoBaloney Internet Svcs - In Memoriam †
Joined
Jun 16, 2003
Messages
26,119
Location
California
This is the DirectAdmin forum, for users of the DirectAdmin hosting control panel. Are you using a DirectAdmin based server? If so, you can use the master MySQL password for da_admin. You can find it in /usr/local/directadmin/conf/mysql.conf

Even if you're not running in DirectAdmin this article in our Knowledgebase may be able to help you: http://help.directadmin.com/item.php?id=45

But be careful.

Jeff
 
Top