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

closed as too broad by StanleyJohns, Jon Seigel, dezso, Mark Storey-Smith, swasheck Aug 6 '13 at 19:21

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    By "check, optimize, and analyse" are you referring to specific MySQL commands or general ideas? – Nick Chammas Aug 2 '13 at 11:50
  • @NickChammas the OP is referring to CHECK TABLE tblname;,OPTIMIZE TABLE tblname;,ANALYZE TABLE tblname; commands in MySQL. – RolandoMySQLDBA Aug 2 '13 at 15:03

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 !!!

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/

Not the answer you're looking for? Browse other questions tagged or ask your own question.