Oops. It looks like copy and paste from vi did a number on part of the script. So here it is as an attachment.
On Tue, 2006-04-25 at 23:40 -0600, Roy Souther wrote:
This is very easy to do in bash. Here it is. Check the MYSQL_ROOT and set it to what your server is using.
#!/bin/bash
MYSQL_ROOT="/var/lib/mysql"
# Get list of databases
DB_LIST=""
MY_CONTENTS=`/bin/ls -1 $MYSQL_ROOT`
for EACH_ITEM in $MY_CONTENTS; do
if [ -d "$MYSQL_ROOT/$EACH_ITEM" ]; then
DB_LIST="$DB_LIST $EACH_ITEM"
fi
done
# Now for each database get a table list and do something to each table.
for EACH_DB in $DB_LIST; do
TABLE_LIST=`echo "SHOW TABLES" | mysql $EACH_DB | tail +2`
for EACH_TABLE in $TABLE_LIST; do
echo "Replace this line with some SQL command or other script usee table $EACH_TABLE and done
done
Are you running multiple Drupal sites from the same server? Are you using virtual hosing? I am working on virtual hosting for CMS and would like to exchange information with you if you are doing this.
On Tue, 2006-04-25 at 23:00 -0600, Shawn wrote:
Hi all.
I've run into a snag with my database that needs me to do an alter table step
on every table in the database (and each of the similar databases I have).
I've worked out the command I need to do, but right now it's a manual
process.
(er, reading this again, it's not really clear what I'm after - basically I
have multiple instances of Drupal websites, which have a problem with the
table definition - not the fields, but the character set and collation
setting. So, I need to do an alter table to set these properly.)
What I'm looking for is some way to either do a procedure in MySQL something
to the effect of "for each table name, alter the table like this...", or a
bash script to do the same.
If I take the route of the bash script, I need to extract a list of all the
(user defined) tables in a given database. Thus far, the only way I know to
do this is with the "show tables" command, but that presents a list that
needs further process before you can identify the table names in code. (i.e.
you have to remove the border characters and trim excess spaces).
Any tips?
One option is to just hard code the list of tables into a variable (something
like "table1,table2,etc..") and use the "for X in Y" looping in bash. But
the downside here is that not all the databases will have the exact same
tables (different modules in some cases). So if I can dynamically extract
the names of the database, and THEN use the for x in y loops, it makes the
routine much more efficient.
Thanks for any input.
Shawn
_______________________________________________
clug-talk mailing list
[email protected]
http://clug.ca/mailman/listinfo/clug-talk_clug.ca
Mailing List Guidelines (http://clug.ca/ml_guidelines.php)
**Please remove these lines when replying
FixMySqlTables.bsh
Description: application/shellscript
signature.asc
Description: This is a digitally signed message part
_______________________________________________
clug-talk mailing list
[email protected]
http://clug.ca/mailman/listinfo/clug-talk_clug.ca
Mailing List Guidelines (http://clug.ca/ml_guidelines.php)
**Please remove these lines when replying
