Thanks to Donna for that very useful SQL statement. Below is the script
I ended up writing to clean the eight-bajillion eventum_* tables that
accidentally got splattered into our servers.
Anyways, this is probably very useful for other types of bulk SQL stuff
via some BASH scripting so I figured I'd post it up if for no other
reason that navel gazing. :-)
BTW, this is not a reflection on Eventum, I've never even used it myself
to say either way if it's good/bad, I was just tasked (as a contractor)
to remove the tables from all six mySQL DB servers.
On Fri, 2009-01-16 at 08:13 -0500, ddevaudre...@intellicare.com wrote:
> Daevid Vincent <dae...@daevid.com> wrote on 01/15/2009 09:57:19 PM:
>
> > you misunderstand me. I have three servers (dev, test, prod) that all
> > have maybe 3 databases EACH that have all these eventum* tables in them.
> > don't ask. a simple "trickle" won't do. I'm writing a script to loop
> > through them all.
> >
>
> The script below will create the Drop Table command for all tables like
> 'eventum%'.
>
> select CONCAT('DROP TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';')
> from information_schema.TABLES
> where TABLE_NAME like 'eventum%' and TABLE_TYPE <> 'VIEW'
>
> Donna D.
#!/bin/bash
# written by Daevid Vincent on 01/15/09
# This will remove all the eventum tables and files
# make sure we're running as root
if (( `/usr/bin/id -u` != 0 )); then { echo "Sorry, must be root.
Exiting..."; exit; } fi
USERNAME=root
PASSWORD=secret
# .1 Dev Master
# .2 Dev Slave
# .3 Test Master
# .4 Test Slave
# .5 Prod Master
# .6 Prod Slave
MYSQLRDBMS="1 3 5 2 4 6"
echo
for ip in $MYSQLRDBMS; do
HOST=10.10.10.$ip
CONNECTION="-u $USERNAME -p$PASSWORD -h$HOST"
echo -e "\e[00;31m[DEBUG] `date`\e[00m"
echo -e "\e[01;37m[DEBUG] mysql $CONNECTION\e[00m"
echo -e "\e[01;32mFinding all 'eventum' instances across all databases
on $HOST...\e[00m"
# [dv] I started doing it this way, then discovered a better way below
from ddevaudre...@intellicare.com but kept this for future reference.
#EDBS=$(mysql -sN $CONNECTION information_schema -e "SELECT DISTINCT
TABLE_SCHEMA FROM TABLES WHERE TABLE_NAME LIKE 'eventum%'")
#echo $EDBS
#for DB in $EDBS; do
# echo "Using database $DB"
# ETABS=$(mysql -sN $CONNECTION $DB -e "SHOW TABLES WHERE
Tables_in_$DB
LIKE 'eventum%'")
# for ETABLE in $ETABS; do
# echo -n "Dropping table $ETABLE..."
# #mysql $CONNECTION $DB -e "DROP TABLE IF EXISTS
$DB.$ETABLE"
# echo "Done"
# done
#done
DROPS=$(mysql $CONNECTION -sN -e "SELECT CONCAT(TABLE_SCHEMA, '.',
TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_NAME like
'eventum%' AND TABLE_TYPE <> 'VIEW'")
for DROPSQL in $DROPS; do
echo -e "\e[00;32m\tDROP TABLE $DROPSQL;\e[00m"
mysql $CONNECTION -sN -e "DROP TABLE IF EXISTS $DROPSQL;"
done
echo
echo "Sleeping (5)"
sleep 5
echo
done #hosts
# If you want to get rid of the rest of the eventum files that may never
get cleaned up
# b/c they're copies in dormant dirs, use this:
echo "Purging remaining 'eventum' from /var/www tree..."
sudo find /var/www -name "eventum*" -exec rm -rf {} \;
echo -e "\e[01;33mCompleted.\e[00m"
