Loop within a loop for mysql ops

[br8kwall]

As you can see I'm trying to (1) return a list of all databases, then for each database (2) return a list of all tables, then (3) for each table perform an optimize. This script never gets to 3. Any thoughts?

#!/bin/sh
MUSER="root"
MPASS=""
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"

# the Bs makes the output appear without the formatting
# and header row.

# Step 1: list all databases

DBS="$($MYSQL -u$MUSER -Bse 'show databases')"
for db in $DBS
do

# Step 2: list all tables in the databases

TABLENAMES="$($MYSQL -u$MUSER $db -Bse 'show tables')"
echo "******"
echo "Database: "$db
echo $TABLENAMES
echo ""
done

# Step 3: perform an optimize (or other op) for all tables returned
for TABLENAME in $TABLENAMES
do
echo "boo, I'm supposed to be a table name"
echo $TABLENAME
mysql -u$MUSER -D $db -Bsev 'optimize TABLE $TABLENAME;'
done

[br8kwall]

Ok I got some help over at http://linuxquestions.org. But I'm stopped again.

Now on to the next weird issue. I cleaned up the script a little and *should* be getting the output I expect. In fact it echos to the screen just fine. But doesn't seem to get expanded into a command that works. But, the command that gets echo'd to the screen runs just fine by itself.

Consider the following output where I copy/paste the echo'd command.

[[START DATABASE]
Database: test
people t2
people
/usr/bin/mysql -uroot test -Bse 'optimize TABLE people;'
test.$TABLENAME optimize error Table 'test.$TABLENAME' doesn't exist
t2
/usr/bin/mysql -uroot test -Bse 'optimize TABLE t2;'
test.$TABLENAME optimize error Table 'test.$TABLENAME' doesn't exist
[END DATABASE]

[br8kwall@localhost ~]$ /usr/bin/mysql -uroot test -Bse 'optimize TABLE t2;'
test.t2 optimize status OK
[br8kwall@localhost ~]$

and here is the modified script

#!/bin/sh
MUSER="root"
MPASS=""
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
# the Bs makes the output appear without the formatting
# and header row.
# Step 1: list all databases

DBS="$($MYSQL -u$MUSER -Bse 'show databases')"

for db in ${DBS[@]}
do

# Step 2: list all tables in the databases
    echo "$MYSQL -u$MUSER $db -Bse 'show tables'"
    TABLENAMES="$($MYSQL -u$MUSER $db -Bse 'show tables')"
    echo "[START DATABASE]"
    echo "Database: "$db
    echo ${TABLENAMES[@]}

# Step 3: perform an optimize (or other op) for all tables returned

        for TABLENAME in ${TABLENAMES[@]}
        do
         echo $TABLENAME
         echo "$MYSQL -u$MUSER $db -Bse 'optimize TABLE $TABLENAME;'"
        $MYSQL -u$MUSER $db -Bse 'optimize TABLE $TABLENAME;'
        done
 echo "[END DATABASE]"
done

[br8kwall]

PS. of course this operation is not something you would want to do except on a hobby box that is all your own.

Though it would be nice feature to be able to exclude (or include) certain databases to be considered by this script.

Even within your own environment, it seems that mysql does not like even the root user toying with the "information_schema" found in MySQL 5.0 and above. So, my next task is to find a way to remove "information_schema" database from the array, or to exclude it from later processing steps. Any thoughts here would be appreciated.

[bashcurescancer.com]

         echo "$MYSQL -u$MUSER $db -Bse 'optimize TABLE $TABLENAME;'"
        $MYSQL -u$MUSER $db -Bse 'optimize TABLE $TABLENAME;'

When using single quotes variables are taken litterally. They are not replaced with their values. However, the echo works because you have used double quotes and thus the single quotes inside are not interpreted by the shell.

[br8kwall]

Yep, that did it. Thank you very much. Here's a revised version. Now of course I need to find a way to exclude "information_schema" from the list of databases.

#!/bin/sh
MUSER="root"
MPASS="password"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
# the Bs makes the output appear without the formatting
# and header row.
# Step 1: list all databases

DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases')"
for db in ${DBS[@]}
do

# Step 2: list all tables in the databases
    echo "$MYSQL -u$MUSER -p$MPASS $db -Bse 'show tables'"
    TABLENAMES="$($MYSQL -u$MUSER -p$MPASS $db -Bse 'show tables')"
    echo "[START DATABASE]"
    echo "Database: "$db
    echo ${TABLENAMES[@]}

# Step 3: perform an optimize (or other op) for all tables returned

        for TABLENAME in ${TABLENAMES[@]}
        do
         echo $TABLENAME
        echo "$MYSQL -u$MUSER -p$MPASS $db -Bse 'optimize TABLE $TABLENAME;'"
       $MYSQL -u$MUSER -p$MPASS $db -Bse "optimize TABLE $TABLENAME;"
        done
 echo "[END DATABASE]"
done

[bashcurescancer.com]

I am not sure where that would be coming from (maybe its a db you have?) but you can remove it like this:

To eliminate information_schema

DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases' | egrep -v 'information_schema')"

Or to eliminate multiple:

DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases' | egrep -v 'information_schema|somethingelse|another')"

[br8kwall]

OK, with help from the community here is the final example script that optimizes (or replace optimize with your favorite command like backup, alter table to InnoDB, etc.) all tables in all databases on a server except the core mysql databases or others that you exclude.

#!/bin/sh
MUSER="username"
MPASS="password"
MHOST="localhost"
MYSQL="$(which mysql)"
# the Bs makes the output appear without the formatting
# and header row.
# Step 1: list all databases EXCEPT core mysql tables and others that can be added
DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases' | egrep -v 'information_schema|mysql|test')"

for db in ${DBS[@]}
do

# Step 2: list all tables in the databases
    echo "$MYSQL -u$MUSER -p$MPASS $db -Bse 'show tables'"
    TABLENAMES="$($MYSQL -u$MUSER -p$MPASS $db -Bse 'show tables')"
    echo "[START DATABASE]"
    echo "Database: "$db
    echo ${TABLENAMES[@]}

# Step 3: perform an optimize (or other op) for all tables returned

        for TABLENAME in ${TABLENAMES[@]}
        do
         echo $TABLENAME
       $MYSQL -u$MUSER -p$MPASS $db -Bse "optimize TABLE $TABLENAME;"  
        done
 echo "[END DATABASE]"
done