LinuxQuestions.org
Review your favorite Linux distribution.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
LinkBack Search this Thread
Old 02-17-2007, 04:03 PM   #1
br8kwall
LQ Newbie
 
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15

Rep: Reputation: 0
bash 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
 
Old 02-17-2007, 09:45 PM   #2
ygloo
Member
 
Registered: Aug 2006
Distribution: slack
Posts: 323

Rep: Reputation: 30
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

Last edited by ygloo; 02-17-2007 at 10:13 PM.
 
Old 02-18-2007, 06:27 AM   #3
br8kwall
LQ Newbie
 
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15

Original Poster
Rep: Reputation: 0
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
 
Old 02-18-2007, 11:38 AM   #4
br8kwall
LQ Newbie
 
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15

Original Poster
Rep: Reputation: 0
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.
 
Old 02-18-2007, 09:04 PM   #5
cfaj
Member
 
Registered: Dec 2003
Location: Toronto, Canada
Distribution: Mint, Mandriva
Posts: 221

Rep: Reputation: 31
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.
 
Old 02-19-2007, 10:45 AM   #6
ygloo
Member
 
Registered: Aug 2006
Distribution: slack
Posts: 323

Rep: Reputation: 30
DBS=( $($MYSQL -u$MUSER -Bse 'show databases') )
yes, brackets make difference...
good observation cfaj
 
Old 02-23-2007, 06:39 AM   #7
br8kwall
LQ Newbie
 
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15

Original Poster
Rep: Reputation: 0
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
 
Old 02-23-2007, 02:56 PM   #8
ygloo
Member
 
Registered: Aug 2006
Distribution: slack
Posts: 323

Rep: Reputation: 30
check post #5
 
Old 03-13-2007, 08:29 AM   #9
br8kwall
LQ Newbie
 
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15

Original Poster
Rep: Reputation: 0
Smile

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
 
Old 03-13-2007, 05:05 PM   #10
cfaj
Member
 
Registered: Dec 2003
Location: Toronto, Canada
Distribution: Mint, Mandriva
Posts: 221

Rep: Reputation: 31
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
 
Old 04-30-2008, 03:50 AM   #11
ranjan.ravi2006
LQ Newbie
 
Registered: Apr 2008
Posts: 1

Rep: Reputation: 0
Want to Backup All databases in mysql but all in seperate file

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
 
  


Reply

Tags
array, bash, databases, list, loop, mysql, tables


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to loop over text file lines within bash script for loop? johnpaulodonnell Linux - Newbie 8 04-05-2011 09:18 AM
SSH Bash Loop m1ck Programming 7 01-31-2007 10:03 AM
for loop only works properly on first loop symo0009 Programming 1 12-25-2005 05:17 PM
Bash for loop Genjix Programming 5 12-23-2004 02:56 AM
bash for loop problem deadlock Programming 5 09-04-2003 04:32 AM


All times are GMT -5. The time now is 07:51 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration