checking/repairing all mysql databases

dory36

Well-Known Member
Aug 30, 2003
179
0
166
Our datacenter had a power outage Friday, and since the power cycle, I have had a few complaints about error messages on sites using mysql. I tested numerous sites, and found the error in only a few.

Using the cPanel/whm function to "repair sql database" seems to work to resolve the problem.

But I have to go in and repair each database as they are reported to me -- and the repair database function only shows a few databases in the window which won't expand, and isn't sorted by user name, so it is a slow process just to find the database that has been reported by a user.

Any suggestions on how to check and repair all the databases on the server at one time, rather than waiting for customers to complain?

Thanks - Bill

(mysql 5, btw)
 

Murtaza_t

Well-Known Member
Jan 24, 2005
476
0
166
Earth
cPanel Access Level
Website Owner
Just Backup your /var/lib/mysql dir and then run this command:

Code:
find /var/lib/mysql/* -type f -name *.MYI -exec myisamchk {} \;
And it should repair all your mysql DB tables.

I have not tested it but it should work for sure.

Also I would suggest you to reupload your avatar as its all scrabbled. :p
 

dory36

Well-Known Member
Aug 30, 2003
179
0
166
Thanks!

(I replaced the avatar -- it used to be this wonderful animated gif that would impress and awe everyone :p , but the forum stopped allowing those, so the old one died...)
 

freedman

Well-Known Member
Feb 13, 2005
314
5
168
I can't remember form where I got this script but.. I have 2 versions.. this one which will "optimize" all the db's.. and another which replaces the "optimize" with "analyze":
to 'repair' run the script with repair as an option

#!/usr/local/bin/php
<?
# Please setup your MySQL Config before continuing
# It is found right here at the top of this file...

#########################################################
# MYSQL CONFIG
#########################################################
global $db_user, $db_password, $db, $db_host;
$db_user = "root";
$db_password = "PASSWORD";
$db = "server";
$db_host = "localhost";

# No additional includes are needed to run this script!
# All functions & config are in this file
# CONFIG SHOWN ABOVE
# FUNCTIONS SHOWN AT END OF SCRIPT

#########################################################
$dbname = isset($_GET["dbname"])?$_GET["dbname"]:'all'; # Try not to edit this, ok? :)
$repair = $_GET["repair"]; # Make this 1 if you want to repair instead
global $DoWhat;
if ($repair==1) {
$DoWhat="REPAIR";
} else {
$DoWhat="OPTIMIZE";
}
#########################################################
# PERMISSIONS required for script to work
#########################################################
# Must have access to MySQL, using the login info above
#
# Must be able to: Show Databases, Show Tables from php
#
# Must be able to: $DoWhat TABLE $db.$table;
#
# Must be able to: Repair Table $db.$table;
#########################################################

#################
# USAGE
#################
# TO OPTIMIZE YOUR ENTIRE SERVER (the $db_host) above
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
#:)

############################
# ACTUAL SCRIPT
############################
if (strstr($dbname,".")) {
# Optimize Single Table Only
$optresult = SmartQuery("ARR $DoWhat TABLE $dbname;");
$tbname = $optresult[0];
$tbstatus = $optresult[3];
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
echo "$tbname: $tbstatus\n";
} else if ($dbname=="") {
echo "No usage commands provided!<br><br>
optimizedb.php usage commands:<br><br>
# TO OPTIMIZE YOUR ENTIRE SERVER
# optimizedb.php?dbname=all
# TO OPTIMIZE A SINGLE DATABASE
# optimizedb.php?dbname=mydatabase
# TO OPTIMIZE A SINGLE TABLE
# optimizedb.php?dbname=mydatabase.mytable
";
} else {
if ($dbname=="all") {
# Optimize Entire Server
$dbs = SmartQuery("BIGARR show databases;");
# echo "<b>Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!<br>" . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . ForceEcho() . "<br><br></b>" . ForceEcho() . ForceEcho() . ForceEcho();
echo "Optimizing " . count($dbs) . " databases: DO NOT STOP YOUR SERVER!!!"; echo "\n\n";
for ($y=0; $y < count($dbs); $y++) {
$dbname=$dbs[$y][0];
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
if ($tbname=="") {
} else {
if ($tbstatus=="") {
} else {
if (strstr($tbstatus,"doesn't exist")) {
} else {
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>" . ForceEcho() . ForceEcho() . ForceEcho();
echo "$tbname: $tbstatus\n";
}
}
}
}
}
} else {
# Optimize One Database
$optlocations = OptimizeDatabase($dbname);
for ($x=0; $x < count($optlocations); $x++) {
$tbname = $optlocations[$x][0][0];
$tbstatus = $optlocations[$x][0][3];
# echo ForceEcho() . "[<font color=darkgreen>$tbstatus</font>] <b>$tbname</b><br>";
echo "$tbname:$tbstatus\n";
}
}
}
###################################################################
# OPTIMIZE DATABASE FUNCTION
###################################################################
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
###################################################################
# Also shows some usage of the SmartQuery Multi-Dimensional Arrays
###################################################################
function OptimizeDatabase($dbname) {
# Gets all of the tables in $dbname, and optimizes them..
global $DoWhat;
$tables = SmartQuery("BIGARR SHOW TABLES FROM `$dbname`;");
$optresult = '';
for ($x=0; $x < count($tables); $x++) {
$table = $tables[$x][0];
$optresult[$x] = SmartQuery("BIGARR $DoWhat TABLE `$dbname`.$table;");
}
return $optresult; # returns format like this: $optresult[$x][0][3] thats the status...
}

#################################################
# SMART QUERY - MULTIDIMENSIONAL ARRAY SUPPORT
#################################################
# usage:
# to return a count: $recordcount = SmartQuery("SELECT count(*) from database.table;");
# to return a single info: $record = SmartQuery("SELECT FirstName from database.table WHERE LastName='jones';");
# to return 1 row as an array: $record = SmartQuery("ARR SELECT * from database.table WHERE LastName='jones';");
# $somethingCol1 = $record[0];
# $somethingCol2 = $record[1];

# to return all rows as a multi-dimensional arrays: $record = SmartQuery("BIGARR SELECT * from database.table WHERE LastName='jones';");
# $somethingRow1Col1 = $record[0][0];
# $somethingRow1Col2 = $record[0][1];
# $somethingRow2Col1 = $record[1][0];
# $somethingRow2Col2 = $record[1][1];

# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!
# Don't edit this!

function SmartQuery ($QueryString) {
global $db, $db_host, $db_user, $db_password;
$con = mysql_connect($db_host,$db_user,$db_password);
if (!$con) {
die("MySQL Database Connection Problem: " . mysql_error() . "\n *");
exit;
}
if (is_array($QueryString)) {
# Array of queries...
for ($x=0; $x < count($QueryString)+1; $x++) {
$arrquery = $QueryString[$x]; # The Query ($x)
if ($arrquery=="") {
# No query
} else {
# has query
if (strtolower(substr($arrquery,0,3))=="arr") {
# Returns a normal ARR of 1 row
$arrquery=trim(substr($arrquery,3));
$returnarray=true;
$returnbig=false;
} else {
if (strtolower(substr($arrquery,0,6))=="bigarr") {
# Returns a big ARR of all rows
$arrquery=trim(substr($arrquery,6));
$returnarray=true;
$returnbig=true;
} else {
$returnarray=false;
$returnbig=false;
}
}
$query = $arrquery;
mysql_select_db("$db");
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($y=1; $y < mysql_num_rows($mysql_result); $y++) {
# Multi-dimensional arrays
$ret[$y] = mysql_fetch_row($mysql_result);
}
$buildret[$x]=$ret;
} else {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
$buildret[$x] = $ret;
} else {
$buildret[$x]=$ret[0];
}
}
} # check empty
} # for x (array queries
return $buildret;
# END OF ARRAY QUERIES
} else {
# BEGIN OF SINGLE QUERY
# Normal query
if (strtolower(substr($QueryString,0,3))=="arr") {
# Returns a normal ARR of 1 row
$QueryString=trim(substr($QueryString,3));
$returnarray=true;
$returnbig=false;
} else {
if (strtolower(substr($QueryString,0,6))=="bigarr") {
# Returns a big ARR of all rows
$QueryString=trim(substr($QueryString,6));
$returnarray=true;
$returnbig=true;
} else {
$returnarray=false;
$returnbig=false;
}
}
$query = $QueryString;
mysql_select_db("$db");
#error_reporting(0);
$mysql_result = mysql_query($query, $con);
if ($returnbig==true) {
# Output large array - special
@$ret[0] = mysql_fetch_row($mysql_result); # set first row
for ($x=1; $x < @mysql_num_rows($mysql_result); $x++) {
$ret[$x] = mysql_fetch_row($mysql_result);
}
return $ret;
} else {
# Normal output
$ret = mysql_fetch_row($mysql_result);
if ($returnarray==true) {
return $ret;
} else {
return $ret[0];
}
}
}
}

# Forces the screen to echo some information back, by flooding it with spaces. Do not use this in a loop with 1 million or more, or it could fill your entire screen up to a GB of data.
# use this to force status onto the screen.
# You can edit this :P
# You can edit this :P
# You can edit this :P
# You can edit this :P

function ForceEcho() {
/*
return "









.";
*/
}
?>
 

Un Area

Well-Known Member
Nov 16, 2006
90
1
156
Also you can run

mysqlcheck --all-databases -r
mysqlcheck --all-databases -a
mysqlcheck --all-databases -o

First repairs
Second Analize
Third Optimize

Regards
 
Thread starter Similar threads Forum Replies Date
albatroz Databases 2
F Databases 0