mysql table optimize and repair

本帖最后由 andy 于 2010-2-10 10:15 编辑

说明： 此脚本完全转自网络，感觉脚本写的十分不错，故转过来收藏。

#!/bin/sh

# this shell script finds all the tables for a database and run a command against it
# @usage "mysql_table_optimize.sh --optimize MyDatabaseABC"
# @date 8/1/2008
# @base on author Son Nguyen's script mysql_tables.sh chaged by cn11747@nhncorp.cn

DBNAME=$2

printUsage() {
  echo "Usage: $0"
  echo " --optimize "
  echo " --repair "
  return
}


doAllTables() {
  #### Get user account for mysql
        echo -n "Enter you mysql user name here:"
        read User;
        echo -n "Enter you passwd for mysql $User:"
        read -s PASSWD;
  #### Get all table name and then oprate them with "'$DBCMD' tables"
        mysql ${DBNAME} -u$User -p${PASSWD} -e "show tables;" |grep -v "Tables_in_" | \      

                             awk '{print "'$DBCMD' table '$DBNAME'." $1 ";"}' | \            

                       xargs -i mysql -u$User -p${PASSWD} -e {};

}

if [ $# -eq 0 ] ; then
  printUsage
  exit 1
fi

case $1 in
  --optimize) DBCMD=OPTIMIZE; doAllTables;;
  --repair) DBCMD=REPAIR; doAllTables;;
  --help) printUsage; exit 1;;
  *) printUsage; exit 1;;
esac