What are maintenance steps and performance steps for Mysql server/database?_问答_开发者

Like index maintenance we have index reorganize/rebuild, upd开发者_如何学Goate stats, shrink database log files , database backup/restore in MS SQL server, What are maintenance steps and performance steps for Mysql server/database ?
ANALYZE TABLE will scan keys and store data that'll help future JOINs. I don't know how useful/needed it actually is but I bet it surely depends on what your typical db workload and table structure are.
OPTIMIZE TABLE will defragment your tables and reclaim unused space but, as you can read in the documentation, it is often unnecessary at all.
You can invoke them via SQL or with the mysqlcheck tool.
While mostly unnecessary, I usually schedule a mysqlcheck -Aao during low activity times (that means weekend nights in my case).
//--------------------------------------------------------------
//Here is a php script you can run weekly.  Just change the config info at the start
//--------------------------------------------------------------




//--------------------------------------------------------------
// BEGIN CONFIG  (I like to put this in a sep file, so I can just change the config for
// different database/servers
//--------------------------------------------------------------

//require_once ("mysql_maintenance_config.inc");  

  $_DB_Host = '127.0.0.1';          // your server ip
  $_DB_Name = 'database_name_goes_here';       // name of db to maintain       
  $_DB_User = 'database_user_goes_here';
  $_DB_Pass = 'pasword_goes_here';

  $_EMAIL_Admin_Addr = 'yourname@gmail.com';   // address to email errors/results to
                                               // php mail must be configured
  $_LOG_TABLE_NAME = 'log_mysql_maintenance';  // change this if you would like the results stored in a different table name
  $_EMAIL_Results = true;
  $_NO_WRITE_TO_BINLOG = false;  // only set to true if database is replicated
  $_SKIP_TABLES_WITH_LESS_THAN_X_ROWS = 100; // set to -1 to process all tables

//--------------------------------------------------------------
// END CONFIG  SECTION
//--------------------------------------------------------------



$total_query_time_for_page = 0;
$debug = false;

// set debug mode
if (isset($_GET['dxdb'])) 
{ 
    if ($_GET['dxdb']=='dcv') { $debug = true; }
} 

// open database connection
$db = mysql_connect ($_DB_Host, $_DB_User, $_DB_Pass);

if (!$db) 
{
    echo 'DATABASE_ERROR_MESSAGE';
    exit;   
}

if (mysql_select_db($_DB_Name, $db)==false)
{
    echo 'CANNOT_SELECT_DATABASE';
    exit;   
}


//--------------------------------------------------------------
// MYSQL MAINTENANCE TASKS
//--------------------------------------------------------------
// 1. Verify / Create Logging Table
// 2. Init Process Log
// 3. Build Table List
// 4. Check and Repair (MyISAM only) Tables if Necessary 
// 5. Optimize Tables 
// 6. Analyze Tables
// 7. Complete Logs and Email
//--------------------------------------------------------------

$_PROCESS_LOG = '';
$_PROCESS_ID = 0;
$_STARTED_ON = '';
$_COMPLETED_ON = 0;
$_TABLE_COUNT = 0;
$_TABLES_CHECKED = 0;
$_TABLES_OPTIMIZED = 0; 
$_TABLES_ANALYZED = 0; 
$_ERRORS_FOUND = 0; 
$_ERRORS_FIXED = 0; 
$_CURRENT_STATUS = '';                  
$_FINAL_STATUS = '';



//--------------------------------------------------------------
// 1. Verify / Create Logging Table  (log table name set in sql_maintenance_config.php)
//--------------------------------------------------------------

$lgx = dbGetReturnForSQL("SELECT COUNT(*) as ct FROM information_schema.tables WHERE table_schema = '$_DB_Name' AND table_name = '$_LOG_TABLE_NAME'",'',true);

