BASH修复所有mysql库表

时间：2010-05-18 来源：xhq6632

Bash Script to Repair ALL MySQL Tables for ALL Databases

This is something any server owner/maintainer should have to run and fix ALL MySQL databases, it's not the quickest script, but theoretically it shouldn't fail (like myisamchk does sometimes, if the MySQL Table errors are too big/weird/deep)

So basically we'll provide you with two files: mysql_repair_db.sh (this file is from http://www.fagioli.biz/?q=mysql-databas ... ash-script but with a bug fixed) and mysql_repair, the file you will need to execute to fix all tables in all databases, because mysql_repair_db.sh only fixes all tables for one database (it can be useful, so we didn't want to mix the two files)

Here's the code for both files, don't forget to change the variables with your own mysql auth data and chmod 700 them so you can execute them.

We suggest you put them on /root/scripts but you're free to put them anywhere you want, you just need to change the scripts path on the code below to reflect your reality.

/root/scripts/mysql_repair : (configure MUSER, MPASS and MHOST)
MUSER="here_goes_your_mysql_user_probably_root"
MPASS="here_goes_your_user_password"
MHOST="localhost"
# No need to change below this, unless your mysql_repair_db.sh script isn't on /root/scripts/mysql_repair_db.sh
MYSQL="$(which mysql)"

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
    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
    fi
done
/root/scripts/mysql_repair_db.sh : (nothing to configure)
#!/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

mysql mysql -s -e "show tables" | grep -v "Tables_in_"

实例：
#!/bin/sh
LOGFILE="/root/myshell/mysql_repair.log"
AUTH='-uroot -ppassword'
DBNAME=$2

printUsage() {
    echo "Usage: "
    echo "$0 <option> <dbname>"
    echo " --optimize/-O    Optimize table"
    echo " --repair/-R        Repairs a possibly corrupted table"
    echo " --help/-h            This help text"
    return
}

doAllTables() {
    # get the table names
    echo "[`date +%c`] GET THE TABLE NAME OF $DBNAME" >>$LOGFILE 2>&1
    TABLENAMES=`mysql $AUTH -D $DBNAME -s -e "SHOW TABLES;"|grep -v 'Tables_in_'`
    echo -ne "[`date +%c`] TABLE LIST:\n$TABLENAMES\n" >>$LOGFILE 2>&1
    # loop through the tables and optimize them
    echo "[`date +%c`] $DBCMD TABLE BEGIN" >>$LOGFILE 2>&1
    for TABLENAME in $TABLENAMES
    do
        echo "[`date +%c`] $DBCMD TABLE \`$TABLENAME\`;" >>$LOGFILE 2>&1
        mysql $AUTH -D $DBNAME -e "$DBCMD TABLE \`$TABLENAME\`;"
    done
    echo "[`date +%c`] $DBCMD TABLE END" >>$LOGFILE 2>&1
}

if [ $# -lt 2 ] ; then
    printUsage
    exit 1
fi

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