bash loop within a loop for mysql ops

br8kwall · 02-17-2007, 04:03 PM

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

ygloo · 02-17-2007, 09:45 PM

hello,

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

    TABLENAMES="$($MYSQL -u$MUSER $db -Bse 'show tables')"
    echo "******"
    echo "Database: "$db
    echo ${TABLENAMES[@]}

# 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;'
            echo ""
        done
done

DBS="$($MYSQL -u$MUSER -Bse 'show databases')"
is an array
${DBS[@]} - prints all its members

i use
bash -x script.sh <-'
to see verbose output

----------------------------
learned it from here:
http://tldp.org/LDP/Bash-Beginners-G...tml/index.html

br8kwall · 02-18-2007, 06:27 AM

Thank you, ygloo.

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

br8kwall · 02-18-2007, 11:38 AM

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.

cfaj · 02-18-2007, 09:04 PM

Quote:

Originally Posted by ygloo

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

No, it is not an array; it is a scalar variable.

To put it into an array:

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

Quote:

${DBS[@]} - prints all its members

True, but there is only one member.

If you don't believe me, try to print the second or third member:

printf "Second member: %s\n" "${DBS[1]}"
printf "Third member: %s\n" "${DBS[2]}"

Quote:

i use
bash -x script.sh <-'
to see verbose output

Or you can put "set -x" at the top of the script.

ygloo · 02-19-2007, 10:45 AM

DBS=( $($MYSQL -u$MUSER -Bse 'show databases') )
yes, brackets make difference...
good observation cfaj

br8kwall · 02-23-2007, 06:39 AM

A post over on http://www.linuxforums.org/forum/lin...tml#post436902

helped me solve the issue of the last command not working. The line needed double quotes.

Here's a revised version below. 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

ygloo · 02-23-2007, 02:56 PM

check post #5

br8kwall · 03-13-2007, 08:29 AM

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

cfaj · 03-13-2007, 05:05 PM

Quote:

Originally Posted by 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.

Code:

#!/bin/sh
MUSER="username"
MPASS="password"
MHOST="localhost"

The quotes are unnecessary.

Quote:

Code:

MYSQL="$(which mysql)"

Do not use 'which'; it is unreliable, and at least one of the versions in the wild only works with csh.

Besides, what do you need it for?

Quote:

Code:

# 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[@]}

It probably makes no difference in this case, but you should quote the array expansion:

Code:

for db in "${DBS[@]}"

That also applies to to similar contructs below.

Quote:

Code:

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

The variable should be quoted:

Code:

echo "Database: $db"

Quote:

Code:

    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

ranjan.ravi2006 · 04-30-2008, 03:50 AM

Same way I would like to backup the database using mysqldump but with not in single file but all databases with diffrent file.

Please help