if ($lgx==0)  // create logging table
{

    $sql = "CREATE TABLE $_LOG_TABLE_NAME (                              
             process_id int(11) NOT NULL auto_increment,                     
             started_on varchar(24) NOT NULL default '',                  
             completed_on varchar(24) NOT NULL default '',                  
             tables_checked int(11) NOT NULL default 0,                     
             tables_optimized int(11) NOT NULL default 0,                     
             tables_analyzed int(11) NOT NULL default 0,                     
             errors_found int(11) NOT NULL default 0,                     
             errors_fixed int(11) NOT NULL default 0,                     
             current_status varchar(32) NOT NULL default '',                    
             final_status varchar(32) NOT NULL default '',                    
             process_log varchar(20000) NOT NULL default '',                   
             PRIMARY KEY  (process_id)               
           ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";

    dbExecuteSQL($sql);           

    $_PROCESS_LOG = "FIRST RUN - CREATING LOGGING TABLE \n -------------------------------------------------- \n";
}
else
{
    $_PROCESS_LOG = "LOG TABLE OK - BEGINING PROCESS \n -------------------------------------------------- \n";        

}




//--------------------------------------------------------------
// 2. Init Process Log
//--------------------------------------------------------------

$_STARTED_ON = getLogStamp();
$_CURRENT_STATUS = 'INIT PROCESS LOG';
$_FINAL_STATUS = 'IN PROCESS - NO ERRORS';

$sql = "INSERT INTO $_LOG_TABLE_NAME (started_on, current_status, final_status, process_log) VALUES ('$_STARTED_ON', '$_CURRENT_STATUS', '$_FINAL_STATUS', '$_PROCESS_LOG')";

$_PROCESS_ID = dbExecuteSQL($sql, true);

add2ProcessLog('Init Process Log', true);


if ($_NO_WRITE_TO_BINLOG)
{
    $_BINLOG = 'NO_WRITE_TO_BINLOG';
    add2ProcessLog('BINLOG WRITE DISABLED');   
}
else
{
    $_BINLOG = '';
    add2ProcessLog('BINLOG WRITE ENABLED');       
}




//--------------------------------------------------------------
// 3. Build Table List
//--------------------------------------------------------------

$_CURRENT_STATUS = 'BUILD TABLE LIST';

add2ProcessLog('Building Table List', true);

$sql = "SELECT TABLE_NAME, ENGINE, TABLE_ROWS 
        FROM information_schema.tables 
        WHERE table_schema = '$_DB_Name'";

$_TABLE_LIST = dbGetReturnForSQL($sql);

if ($_TABLE_LIST == 'x_no_data_x')  // no tables found
{
    add2ProcessLog("ERROR - NO TABLES FOUND", false);
    emailLogAndExit();
}
else
{
    $_TABLE_COUNT = count($_TABLE_LIST);   
    add2ProcessLog($_TABLE_COUNT . " tables found", true);
}





//--------------------------------------------------------------
// 4. Check and Repair (MyISAM only) Tables if Necessary 
//--------------------------------------------------------------

$_CURRENT_STATUS = 'CHECK AND REPAIR MYISAM TABLES';

add2ProcessLog("CHECK AND REPAIR MYISAM TABLES", true);

foreach($_TABLE_LIST AS $tbl_row)
{
    $tbl = $tbl_row['TABLE_NAME'];
    $eng = $tbl_row['ENGINE'];
    $rct = $tbl_row['TABLE_ROWS'];

    if ($tbl != $_LOG_TABLE_NAME && $eng == 'MyISAM')
    {
        $_TABLES_CHECKED++;

        add2ProcessLog("checking: $tbl");
        $sql = "CHECK TABLE $tbl";
        $rs = dbGetReturnForSQL($sql);      

        if ($rs == 'x_no_data_x')
        {
            emailLogAndExit("ERROR CHECKING TABLE: $tbl");
        }
        else
        {
            $rw = getLastRecord($rs);      
            if ($rw['Msg_text'] != 'OK')
            {
                $_ERRORS_FOUND++;
                add2ProcessLog('ERROR FOUND: ' . $rw['Msg_text'], true);

                // problem checking table - attempt to repair   
                $sql = "REPAIR TABLE $tbl";
                $rs = dbGetReturnForSQL($sql); 
                if ($rs == 'x_no_data_x')
                {
                    emailLogAndExit("ERROR REPAIRING TABLE: $tbl");
                }
                else
                {                
                    $rprw = getLastRecord($rs); 
                    if ($rw['Msg_text'] == 'OK')
                    {
                        $_ERRORS_FIXED++;
                        add2ProcessLog('ERROR REPAIRED on ' . $tbl);
                    }      
                    else   //could not repair error
                    {
                        emailLogAndExit("ERROR REPAIRING TABLE: $tbl | " . $rw['Msg_text']);    
                    }
                }
            }
            else
            {
                add2ProcessLog($tbl . ' checked ok');       
            }
        }
    }
}

