How can I write a routine to check, optimize and analyze all tables in databases in MySQL [closed]

Question

How can I create a routine in MySQL to check, optimize and then analyse the tables?

Also how can I schedule this routine to run every night at set time?

Thanks

Answer 1

I prefer writing it myself. Place these lines in /usr/local/sbin/OptimizeAllTables.sh

WHICH=/usr/bin/which
MYSQL=`${WHICH} mysql`
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE LOCAL TABLE ',db,'.',tb,';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,engine eng"
SQL="${SQL} FROM information_schema.tables WHERE"
SQL="${SQL} engine in ('InnoDB','MyISAM') AND table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY data_length DESC) A"
${MYSQL} ${MYSQL_CONN} -ANe"${SQL}" > /tmp/OptimizeAllTables.sql
${MYSQL} ${MYSQL_CONN} < /tmp/OptimizeAllTables.sql

and make it executeable

chmod +x /usr/local/sbin/OptimizeAllTables.sh

CAVEAT #1

Running OPTIMIZE TABLE is the same as running ALTER TABLE ... ENGINE=... followed by ANALYZE TABLE. Therefore, there is no need to run a separate ANALYZE TABLE.

CAVEAT #2

Please note I use OPTIMIZE LOCAL TABLE instead of OPTIMIZE TABLE. This ensures that the OPTIMIZE TABLE is not recorded in the binary logs because it can replicate to Slaves. If you want the OPTIMIZE TABLE to be replicated all Slaves, OPTIMIZE TABLE is fine.

CAVEAT #3

Note that I ordered all the tables by the biggest table first. If you want to optimize all tables starting with the smallest table, change this line

SQL="${SQL} ORDER BY data_length DESC) A"

to

SQL="${SQL} ORDER BY data_length) A"

EPILOGUE

Simply place this line in the crontab to run every midnight

0 0 * * * /usr/local/sbin/OptimizeAllTables.sh

If the tables are enormous, you may want to schedule this for one a week (Sunday at Midnight)

0 0 * * 0 /usr/local/sbin/OptimizeAllTables.sh

Give it a Try !!!

Answer 2

It may be that you do not need to write your own script.

I would suggest reading about the mysqlcheck MySQL command. It has options to do all of the things that you want to do to a table, to all tables in a database, and so forth. The MySQL documentation on this can be found at: http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html

In terms of scheduling the tasks MySQL has an Event Scheduler which can be used to manage the scheduling and running of events. The MySQL documentation starts here: http://dev.mysql.com/doc/refman/5.1/en/events.html

Original link posted was: http://www.thegeekstuff.com/2011/12/mysqlcheck/