[SOLVED] Send by email the name of the database that has MyISAM tables

bmxakias · 11-05-2019, 09:00 PM

Hello

I am trying to create a script that will check all databases and if any of them has MyISAM tables to send me an email with the name of the database(s).

Now i have that:

Code:

#!/bin/bash
EMAILALERT="email@gmail.com"
HOSTNAME="$(hostname)"
MYSQLDATADIR=$(mysqladmin var | grep 'datadir' | awk '{ print $4}')


if [[ "$(find ${MYSQLDATADIR} -type f -not -path "${MYSQLDATADIR}mysql/*" -name "*.MYD")" ]]; then
  echo "Server has MyISAM tables in database" | mail -s "$HOSTNAME has MyISAM tables in database" $EMAILALERT
fi

It works as expected but i don't know how i can get the name(s) of the folder(s) of the database(s) on my email so i will know exactly which database has the MyISAM tables.

Need some help please.

Thank you

Firerat · 11-05-2019, 11:40 PM

awk can do the grep bit

Code:

#!/bin/bash
EMAILALERT="email@gmail.com"
HOSTNAME="$(hostname)"
MYSQLDATADIR=$(mysqladmin var | awk '/datadir/{ print $4}')


MYDs=( "$(find ${MYSQLDATADIR} -type f -not -path "${MYSQLDATADIR}mysql/*" -name "*.MYD")" )
# put path/filenames in array

email_body () {
# this just prints things
  printf "%s\n" "Server has MyISAM tables in database"
  printf "%s\n" "${MYDs[@]}"
}


if [[ ${#MYDs[@]} -gt 0 ]]
then
  email_body #removeme | mail -s "$HOSTNAME has MyISAM tables in database" $EMAILALERT 
else
  echo "nothing in the array"
fi

once you are happy with the email_body output remove the #removeme to go live with the pipe to mail

Edit, the array is probably overkill
you might get away with setting -E on mail
from man mail
-E Don't send messages with an empty body.

Code:

#!/bin/bash
EMAILALERT="email@gmail.com"
HOSTNAME="$(hostname)"
MYSQLDATADIR=$(mysqladmin var | awk '/datadir/{ print $4}')

find ${MYSQLDATADIR} \
  -type f \
  -not -path "${MYSQLDATADIR}mysql/*" \
  -name "*.MYD" \
  | mail -E -s "$HOSTNAME has MyISAM tables in database" $EMAILALERT

but I'm not 100% certain of what you actually want

scasey · 11-06-2019, 12:52 AM

Code:

find ${MYSQLDATADIR} -type f -not -path "${MYSQLDATADIR}mysql/*" -name "*.MYD"

gives the entire path to each MYD file:

Code:

/var/lib/mysql/STG/Characters.MYD
/var/lib/mysql/STG/Conflicts.MYD

And you know the value of MYSQLDATADIR (/var/lib/mysql/ in this example) so the directory you're looking for follows that in the results of the find.

Seems you only need to parse that result -- which you're not now doing.

bmxakias · 11-06-2019, 02:54 AM

Quote:

Seems you only need to parse that result -- which you're not now doing.

Got it working !

Thank you

@Firerat

Thanks for your reply also !

scasey · 11-06-2019, 08:15 AM

Quote:

Originally Posted by bmxakias

Got it working !

Thank you

@Firerat

Thanks for your reply also !

You're welcome.
Share your final script here for the next person?

bmxakias · 11-06-2019, 08:53 AM

Sure:

Code:

#!/bin/bash
###########
# variables
###########
EMAILALERT="email@gmail.com"
HOSTNAME="$(hostname)"
MYSQLDATADIR=$(mysqladmin var | grep 'datadir' | awk '{ print $4}')
MYDs="$(find ${MYSQLDATADIR} -type f -not -path "${MYSQLDATADIR}mysql/*" -name "*.MYD")"
########################################################################################

if [[ "$(find ${MYSQLDATADIR} -type f -not -path "${MYSQLDATADIR}mysql/*" -name "*.MYD")" ]]; then
  echo "Server has MyISAM tables in database(s): $MYDs" | mail -s "MyISAM tables in database(s) at $HOSTNAME" $EMAILALERT
  echo ""
  echo "MyISAM tables in database(s):"
  echo "$MYDs"
  echo ""
fi