Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

checking/repairing all mysql databases

Discussion in 'General Discussion' started by dory36, Apr 15, 2007.

  1. dory36

    dory36 Well-Known Member

    Joined:
    Aug 30, 2003
    Messages:
    179
    Likes Received:
    0
    Trophy Points:
    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)
     
  2. Murtaza_t

    Murtaza_t Well-Known Member

    Joined:
    Jan 24, 2005
    Messages:
    476
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. dory36

    dory36 Well-Known Member

    Joined:
    Aug 30, 2003
    Messages:
    179
    Likes Received:
    0
    Trophy Points:
    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...)
     
  4. freedman

    freedman Well-Known Member

    Joined:
    Feb 13, 2005
    Messages:
    312
    Likes Received:
    1
    Trophy Points:
    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 "









    .";
    */
    }
    ?>
     
  5. david510

    david510 Well-Known Member

    Joined:
    Aug 22, 2004
    Messages:
    473
    Likes Received:
    0
    Trophy Points:
    166
    The following command from shell as root will repair all databases on the server

    mysqlcheck --all-databases --auto-repair
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. Un Area

    Un Area Well-Known Member

    Joined:
    Nov 16, 2006
    Messages:
    90
    Likes Received:
    0
    Trophy Points:
    156
    Also you can run

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

    First repairs
    Second Analize
    Third Optimize

    Regards
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  7. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    920
    Likes Received:
    13
    Trophy Points:
    168
    cPanel Access Level:
    Root Administrator
    Just what i was looking for ! thanks :)
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. gudneo

    gudneo Registered

    Joined:
    Jan 22, 2007
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    151
Loading...

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice