Home > Linux Shell, mysql > MySQL: Optimize all tables; Run using cron

MySQL: Optimize all tables; Run using cron

I was looking for a optimize table script and finally stumbled upon some code which could be salvaged into a shell script. The full code is reproduced here for future reference.


#!/bin/bash
 
#-- I usually add the root username and password to the ~/.my.cnf
MYSQL_CMD='/usr/bin/mysql '
 
$MYSQL_CMD -e "SELECT concat('OPTIMIZE TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,';') as cmd FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 10 AND NOT ENGINE='MEMORY' INTO OUTFILE '/tmp/cmd.sql'"
 
$MYSQL_CMD < /tmp/cmd.sql
rm -f /tmp/cmd.sql

Categories: Linux Shell, mysql Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

fifty eight + = fifty nine