cYKysrvW

1#!/bin/bash
2#
3# #automysqlcheck.sh
4#
5# This is a small bash script that checks all mysql databases for errors
6# and mails a log file to a specified email address. All variables are
7# hardcoded for ease of use with cron. Any databases you wish not to check
8# should be added to the DBEXCLUDE list, with a space in between each name.
9#
10# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
11#
12# modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se and Jake Carr jake-+AT+-websitesource-+DOT+-com
13# (see Change Log for details)
14#
15#=====================================================================
16# Change Log
17#=====================================================================
18#
19# VER 1.4 - (2010-10-18)
20# Added I/O redirection to $LOGFILE
21# added flush & lock tables before check
22# modified the database exclusion so that it works also with Darwin/Mac OS X
23# Modified by Fabrizio La Rosa
24# VER 1.3 - (2006-12-02)
25# Added --host=$DBHOST in mysql commands, so it's useful for non-localhost situations
26# Jake Carr
27# VER 1.2 - (2006-10-29)
28# Added "\`" arround the tables in $DBTABLES, otherwise it'll create
29# errors if tablenames containt characters like -.
30# Modified by Mickael Sundberg
31# VER 1.1 - (2005-02-22)
32# Named script automysqlcheck.sh
33# Added PATH variable to make this script more CRON friendly
34# Removed the $DBTABLES loop and replaced it with single command
35# that executes the CHECK TABLE command on all tables in a given DB
36# Changed code to only check MyISAM and InnoDB tables
37# Cleaned up output to make the email prettier
38# Modified script to skip databases that have no tables
39# Modified by eyechart
40# VER 1 - (2004-09-24)
41# Initial release by sbray@csc.uvic.ca
42
43# system variables (change these according to your system)
44PATH=/usr/local/bin:/usr/bin:/bin:$PATH
45USER=root
46PASSWORD= # add your pw here
47DBHOST=localhost
48LOGFILE=/var/log/automysqlcheck.log
49MAILTO=root@localhost
50TYPE1= # extra params to CHECK_TABLE e.g. FAST, QUICK, CHANGED, MEDIUM, EXTENDED
51# QUICK Do not scan the rows to check for incorrect links. Applies to InnoDB and MyISAM tables and views.
52# FAST Check only tables that have not been closed properly. Applies only to MyISAM tables and views; ignored for InnoDB.
53# CHANGED Check only tables that have been changed since the last check or that have not been closed properly. Applies only to MyISAM tables and views; ignored for InnoDB.
54# MEDIUM Scan rows to verify that deleted links are valid. This also calculates a key checksum for the rows and verifies this with a calculated checksum for the keys. Applies only to MyISAM tables and views; ignored for InnoDB.
55# EXTENDED Do a full key lookup for all keys for each row. This ensures that the table is 100% consistent, but takes a long time. Applies only to MyISAM tables and views; ignored for InnoDB.
56TYPE2=
57CORRUPT=no # start by assuming no corruption
58DBNAMES="all" # either "all" or a list delimited by space
59DBEXCLUDE="" # either "" or a list delimited by space
60
61# I/O redirection...
62touch $LOGFILE
63exec 6>&1
64exec > $LOGFILE # stdout redirected to $LOGFILE
65echo -n "AutoMySQLCheck: "
66date
67echo "---------------------------------------------------------"; echo; echo
68
69# Get our list of databases to check...
70if [ "$DBNAMES" = "all" ] ; then
71DBNAMES=""
72ALLDB="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
73for i in $ALLDB ; do
74INCLUDEDB=1
75for j in $DBEXCLUDE ; do
76if [ "$i" = "$j" ] ; then
77INCLUDEDB=0
78fi
79done
80if [ $INCLUDEDB -eq 1 ] ; then
81DBNAMES=$DBNAMES" "$i
82fi
83done
84fi
85
86# Lock tables
87mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "flush tables with read lock; flush logs"
88# Run through each database and execute our CHECK TABLE command for all tables
89# in a single pass - eyechart
90for i in $DBNAMES ; do
91# echo the database we are working on
92echo "Database being checked:"
93echo -n "SHOW DATABASES LIKE '$i'" | mysql -t --host=$DBHOST -u$USER -p$PASSWORD $i; echo
94
95# Check all tables in one pass, instead of a loop
96# Use AWK to put in comma separators, use SED to remove trailing comma
97# Modified to only check MyISAM or InnoDB tables - eyechart
98DBTABLES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" | awk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"
99
100# Output in table form using -t option
101if [ ! "$DBTABLES" ] ; then
102echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
103else
104echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql --host=$DBHOST -t -u$USER -p$PASSWORD $i; echo; echo
105fi
106done
107# Unlock tables
108mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "unlock tables"
109
110exec 1>&6 6>&- # Restore stdout and close file descriptor #6
111
112# test our logfile for corruption in the database...
113for i in `cat $LOGFILE` ; do
114if test $i = "warning" ; then
115CORRUPT=yes
116elif test $i = "error" ; then
117CORRUPT=yes
118fi
119done
120
121# Send off our results...
122if test $CORRUPT = "yes" ; then
123cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
124else
125cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $DBHOST-`date`" $MAILTO
126fi