Наблюдаване на Replication Сървъри

от MySQL UG Bulgaria

Ако имате MySQL Replication Cluster (не MySQL Cluster), този скрипт ще ви свърши чудесна работа. Той се грижи да прави мониторинг на това кой сървър в момента какво прави.

Ето и примерен резултат от него:

-------------------------------------------------------------------------------------
| MySQL Replication Watcher - by phpfreak - www.thewebfreaks.com                    |
-------------------------------------------------------------------------------------
| Type: Server   | Status  | Log File         | RMLP      | EMLP      | OFM  | SBM  |
-------------------------------------------------------------------------------------
| M: localhost   | -       | bdb01-bin.000002 | 8193941   | -         | -    | -    |
| S: 10.0.10.22  | Active  | bdb01-bin.000002 | 8193941   | 8193941   | 0    | 0    |
| S: 10.0.10.23  | Active  | bdb01-bin.000002 | 8193941   | 8193941   | 0    | 0    |
-------------------------------------------------------------------------------------
| RMLP = Read Master Log Position (Slaves) | Master Log Position (Masters)          |
| EMLP = Exec Master Log Posotion (Slaves only)                                     |
| SBM = Seconds Behind Master                                                       |
| OFM = Offset From Master - How far slave is behind in processing from master      |
-------------------------------------------------------------------------------------
| Timestamp: Mon, 19 Jun 2006 23:54:21 -0400                                        |
-------------------------------------------------------------------------------------

Ето и кода

#!/usr/local/bin/php
<?
/*
Developed by Eric 'phpfreak' Rosebrock
http://www.thewebfreaks.com
http://www.serverpowered.com
(C) Copyright 2006 - The Web Freaks, INC
No warranty, no promises.
Not for commercial distribution without author consent
*/

$master = array();
$master['host'] = 'localhost';
$master['user'] = 'root';
$master['password'] = 'Master_PASSWORD_HERE';

$i = 0;
$slaves = array();

// add slaves here
$slaves[$i]['host'] = '10.0.10.22';
$slaves[$i]['user'] = 'root';
$slaves[$i]['password'] = 'SLAVE_PASSWORD_HERE';
$i++;

$slaves[$i]['host'] = '10.0.10.23';
$slaves[$i]['user'] = 'root';
$slaves[$i]['password'] = 'SLAVE2_PASSWORD_HERE';
$i++;


function spaces($str, $num = 25)
{
        $ret = $str;
        while(strlen($ret) < $num) { $ret .= " "; }
        return $ret;
}

function line($num = 85)
{
        $ret = '';
        while(strlen($ret) < $num){ $ret .= '-'; }
        return $ret;
}

// connect to the master and grab its current status
$cnx = @mysql_connect($master['host'], $master['user'], $master['password']);
$ms = @mysql_fetch_object(mysql_query("SHOW MASTER STATUS"));

foreach($ms AS $key => $var)
{
        $master[strtolower($key)] = $var;
}

$col = array();
$col[0] = 15;
$col[1] = 8;
$col[2] = 17;
$col[3] = 10;
$col[4] = 10;
$col[5] = 5;
$col[6] = 5;
$default = '82';

$output = line()."\n";
$output .= '| '.spaces('MySQL Replication Watcher - by phpfreak - www.thewebfreaks.com', $default).'|'."\n";
$output .= line()."\n";


$output .= '| '.spaces('Type: Server', $col[0]).'| ';   // Server column
$output .= spaces('Status', $col[1]).'| ';              // Status column
$output .= spaces('Log File', $col[2]).'| ';            // Current Log file column
$output .= spaces('RMLP',$col[3]).'| ';                 // Log file Position column
$output .= spaces('EMLP',$col[4]).'| ';                 // Log file Position column
$output .= spaces('OFM',$col[5]).'| ';                  // Log file Position column
$output .= spaces('SBM',$col[6])."|\n";                 // Seconds behind master column

$output .= line()."\n";

$output .= '| '.spaces('M: '.$master['host'], $col[0]).'| ';
$output .= spaces('-', $col[1]).'| ';
$output .= spaces($master['file'],$col[2]).'| ';
$output .= spaces($master['position'],$col[4]).'| ';
$output .= spaces('-',$col[3]).'| ';
$output .= spaces('-', $col[5]).'| ';
$output .= spaces('-', $col[6])."|\n";

for($x = 0; $x < count($slaves); $x++)
{
        // connect to each slave and grab its status
        $cnx = @mysql_connect($slaves[$x]['host'], $slaves[$x]['user'], $slaves[$x]['password']);
        $s = @mysql_fetch_object(mysql_query("SHOW SLAVE STATUS"));
        foreach($s AS $key => $var)
        {
                $slaves[$x][strtolower($key)] = $var;
                if($key == 'Slave_IO_Running')
                {
                        $slaves[$x]['slave_io_running'] = ($var == 'Yes') ? 'Active' : 'Inactive';
                }

        }
        $ofm = ($master['position'] - $slaves[$x]['exec_master_log_pos']);
        $output .= '| '.spaces('S: '.$slaves[$x]['host'], $col[0]).'| ';
        $output .= spaces($slaves[$x]['slave_io_running'], $col[1]).'| ';       // Slave HostName/IP
        $output .= spaces($slaves[$x]['master_log_file'], $col[2]).'| ';        // Master log file
        $output .= spaces($slaves[$x]['read_master_log_pos'],$col[3]).'| ';     // master log exec position
        $output .= spaces($slaves[$x]['exec_master_log_pos'],$col[4]).'| ';     // master log exec position
        $output .= spaces($ofm,$col[5]).'| ';                                   // master log exec position
        $output .= spaces($slaves[$x]['seconds_behind_master'],$col[6]);        // Seconds behind master
        $output .= "|\n";
}
$output .= line()."\n";

$output .= '| '.spaces('RMLP = Read Master Log Position (Slaves) | Master Log Position (Masters)',$default).'|'."\n";
$output .= '| '.spaces('EMLP = Exec Master Log Posotion (Slaves only)', $default).'|'."\n";
$output .= '| '.spaces('SBM = Seconds Behind Master',$default).'|'."\n";
$output .= '| '.spaces('OFM = Offset From Master - How far slave is behind in processing from master', $default).'|'."\n";

$output .= line()."\n";
$output .= '| '.spaces('Timestamp: '.date('r'), $default).'|'."\n";
$output .= line()."\n";

echo $output;
?>

Повече за репликацията, можете да прочетете в раздела за Документация.