Simple bash script's for managing mysql

1. Monitoring replication health

#!/bin/bash

sql_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_SQL_Running/ { print $2 }' | tr -d ' ')
io_thread_running=$(mysql -e "show slave status\G" | awk -F":" '/Slave_IO_Running/ { print $2 }' | tr -d ' ')
seconds_late=$(mysql -e "show slave status\G" | awk -F":" '/Seconds_Behind_Master/ { print $2 }' | tr -d ' ')
seconds_late=$(($seconds_late+0))

if [ "$sql_thread_running" = "No" ] || [ "$io_thread_running" = "No" ] || [ $seconds_late -gt 3600 ]||[ "$io_thread_running" = "Connecting" ]; then

mysql -e "show slave status\G"|mail -s "Slave Database Issue" xxx.gmail.com

fi

2.Monitoring mysql status

 #!/bin/bash

 /usr/bin/mysqladmin  ping| grep 'mysqld is alive' > /dev/null 2>&1

 if [ $? != 0 ]

 then

     echo 'Mysql Service is Stop'|mail -s "Slave Mysql service down" xxx@gmail.com 

  else

     echo 'Mysql Service Running'

 fi

3.Backup mysql data

#!/bin/bash

databases=`mysql -e "SHOW DATABASES;" | grep -E "(test1|test2)"`

for db in $databases; do

  mysqldump  --databases $db | gzip > /home/mysql/data/$db`date +%Y%m%d`.sql.gz;

  mysqldump  --no-data --databases $db | gzip > /home/mysql/schema/$db`date +%Y%m%d`.sql.gz;

  done

find /home/mysql/data  -iname "*.gz" -mtime +7 -exec rm {} \;

find /home/mysql/schema  -iname "*.gz" -mtime +7 -exec rm {} \;

echo 'Dump completed Successfully'|mail -s "Dump completed" xxx@gmail.com

This script will hold last 7 days backup.Change it according to your need.

Hope this article helps !