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

seachen

Verified User
Joined
Feb 3, 2007
Messages
497
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
 
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
 
Hi,

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

When using this command, I get error 1045.

What should I do.

Thanks.
 
what if we want to check database tables only for specific user only... rather than repairing each and every tables.... plz be advice...
 
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
 
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
 
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
 
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.
 
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.
 
Thank you, Alex! I did your second option, and it works great on MySQL 5.5.x
 
SSHing into the server is much more risky than running this command.... unless you've got enough knowledges.
 
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?
 
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.
 
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
 
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
 
Back
Top