add2ProcessLog("CHECK AND REPAIR MYISAM TABLES COMPLETED OK", true); 

//--------------------------------------------------------------
// 5. Optimize Tables 
//--------------------------------------------------------------

$_CURRENT_STATUS = 'OPTIMIZE TABLES'; 

add2ProcessLog("OPTIMIZE TABLES", true); 

foreach($_TABLE_LIST AS $tbl_row)
{
    $tbl = $tbl_row['TABLE_NAME'];
    $eng = $tbl_row['ENGINE'];
    $rct = $tbl_row['TABLE_ROWS'];

    if ($tbl != $_LOG_TABLE_NAME && intval($_SKIP_TABLES_WITH_LESS_THAN_X_ROWS)  'Jacks Function', '//#_reason_//#' => 'Because I said so')
    // $new_page:   if true, clear buffer and wrap in new page, if false, display err msg and exit  (note, for this to be used, ob_start() must have been called on the page first
    // $dev_dump:   any misc info you would like in the log, such as the query that caused the error.  this will not be displayed to the user
    // $email_err:  email address to send error to. if set to 'devteam' then the current group mailing addr will be used (i.e. devteam@thinklinklearning.com)
    //  FUTURE - might want to set a way to override this in the error_codes table to send to a specific developer during testing ( currently can only override to always email )
    // $log_only:   if true, do not display message, return to code  -could be used if we wanted to log here from perish() 

    global $db, $_EMAIL_Admin_Addr, $_CURRENT_STATUS, $_FINAL_STATUS;

    // log the error first

    $host_info = mysql_get_host_info();

    $rq = substr(var_export($_REQUEST,true),0,2000);
    $rq = mysql_real_escape_string($rq);

    // append last sql error to dev_dump

    $sql_dump = $dev_dump;

    // format sql

    $sql_dump = str_replace("\r\n",'',$sql_dump); 
    $sql_dump = str_replace("\n",'',$sql_dump);
    $sql_dump = str_replace("\t",'',$sql_dump);    

    $sql_dump = str_ireplace('SELECT',"\r\n SELECT",$sql_dump);
    $sql_dump = str_ireplace('FROM',"\r\n FROM",$sql_dump);
    $sql_dump = str_ireplace('WHERE',"\r\n WHERE",$sql_dump);
    $sql_dump = str_ireplace('INNER JOIN',"\r\n INNER JOIN",$sql_dump);
    $sql_dump = str_ireplace('LEFT JOIN',"\r\n LEFT JOIN",$sql_dump);
    $sql_dump = str_ireplace('AND',"\r\n AND",$sql_dump);
    $sql_dump = str_ireplace('GROUP BY',"\r\n GROUP BY",$sql_dump);
    $sql_dump = str_ireplace('ORDER BY',"\r\n ORDER BY",$sql_dump);
    $sql_dump = str_ireplace(',',"\r\n, ",$sql_dump);

    $sql_dump = str_replace('  ',' ',$sql_dump);
    $sql_dump = str_replace('  ',' ',$sql_dump);
    $sql_dump = str_replace('  ',' ',$sql_dump);

    $sql_err = mysql_error();
    $dev_dump = $sql_dump . "\r\n\r\n" . $sql_err;

    //echo "
 $sql_err
$sql_dump";

    $dev_dump_to_mail = $host_info . "\r\n\r\n" . $dev_dump;
    $dev_dump = mysql_real_escape_string($dev_dump_to_mail . "\r\n\r\n" . $dev_dump);

    // get stack backtrace
    $a = debug_backtrace();
    $stack_tr = "stack trace: \r\n";
    $stack_tr .= "------------ \r\n"; 

    foreach($a as $k => $v)
    {
        $stack_tr .= "file: $v[file] \r\n"; 
        $stack_tr .= "line: $v[line] \r\n"; 
        $stack_tr .= "func: $v[function] \r\n"; 

        $args = $v['args'];

        foreach($args as $a_label => $a_val)
        {
            $stack_tr .= "$a_label => $a_val \r\n"; 
        }


        $stack_tr .= "//# \r\n";   
    }


    $pg = mysql_real_escape_string($_SERVER['PHP_SELF']);

    $stack_tr_to_mail = $stack_tr;
    $stack_tr = mysql_real_escape_string($stack_tr);

    if ($err_id=='') { $err_id = 'GENERAL_ERROR'; }

    $email_err = $_EMAIL_Admin_Addr;  // always email errors to admin
    $err_html = '';

    // email error if requested
    if ($email_err!='')
    {   
        $pg = explode('/', $pg);
        $pg = $pg[count($pg)-1];

        if (strpos(strtoupper($_SERVER['SERVER_NAME']),'DEV')>2) { $fsrv = ' (DEV)'; } else { $fsrv = ''; }

        mail($email_err, "Error$fsrv: $pg - $err_id ", 
"PHP_SELF\t $_SERVER[PHP_SELF]
\n\n------------------------------------\n\n
error_id:\t $err_id\n\n
\n\n------------------------------------\n\n
sql_err:\t $sql_err \n\n
\n\n------------------------------------\n\n
sql_dump:\n\n $sql_dump \n\n
\n\n------------------------------------\n\n
last_db_host:\t $host_info\n\n
\n\n------------------------------------\n\n
stack_tr:\n 
$stack_tr_to_mail\n\n
\n\n------------------------------------\n\n
dev_dump:\n
$dev_dump_to_mail\n\n
\n\n------------------------------------\n\n
err_log_key: $err_log_key ");
    }        


    // update process log
    $_CURRENT_STATUS = 'ERROR';
    $_FINAL_STATUS = 'ERROR';
    add2ProcessLog('ERROR - ' . $sql_err, true);

    echo 'ERROR: ' . $pg . ' - ' . $sql_err;    

    exit();

}  

//--------------------------------------------------------------

function getRequestVar($fieldname,$defaultvalue,$numeric_only=false,$prep_for_sql_insert=false)
{
    $rtn = '';

    if (isset($_POST[$fieldname]))
    {
        $rtn = $_POST[$fieldname];
    }
    else
    {
        if (isset($_GET[$fieldname]))
        {
            $rtn = $_GET[$fieldname];
        }
        else
        {
            $rtn = $defaultvalue;
        }
    }

    if ($numeric_only==true)
    {
        if (is_numeric($rtn)==false) {$rtn = $defaultvalue;}   
    }

    if ($prep_for_sql_insert==true)
    {
        $rtn = "'" . mysql_real_escape_string($rtn) . "'";   
    }

    return($rtn);

}

//--------------------------------------------------------------

function prepForSQL($rtn_val, $numeric_only=false)
{

    if ($numeric_only==true)
    {
        if (is_numeric($rtn_val)==false) {$rtn_val = -999;}   
    }
    else
    {
        // remove chrs we dont allow in the system
        $rtn_val = str_replace("'","",$rtn_val);
        $rtn_val = str_replace('"',"",$rtn_val);
        $rtn_val = str_replace("\\","",$rtn_val);
        $rtn_val = str_replace(';',"",$rtn_val);
        $rtn_val = "'" . mysql_real_escape_string($rtn_val) . "'";   
    }

    return($rtn_val);

}

//--------------------------------------------------------------

function dbGetReturnForSQL($sql, $field_for_key = '', $get_scalar_value = false, $default_value = 'x_no_data_x')
{
    /*  this function works for scalar, one row and multi row queries

        if $field_for_key = '' then use index

        if $get_scalar_value = true then return 1st row in array only (not nested in row array) or if only 1 value return value only (no array)  - first row only must be set to true 

        if no data is present $default_value will be returned, if blank, 'x_no_data_x' will be returned

        $error_id_on_fail, error id to pass to displayErrorMsg (defaults to GENERAL_ERROR)



    */

    global $db, $debug, $total_query_time_for_page;

    $loc_ptr = 0;
    $loc_key = '';
    $loc_arr = array();
    $loc_val = '';
    $use_srv = 'MASTER';
    $error_id_on_fail = '';

    if ( $debug ) {
        echo "
QUERY:

\n\n" . ereg_replace("\t", " ", $sql) . "\n
\n

\n";
    }


    if ( $debug ) { $start_time = microtime(true); }

    $result = mysql_query($sql,$db) OR displayErrorMsg($error_id_on_fail,'','',false,$sql,'richard.varno@gmail.com',false);     

    if ( $debug ) 
    { 
        $end_time = microtime(true); 
        $rows_rtn = mysql_num_rows($result);
        $qry_time = $end_time - $start_time;
        $total_query_time_for_page = $total_query_time_for_page + $qry_time;
        $host_info = mysql_get_host_info();  
        echo "$rows_rtn row(s) returned in $qry_time seconds from $host_info ($use_srv) 

total query time elapsed: $total_query_time_for_page seconds

\n";   

    }

    while ($rw = mysql_fetch_assoc($result)) 
    { 
        if ($get_scalar_value==true)
        {

            // show return val if debug = true
            if ($debug)
            {
                echo "Scalar Value
\n\n";

                foreach($rw as $fld_key => $fld_val)
                {
                    echo  "$fld_key$fld_val\n";       
                }
                echo "\n";
            }


            if (count($rw)==1)
            {
                $rw_key = array_keys($rw);
                $loc_val = $rw[$rw_key[0]];
                mysql_free_result($result);
                return($loc_val);      
            }
            else
            {
                mysql_free_result($result);
                return($rw);                
            }    

        }
        else
        {            
            if ($field_for_key=='') //use index pointer 
            {
                $loc_key = $loc_ptr;
            }
            else // use field
            {
                $loc_key = $rw[$field_for_key];   
            }

            $loc_arr[$loc_key] = $rw;
            $loc_ptr++;

        }
    }

    mysql_free_result($result);

    // if no results were found, return default
    if ($loc_ptr == 0) 
    {
        $loc_arr = $default_value; 
    }
    else
    {

        // show return val if debug = true (1st 50 rows only)
        if ($debug)
        {
            if($rows_rtn > 50)
            {
                    echo "Displaying rows 1 to $rows_rtn of $rows_rtn
\n";
                    $max_row_to_display = 49;
            }
            else
            {
                    echo "Displaying rows 1 to $rows_rtn of $rows_rtn
\n";            
                    $max_row_to_display = $rows_rtn-1; 
            }

            echo "\n";

            // show headers

            $loc_arr_keys = array_keys($loc_arr);

            $rw = $loc_arr[$loc_arr_keys[0]];

            echo "\n";

            foreach($rw as $fld_key => $fld_val)
            {
                echo  "$fld_key\n";       
            }

            echo "\n";

            // show data

            $cur_row = 0;

            foreach ($loc_arr as $ptr=>$row)
            {
                echo "\n";

                foreach($rw as $fld_key => $fld_val)
                {
                    echo  "$row[$fld_key]\n";       
                }

                echo "\n";

                $cur_row++;
                if ($cur_row > $max_row_to_display) { break; }               

            }

            echo "\n";
        }         


    }

    return($loc_arr);  



}

//--------------------------------------------------------------

function dbExecuteSQL($sql, $return_insert_id = false)
{
    /*  this function will execute and SQL Statement against the master database and return either the rows affected or the insert id if requested

        $error_id_on_fail, error id to pass to displayErrorMsg (defaults to GENERAL_ERROR)

    */

    global $db, $debug, $total_query_time_for_page;

    $loc_ptr = 0;
    $loc_insert_id = 0;
    $loc_rows_affected = 0;
    $error_id_on_fail = '';

    if ( $debug ) {
        echo "
QUERY:

\n\n" . ereg_replace("\t", " ", $sql) . "\n
\n

\n";
    }



    if ( $debug ) { $start_time = microtime(true); }

    $result = mysql_query($sql,$db) OR displayErrorMsg($error_id_on_fail,'','',false,$sql,'richard.varno@gmail.com',false);
    $loc_insert_id = mysql_insert_id($db);
    $loc_rows_affected = mysql_affected_rows($db);

    $use_srv = 'MASTER'; 


    if ( $debug ) 
    { 
        $end_time = microtime(true); 
        $qry_time = $end_time - $start_time;
        $total_query_time_for_page = $total_query_time_for_page + $qry_time;
        $host_info = mysql_get_host_info($db);  
        echo "$loc_rows_affected row(s) affected in $qry_time seconds from $host_info ($use_srv) 

total query time elapsed: $total_query_time_for_page seconds

\n";   

    }

    if ($return_insert_id==true) { return($loc_insert_id); } else { return($loc_rows_affected); }


}

//--------------------------------------------------------------