Database Checking Scripts
Houurs making these.
#/bin/bash
#Written by Frost
#Checks DB Size and Last Update (with Creation Date)
myuser="MYDBUSER"
mypass="MYDBPASSWORD"
mydb="information_schema"
dbsizeq1="sum( data_length + index_length ) / 1024 /
1024"
dbsizeq2="FROM information_schema.TABLES GROUP BY
table_schema"
dbtimeq='SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME),
TABLE_SCHEMA FROM TABLES GROUP BY TABLE_SCHEMA ORDER BY 1, 2'
#Cleanup
rm -f /tmp/DBsize.html
rm -f /tmp/DBtime.html
rm -f /tmp/listofdatabases.txt
#Add HTML TAgs
echo "<html>" > /tmp/DBsize.html
echo "<html>" > /tmp/DBtime.html
DBSize=`mysql -u$myuser -p$mypass --html -t -s -e "use $mydb;
SELECT table_schema 'DB Name',$dbsizeq1 'Data Base Size in MB:'
$dbsizeq2;"`
echo
$DBSize >> /tmp/DBsize.html
DBTime=`mysql -u$myuser -p$mypass --html -t -B -s -e "use
$mydb;$dbtimeq;"`
echo
$DBTime > /tmp/DBtime.html
#Add HTML TAgs
echo "</html>" >> /tmp/DBsize.html
echo "</html>" >> /tmp/DBtime.html
--------
#/bin/bash
#Written by Frost
#Checks DB Size and Last Update (with Creation Date)
myuser="MYDBUSER"
mypass="MYDBPASSWORD"
mydb="mysql"
dbquery="select Host, Db, User from db group by Db
order by 1,2;"
checkquery="select Db, User from db where
Host="
#Lets cleanup from previous runs.
rm -f /tmp/dbuserlist.txt
rm -f /tmp/dbhostlist.txt
rm -f /tmp/crossref.txt
#Now we prepare the files.
echo "<html>" > /tmp/crossref.txt
#Yes, I know that this variable doesnt exist, but fuckit.
it works.
echo $myquery | while read -r line
do
echo
$line >> /tmp/dbuserlist.txt
done < <(mysql -u$myuser -p$mypass mysql -B -s -e
"$dbquery")
# Now that we have the List, get the Hosts that are
allowed.
more /tmp/dbuserlist.txt | awk -F " " '{print
$1}' | uniq > /tmp/dbhostlist.txt
array=()
readarray -t array < /tmp/dbhostlist.txt
#Now we run through the array and check for Databases and
Users associated to that Host.
for e in "${array[@]}"
do
result=`mysql
-u$myuser -p$mypass mysql --html -B -t -s -e "$checkquery '$e';"`
echo
Usernames with the Host: $e >> /tmp/crossref.txt
echo
$result >> /tmp/crossref.txt
done
echo "</html>" >> /tmp/crossref.txt
#Send report off to whomever wants it.
Comments