Loop within a loop for mysql ops

02-17-2007	#1 (permalink)
br8kwall Just Joined! Join Date: Feb 2007 Posts: 5	Loop within a loop for mysql ops 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

02-18-2007	#2 (permalink)
br8kwall Just Joined! Join Date: Feb 2007 Posts: 5	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 Code: #!/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

02-18-2007	#3 (permalink)
br8kwall Just Joined! Join Date: Feb 2007 Posts: 5	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.

02-19-2007	#4 (permalink)
bashcurescancer.com Just Joined! Join Date: Feb 2007 Posts: 41	Code: 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.

02-23-2007

#5 (permalink)

br8kwall

Just Joined!

Join Date: Feb 2007

Posts: 5

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.

Code:

#!/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

02-23-2007	#6 (permalink)
bashcurescancer.com Just Joined! Join Date: Feb 2007 Posts: 41	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 Code: DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases' \| egrep -v 'information_schema')" Or to eliminate multiple: Code: DBS="$($MYSQL -u$MUSER -p$MPASS -Bse 'show databases' \| egrep -v 'information_schema\|somethingelse\|another')"

03-13-2007

#7 (permalink)

br8kwall

Just Joined!

Join Date: Feb 2007

Posts: 5

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.

Code:

#!/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

Are you a Linux Guru who is willing to share your in-depth Linux knowledge? Try our new Article submission system.