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)
- Code: Select all
### MySQL Setup ###
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)
- Code: Select all
#!/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
If you get into any trouble, let us know!
