Mysql服务器/数据库的维护步骤和性能步骤是什么？

Question

像索引维护一样，我们有索引重组/重建、更新统计信息、收缩数据库日志文件、MS SQL 服务器中的数据库备份/恢复，Mysql 服务器/数据库的维护步骤和性能步骤是什么？

Answer 1

ANALYZE TABLE 将扫描键并存储数据将有助于将来的加入。我不知道它实际上有多大用处/需要多少，但我敢打赌这肯定取决于您典型的数据库工作负载和表结构。

OPTIMIZE TABLE 将对表进行碎片整理并回收未使用的空间但是，正如您在文档中所读到的那样，这通常根本没有必要。

您可以通过 SQL 或使用 mysqlcheck 工具调用它们。

虽然基本上没有必要，但我通常会在低活动时间（这意味着在我的情况下是周末晚上）安排一个 mysqlcheck -Aao。

Answer 2

//--------------------------------------------------------------
//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 = '[email protected]';   // 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. [email protected])
    //  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,'[email protected]',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,'[email protected]',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); }


}

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