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 |
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
Are you new to LinuxQuestions.org? Visit the following links:
Site Howto |
Site FAQ |
Sitemap |
Register Now
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
|
 |
02-17-2007, 05:03 PM
|
#1
|
LQ Newbie
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15
Rep:
|
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
|
|
|
02-17-2007, 10:45 PM
|
#2
|
Member
Registered: Aug 2006
Distribution: slack
Posts: 323
Rep:
|
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 11:13 PM.
|
|
|
02-18-2007, 07:27 AM
|
#3
|
LQ Newbie
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15
Original Poster
Rep:
|
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
|
|
|
02-18-2007, 12:38 PM
|
#4
|
LQ Newbie
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15
Original Poster
Rep:
|
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-18-2007, 10:04 PM
|
#5
|
Member
Registered: Dec 2003
Location: Toronto, Canada
Distribution: Mint, Mandriva
Posts: 221
Rep:
|
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.
|
|
|
02-19-2007, 11:45 AM
|
#6
|
Member
Registered: Aug 2006
Distribution: slack
Posts: 323
Rep:
|
DBS=( $($MYSQL -u$MUSER -Bse 'show databases') )
yes, brackets make difference...
good observation cfaj
|
|
|
02-23-2007, 07:39 AM
|
#7
|
LQ Newbie
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15
Original Poster
Rep:
|
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
|
|
|
02-23-2007, 03:56 PM
|
#8
|
Member
Registered: Aug 2006
Distribution: slack
Posts: 323
Rep:
|
check post #5
|
|
|
03-13-2007, 09:29 AM
|
#9
|
LQ Newbie
Registered: Aug 2005
Location: Youngstown, NY
Distribution: Debian
Posts: 15
Original Poster
Rep:
|
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
|
|
|
03-13-2007, 06:05 PM
|
#10
|
Member
Registered: Dec 2003
Location: Toronto, Canada
Distribution: Mint, Mandriva
Posts: 221
Rep:
|
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
|
|
|
|
04-30-2008, 04:50 AM
|
#11
|
LQ Newbie
Registered: Apr 2008
Posts: 1
Rep:
|
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
|
|
|
All times are GMT -5. The time now is 10:02 AM.
|
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
|
Latest Threads
LQ News
|
|