usefull to optimize and repair your mysql databases 
It work for every table of the given database
mkdir /root/scripts
nano /root/scripts/mysql_repair.sh
edit root password
Code
- ### MySQL Setup ###MUSER="root"MPASS="mysql password"MHOST="localhost"# No need to change below this, unless your mysql_repair_db.sh script isn't on /root/scripts/mysql_repair_db.shMYSQL="$(which mysql)"DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"for db in $DBSdo if [ "$db" != "information_schema" ]; then RES="$(/root/scripts/mysql_repair_db.sh --optimize --credentials "-u${MUSER} -p${MPASS}" $db)" #echo $RES >> /root/scripts/log_mysql_optimize RES="$(/root/scripts/mysql_repair_db.sh --repair --credentials "-u${MUSER} -p${MPASS}" $db)" #echo $RES >> /root/scripts/log_mysql_repair fidone
nano /root/scripts/mysql_repair_db.sh
nothing to edit here ....
Shell-Script
- #!/bin/sh
- # this shell script finds all the tables for a database and run a command against it
- # @usage "mysql_tables.sh --optimize MyDatabaseABC"
- # @bug fixed by WebLive Help at July 1st 2008
- # @author Son Nguyen from http://www.fagioli.biz/?q=mysql-database-optimize-and-repair-bash-script
- #AUTH='-uweb -pwebphp'
- AUTH='-uadmin -pqLtwUCRKL8'
- AUTH=$3
- DBNAME=$4
- printUsage() {
- echo "Usage: $0"
- echo " --optimize --credentials '-uUSERNAME -pPASSWORD' "
- echo " --repair "
- return
- }
- doAllTables() {
- # get the table names
- TABLENAMES=`mysql $AUTH -D $DBNAME -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
- # loop through the tables and optimize them
- for TABLENAME in $TABLENAMES
- do
- mysql $AUTH -D $DBNAME -e "$DBCMD TABLE $TABLENAME;"
- done
- }
- if [ $# -lt 3 ] ; then
- printUsage
- exit 1
- fi
- case $1 in
- --optimize) DBCMD=OPTIMIZE; doAllTables;;
- --repair) DBCMD=REPAIR; doAllTables;;
- --help) printUsage; exit 1;;
- *) printUsage; exit 1;;
- esac
chmod 700 /root/scripts/mysql_repair.sh
chmod 700 /root/scripts/mysql_repair_db.sh
set-up a cronjob:
/root/scripts/mysql_repair.sh