CHECK TABLEtbl_name
[,tbl_name
] ... [option
] ...option
= {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
CHECK TABLE
checks a table or
tables for errors. CHECK TABLE
works for MyISAM
, InnoDB
,
ARCHIVE
, and CSV
tables.
For MyISAM
tables, the key statistics are
updated as well.
To check a table, you must have some privilege for it.
CHECK TABLE
can also check views
for problems, such as tables that are referenced in the view
definition that no longer exist.
CHECK TABLE
is supported for
partitioned tables, and you can use ALTER TABLE ...
CHECK PARTITION
to check one or more partitions; for
more information, see Section 12.1.6, “ALTER TABLE
Syntax”, and
Section 17.3.3, “Maintenance of Partitions”.
CHECK TABLE
returns a result set
with the following columns.
Column | Value |
Table |
The table name |
Op |
Always check
|
Msg_type |
status , error ,
info , or warning
|
Msg_text |
An informational message |
Note that the statement might produce many rows of information
for each checked table. The last row has a
Msg_type
value of status
and the Msg_text
normally should be
OK
. If you don't get OK
,
or Table is already up to date
you should
normally run a repair of the table. See
Section 6.6, “MyISAM
Table Maintenance and Crash Recovery”. Table is
already up to date
means that the storage engine for
the table indicated that there was no need to check the table.
The FOR UPGRADE
option checks whether the
named tables are compatible with the current version of MySQL.
With FOR UPGRADE
, the server checks each
table to determine whether there have been any incompatible
changes in any of the table's data types or indexes since the
table was created. If not, the check succeeds. Otherwise, if
there is a possible incompatibility, the server runs a full
check on the table (which might take some time). If the full
check succeeds, the server marks the table's
.frm
file with the current MySQL version
number. Marking the .frm
file ensures that
further checks for the table with the same version of the server
will be fast.
Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.
Currently, FOR UPGRADE
discovers these
incompatibilities:
The indexing order for end-space in
TEXT
columns for
InnoDB
and MyISAM
tables changed between MySQL 4.1 and 5.0.
The storage method of the new
DECIMAL
data type changed
between MySQL 5.0.3 and 5.0.5.
If your table was created by a different version of the
MySQL server than the one you are currently running,
FOR UPGRADE
indicates that the table has
an .frm
file with an incompatible
version. In this case, the result set returned by
CHECK TABLE
contains a line
with a Msg_type
value of
error
and a Msg_text
value of Table upgrade required. Please do "REPAIR
TABLE `
tbl_name
`" to fix
it!
Changes are sometimes made to character sets or collations
that require table indexes to be rebuilt. For details about
these changes and when FOR UPGRADE
detects them, see
Section 2.12.3, “Checking Whether Tables or Indexes Must Be Rebuilt”.
The other check options that can be given are shown in the
following table. These options are passed to the storage engine,
which may use them or not. MyISAM
uses them;
they are ignored for InnoDB
tables and views.
Type | Meaning |
QUICK |
Do not scan the rows to check for incorrect links. |
FAST |
Check only tables that have not been closed properly. |
CHANGED |
Check only tables that have been changed since the last check or that have not been closed properly. |
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. |
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. |
If none of the options QUICK
,
MEDIUM
, or EXTENDED
are
specified, the default check type for dynamic-format
MyISAM
tables is MEDIUM
.
This has the same result as running myisamchk
--medium-check tbl_name
on
the table. The default check type also is
MEDIUM
for static-format
MyISAM
tables, unless
CHANGED
or FAST
is
specified. In that case, the default is
QUICK
. The row scan is skipped for
CHANGED
and FAST
because
the rows are very seldom corrupted.
You can combine check options, as in the following example that does a quick check on the table to determine whether it was closed properly:
CHECK TABLE test_table FAST QUICK;
In some cases, CHECK TABLE
changes the table. This happens if the table is marked as
“corrupted” or “not closed properly”
but CHECK TABLE
does not find
any problems in the table. In this case,
CHECK TABLE
marks the table as
okay.
If a table is corrupted, it is most likely that the problem is in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.
If you just want to check a table that you assume is okay, you
should use no check options or the QUICK
option. The latter should be used when you are in a hurry and
can take the very small risk that QUICK
does
not find an error in the data file. (In most cases, under normal
usage, MySQL should find any error in the data file. If this
happens, the table is marked as “corrupted” and
cannot be used until it is repaired.)
FAST
and CHANGED
are
mostly intended to be used from a script (for example, to be
executed from cron) if you want to check
tables from time to time. In most cases, FAST
is to be preferred over CHANGED
. (The only
case when it is not preferred is when you suspect that you have
found a bug in the MyISAM
code.)
EXTENDED
is to be used only after you have
run a normal check but still get strange errors from a table
when MySQL tries to update a row or find a row by key. This is
very unlikely if a normal check has succeeded.
Use of CHECK TABLE
... EXTENDED
might influence the execution plan
generated by the query optimizer.
Some problems reported by CHECK
TABLE
cannot be corrected automatically:
Found row where the auto_increment column has the
value 0
.
This means that you have a row in the table where the
AUTO_INCREMENT
index column contains the
value 0. (It is possible to create a row where the
AUTO_INCREMENT
column is 0 by explicitly
setting the column to 0 with an
UPDATE
statement.)
This is not an error in itself, but could cause trouble if
you decide to dump the table and restore it or do an
ALTER TABLE
on the table. In
this case, the AUTO_INCREMENT
column
changes value according to the rules of
AUTO_INCREMENT
columns, which could cause
problems such as a duplicate-key error.
To get rid of the warning, simply execute an
UPDATE
statement to set the
column to some value other than 0.
If CHECK TABLE
finds a
problem for an InnoDB
table, the server
shuts down to prevent error propagation. Details of the
error will be written to the error log.
User Comments
Here is a php function you can use in a cron php script to periodically check the database for errors.
I check with $fast = true every 5 minutes and $fast = false every day.
# CheckSqlTables()
# Checks all tables in a database. Returns true if
# everything went successfully; returns false and fills
# out $msg if it did not.
function CheckSqlTables(&$msg, $fast = true) {
$msg = "";
ConnectToDb(); // Connect to your database here.
$rs_tables = mysql_query("SHOW TABLES");
if (!$rs_tables || mysql_num_rows($rs_tables) <= 0) {
$msg = "Could not iterate database tables\n";
return false;
}
$bOk = true;
$checktype = "";
if ($fast)
$checktype = "FAST";
while (list($tname) = mysql_fetch_row($rs_tables)) {
$rs_status = mysql_query("CHECK TABLE `$tname` $checktype");
if (!$rs_status || mysql_num_rows($rs_status) <= 0) {
$msg .= "Could not get status for table $tname\n";
$bOk = false;
continue;
}
# Seek to last row
mysql_data_seek($rs_status, mysql_num_rows($rs_status)-1);
$row_status = mysql_fetch_assoc($rs_status);
if ($row_status['Msg_type'] != "status") {
$msg .= "Table {$row_status['Table']}: ";
$msg .= "{$row_status['Msg_type']} = {$row_status['Msg_text']}\n";
$bOk = false;
}
}
return $bOk;
}
I've taken Zach Gorman's example and made it directly ready for use via cron. Tested with PHP 4.3.2 on FreeBSD 4.3.
----------------------------------------------
#!/usr/local/bin/php
<?php
/*
* Script to check the validity of MYSQL tables by running CHECK TABLE
* on every table in the given database.
*
* To use:
* php -f <path_to_script> [-- options]
*
* (need to use "--" to separate script options from PHP options
*
* Example:
* php -f /usr/local/bin/check_mysql_tables.php -- -verbose -fast
*
* An example cron entry, checks each day at 2:02am:
* 2 2 * * * /bin/php -f /bin/check_mysql_tables.php
*
*/
$usage = "Checks the validity of tables in a mysql database\n\n" .
"Usage: " . $argv[0] . " [options]\n" .
" -f[ast] perform a fast check (CHECK TABLE tblName FAST)\n" .
" -<?|h|x> show usage string (this message)\n" .
" -v[erbose] display verbose messages while running\n";
$msg = "";
$fast = false;
$verbose = false;
$server = "localhost";
$database = "test";
$uid = "root";
$pwd = "";
$tcount = 0;
array_shift($argv); // take out the script name
foreach ($argv as $option) {
switch ($option) {
case '-f':
case '-fast':
$fast = true;
break;
case '-v':
case '-verbose':
$verbose = true;
echo "Verbose enabled\n";
break;
case '-x':
case '-?':
case '-h':
die($usage);
break;
default:
die("Unknown parameter: " . $option . "\n\n" . $usage);
break;
}
}
if ( ! mysql_connect($server, $uid, $pwd) ) {
die("Failed connecting to server: " . mysql_error());
}
if ($verbose) echo "Connected to server: $server\n";
if ( ! mysql_select_db($database) ) {
die( "Failed selecting database '$database': " . mysql_error() );
}
if ($verbose) echo "Selected database: " . $database . "\n";
$rs_tables = mysql_query("SHOW TABLES");
if (!$rs_tables || (($num_tables = mysql_num_rows($rs_tables)) <= 0) ) {
die( "Could not iterate database tables\n" );
}
if ($verbose) echo "Number of tables: $num_tables\n";
$bOk = true;
$checktype = "";
if ($fast) $checktype = "FAST";
while (list($tname) = mysql_fetch_row($rs_tables)) {
$query = "CHECK TABLE `$tname` $checktype";
if ($verbose) printf("%3d. $query:\n", ++$tcount);
$rs_status = mysql_query( $query );
if (!$rs_status || mysql_num_rows($rs_status) <= 0 ) {
$msg .= "Could not get status for table $tname\n";
$bOk = false;
continue;
}
// seek to last row
mysql_data_seek($rs_status, mysql_num_rows($rs_status)-1);
$row_status = mysql_fetch_assoc($rs_status);
if ($row_status['Msg_type'] != "status") {
$msg .= "Table {$row_status['Table']}: ";
$msg .= "{$row_status['Msg_type']} = {$row_status['Msg_text']}\n";
$bOk = false;
if ($verbose) echo " ** Check failed!!\n";
}
if ($verbose) {
echo " {$row_status['Msg_type']} -> {$row_status['Msg_text']}\n";
}
}
if ( ! $bOk ) die( "Check failed: \n\n" . $msg );
exit(0);
?>
Here is a bash script insipired by the php code posted here by Greg Fenton and Zach Gorman. It works nicely with cron, and has been tested on both linux and darwin. Note that on darwin, the dbexclude feature will not work due to word boundary anchor bugs in regex.
#!/bin/bash
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# sbray@csc.uvic.ca, UVic Fine Arts 2004.
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
# system variables (change these according to your system)
USER=username
PASSWORD=password
DBHOST=localhost
LOGFILE=./mysql_check.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space
# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE
echo -n "Logfile: "
date
echo "---------------------------------------------------------"
echo
# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi
# Run through each database and execute our CHECK TABLE command for each table...
for i in $DBNAMES
do
# to fancy up our log file
echo ""
echo "Database: $i"
echo "---------------------------------------------------------"
DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show tables"`"
for j in $DBTABLES
do
echo "CHECK TABLE $j $TYPE1 $TYPE2" | mysql -u$USER -p$PASSWORD $i
done
echo ""
done
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done
# send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi
For anyone who refuses to give up their VB6 (like me), here's a code snippet for you. You need to make sure you have the Microsoft Active X Data Objects Library referenced in your project.
Private Sub dbCheck()
Dim chkConn, chkRs
Set chkConn = New ADODB.Connection
Set chkRs = New ADODB.Recordset
chkConn.CursorLocation = adUseClient
chkConn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};uid=maximus;password=allhail;server=pablo;database=mexico;option=" & 1 + 2 + 8 + 32 + 2048 + 16384
chkConn.Open
chkRs.Open = chkConn.Execute("CHECK TABLE `inventory` MEDIUM") 'create a recordset using the results from CHECK TABLE
chkRs.MoveLast 'last row will contain the status
strMsgText = StrConv(chkRs("Msg_text"), vbProperCase)
strMsgType = StrConv(chkRs("Msg_type"), vbProperCase)
If chkRs("Msg_text") = "OK" Then
MsgBox "Check successful. No errors reported", vbInformation + vbOKOnly, App.Title
GoTo cleanup
Else
msg = "Severity: " & strMsgType & vbCrLf & _
"Message: " & strMsgText & vbCrLf & vbCrLf & _
"Please run DBRepair or contact technical support for assistance."
MsgBox msg, vbCritical, "DBCheck reported the following error(s):"
GoTo cleanup
End If
cleanup:
'Close database connections and release variables
chkRs.Close
chkConn.Close
Set chkRs = Nothing
Set chkConn = Nothing
End Sub
I changed up and improved the code posted by Stu Bray. Check out the change log for details.
-eyechart
-----
<?bash
#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.1 - (2005-02-22)
# Named script automysqlcheck.sh
# Added PATH variable to make this script more CRON friendly
# Removed the $DBTABLES loop and replaced it with single command
# that executes the CHECK TABLE command on all tables in a given DB
# Changed code to only check MyISAM and InnoDB tables
# Cleaned up output to make the email prettier
# Modified script to skip databases that have no tables
# VER 1 - (2004-09-24)
# Initial release by sbray@csc.uvic.ca
# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space
# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE
echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo
# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi
# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
# echo the database we are working on
echo "Database being checked:"
echo -n "SHOW DATABASES LIKE '$i'" | mysql -t -u$USER -p$PASSWORD $i; echo
# Check all tables in one pass, instead of a loop
# Use GAWK to put in comma separators, use SED to remove trailing comma
# Modified to only check MyISAM or InnoDB tables - eyechart
DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" \
| gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print $1}' | sed 's/, $//'`"
# Output in table form using -t option
if [ ! "$DBTABLES" ]
then
echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
else
echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql -t -u$USER -p$PASSWORD $i; echo; echo
fi
done
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done
# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi
?>
Hallo eyechart,
When you can not guarantee that no users work with the table, it is better to work with "FLUSH TABLES" and "UNLOCK TABLES".
So the line with "CHECK TABLE...." we can add:
echo "FLUSH TABLES WITH READ LOCK; CHECK TABLE $DBTABLES $TYPE1 $TYPE2; UNLOCK TABLES;" | mysql -t -u$USER -p$PASSWORD $i; echo; echo
I think this is safty.
Ronald Laszlob
Note that CHECK TABLE requires SELECT privileges only.
The above script has a flaw that prevents it from finding and reporting some corrupted tables.
It appears that if a table is completely corrupted, MyISAM does not appear in the 'Engine' column. Thus the script skips the table. Here is an example of output showing this case:
> show table status \G
[ ... snip ...]
*************************** 3. row ***************************
Name: residential
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: Can't open file: 'residential.MYI' (errno: 145)
Thus this table is simply missing from the results. If you have hundreds of tables, it is pretty easy to not realize that one is missing.
Matthew Haines writes on August 17 2005 12:22pm:
>>The above script has a flaw that prevents it from finding and reporting some corrupted tables.
There are at least three scripts "above," all doing similar things. Which one are you refering to? Or all of them?
Thanks.
I've made one small improvement to this script that is simple but I post it so the less geeky won't run into trouble.
I skipped the colour coding as the first backlash in "\`" $1 "\`" disappeared.
Check out the change log for details.
/ Mickael Sundberg
-----
#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se
# (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.2 - (2006-10-29)
# Added "\`" arround the tables in $DBTABLES, otherwise it'll create
# errors if tablenames containt characters like -.
# Modified by Mickael Sundberg
# VER 1.1 - (2005-02-22)
# Named script automysqlcheck.sh
# Added PATH variable to make this script more CRON friendly
# Removed the $DBTABLES loop and replaced it with single command
# that executes the CHECK TABLE command on all tables in a given DB
# Changed code to only check MyISAM and InnoDB tables
# Cleaned up output to make the email prettier
# Modified script to skip databases that have no tables
# Modified by eyechart
# VER 1 - (2004-09-24)
# Initial release by sbray@csc.uvic.ca
# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space
# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE
echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo
# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi
# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
# echo the database we are working on
echo "Database being checked:"
echo -n "SHOW DATABASES LIKE '$i'" | mysql -t -u$USER -p$PASSWORD $i; echo
# Check all tables in one pass, instead of a loop
# Use GAWK to put in comma separators, use SED to remove trailing comma
# Modified to only check MyISAM or InnoDB tables - eyechart
DBTABLES="`mysql --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" \
| gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"
# Output in table form using -t option
if [ ! "$DBTABLES" ]
then
echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
else
echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql -t -u$USER -p$PASSWORD $i; echo; echo
fi
done
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done
# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $HOST-`date`" $MAILTO
fi
One small tweak to Mickael Sundberg's version of the script posted above, to get the script to pay attention to the DBHOST variable at the top:
#!/bin/bash
#
# #automysqlcheck.sh
#
# This is a small bash script that checks all mysql databases for errors
# and mails a log file to a specified email address. All variables are
# hardcoded for ease of use with cron. Any databases you wish not to check
# should be added to the DBEXCLUDE list, with a space in between each name.
#
# Note that DBEXCLUDE will only work with GNU sed, as BSD regular expressions
# on Darwin seem to have some trouble with word boundary anchors.
#
# original version by sbray@csc.uvic.ca, UVic Fine Arts 2004
#
# modified by eyechart AT gmail.com and Mickael Sundberg at mickael@pischows.se and Jake Carr jake-+AT+-websitesource-+DOT+-com
# (see Change Log for details)
#
# Some of this code was inspired from automysqlbackup.sh.2.0
# http://sourceforge.net/projects/automysqlbackup/
#
#=====================================================================
# Change Log
#=====================================================================
#
# VER 1.3 - (2006-12-02)
# Added --host=$DBHOST in mysql commands, so it's useful for non-localhost situations
# Jake Carr
# VER 1.2 - (2006-10-29)
# Added "\`" arround the tables in $DBTABLES, otherwise it'll create
# errors if tablenames containt characters like -.
# Modified by Mickael Sundberg
# VER 1.1 - (2005-02-22)
# Named script automysqlcheck.sh
# Added PATH variable to make this script more CRON friendly
# Removed the $DBTABLES loop and replaced it with single command
# that executes the CHECK TABLE command on all tables in a given DB
# Changed code to only check MyISAM and InnoDB tables
# Cleaned up output to make the email prettier
# Modified script to skip databases that have no tables
# Modified by eyechart
# VER 1 - (2004-09-24)
# Initial release by sbray@csc.uvic.ca
# system variables (change these according to your system)
PATH=/usr/local/bin:/usr/bin:/bin:$PATH
USER=root
PASSWORD=
DBHOST=localhost
LOGFILE=/var/log/automysqlcheck.log
MAILTO=root@localhost
TYPE1= # extra params to CHECK_TABLE e.g. FAST
TYPE2=
CORRUPT=no # start by assuming no corruption
DBNAMES="all" # or a list delimited by space
DBEXCLUDE="" # or a list delimited by space
echo -n "AutoMySQLCheck: "
date
echo "---------------------------------------------------------"; echo; echo
# Get our list of databases to check...
# NOTE: the DBEXCLUDE feature seemed to only work with Linux regex, GNU sed
if test $DBNAMES = "all" ; then
DBNAMES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD --batch -N -e "show databases"`"
for i in $DBEXCLUDE
do
DBNAMES=`echo $DBNAMES | sed "s/\b$i\b//g"`
done
fi
# Run through each database and execute our CHECK TABLE command for all tables
# in a single pass - eyechart
for i in $DBNAMES
do
# echo the database we are working on
echo "Database being checked:"
echo -n "SHOW DATABASES LIKE '$i'" | mysql -t --host=$DBHOST -u$USER -p$PASSWORD $i; echo
# Check all tables in one pass, instead of a loop
# Use GAWK to put in comma separators, use SED to remove trailing comma
# Modified to only check MyISAM or InnoDB tables - eyechart
DBTABLES="`mysql --host=$DBHOST --user=$USER --password=$PASSWORD $i --batch -N -e "show table status;" | gawk 'BEGIN {ORS=", " } $2 == "MyISAM" || $2 == "InnoDB"{print "\`" $1 "\`"}' | sed 's/, $//'`"
# Output in table form using -t option
if [ ! "$DBTABLES" ]
then
echo "NOTE: There are no tables to check in the $i database - skipping..."; echo; echo
else
echo "CHECK TABLE $DBTABLES $TYPE1 $TYPE2" | mysql --host=$DBHOST -t -u$USER -p$PASSWORD $i; echo; echo
fi
done
exec 1>&6 6>&- # Restore stdout and close file descriptor #6
# test our logfile for corruption in the database...
for i in `cat $LOGFILE`
do
if test $i = "warning" ; then
CORRUPT=yes
elif test $i = "error" ; then
CORRUPT=yes
fi
done
# Send off our results...
if test $CORRUPT = "yes" ; then
cat $LOGFILE | mail -s "MySQL CHECK Log [ERROR FOUND] for $DBHOST-`date`" $MAILTO
else
cat $LOGFILE | mail -s "MySQL CHECK Log [PASSED OK] for $DBHOST-`date`" $MAILTO
fi
There is an error in the above example that prevents logfile generation. The block below is missing:
# I/O redirection...
touch $LOGFILE
exec 6>&1
exec > $LOGFILE # stdout redirected to $LOGFILE
It should be between:
DBEXCLUDE="" # or a list delimited by space
and:
echo -n "AutoMySQLCheck: "
I also found it a more useful default to set $dbhost=localhost to
$dbhost=`hostname -s`
Starting with 5.0.51 or 5.0.67 the scripts above will have a problem when they perform a CHECK TABLE on the information_schema MyISAM tables, as these produce an error with the msg_text of:
Table upgrade required. Please do "REPAIR TABLE `/tmp/#sql_162_0`" to fix it!
This is a problem introduced in one of the two versions. Even with a completely clean install, and no newly created databases, this message will result from the check. Modifying the shell script to do something like (in context):
for i in $DBNAMES
do
if test $i = 'information_schema'
then
continue
fi
# echo the database we are working on
...
This will just exclude the information_schema database from the checks, thus preventing seeing the error that is not really an error. A similar fix could be done with the php script as well.
Add your own comment.