The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    16
    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:
    16
    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
     
  3. dory36

    dory36 Well-Known Member

    Joined:
    Aug 30, 2003
    Messages:
    179
    Likes Received:
    0
    Trophy Points:
    16
    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:
    18
    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:
    16
    The following command from shell as root will repair all databases on the server

    mysqlcheck --all-databases --auto-repair
     
  6. Un Area

    Un Area Well-Known Member

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

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

    First repairs
    Second Analize
    Third Optimize

    Regards
     
  7. kernow

    kernow Well-Known Member

    Joined:
    Jul 23, 2004
    Messages:
    865
    Likes Received:
    9
    Trophy Points:
    18
    cPanel Access Level:
    Root Administrator
    Just what i was looking for ! thanks :)
     
  8. gudneo

    gudneo Registered

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

Share This Page