Home Page
Home Page
Posts > MySQL replication ring status reporting script
Home
Projects
Archive
Forums
Contact
Misc
Site Map
Search:
MySQL replication ring status reporting script

I just threw together a quick script to report status on a MySQL replication ring. While replication rings have been the only real multi-master MySQL solution for replication (with the ability for nodes to go down without majorly breaking things) until recently, I have read that MariaDB (still not MySQL) now allows a slave to have multiple masters, meaning many replication topologies are now possible (star, mesh, etc). This script could easily be adapted for those circumstances too.

This script will report all the variables from “SHOW MASTER STATUS” and “SHOW SLAVE STATUS” from all servers in your replication ring, in a unified table. It also includes a “Pretty Status” row that lets you quickly see how things look. The possibilities for this row are:

  • Bad state: ...
    This shows if the Slave_IO_State is not “Waiting for master to send event”
  • Cannot determine master’s real position
    This shows if the Position variable on the master could not be read
  • On old master file
    This shows if the slave’s “Master_Log_File” variable does not equal the master’s “File” variable
  • Bytes behind: xxx
    This shows if none of the above errors occurred. It subtracts the master’s “Position” from the slave’s “Read_Master_Log_Pos”. This should generally be at or around 0. A negative value essentially means 0 (this should only happen between the last and first server).

The “Seconds_Behind_Master” variable can also be useful for determining the replication ring’s current replication status.

The code is below the example. The entire source file can also be found here. The 3 variables that need to be configured are at the top of the file. It assumes that all servers are accessible via the single given username and password.


Example:
Master
Server Name
EXAMPLE1.MYDOMAIN.COM
EXAMPLE2
Filemysql-bin.000003mysql-bin.000011
Position252497463215834
Binlog_Do_DBexample_data,devexample_dataexample_data,devexample_data
Binlog_Ignore_DB
Slave
Pretty Status
Bytes behind: 0
Bytes behind: 0
Slave_IO_StateWaiting for master to send eventWaiting for master to send event
Master_HostEXAMPLE2EXAMPLE1.MYDOMAIN.COM
Master_Userexample_slaveexample_slave
Master_Port33063306
Connect_Retry6060
Master_Log_Filemysql-bin.000011mysql-bin.000003
Read_Master_Log_Pos321583425249746
Relay_Log_Filewww-relay-bin.070901www-relay-bin.071683
Relay_Log_Pos252252
Relay_Master_Log_Filemysql-bin.000011mysql-bin.000003
Slave_IO_RunningYesYes
Slave_SQL_RunningYesYes
Replicate_Do_DBexample_data,devexample_dataexample_data,devexample_data
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno00
Last_Error
Skip_Counter00
Exec_Master_Log_Pos321583425249746
Relay_Log_Space552552
Until_ConditionNoneNone
Until_Log_File
Until_Log_Pos00
Master_SSL_AllowedNoNo
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master00
Master_SSL_Verify_Server_CertNoNo
Last_IO_Errno00
Last_IO_Error
Last_SQL_Errno00
Last_SQL_Error
Replicate_Ignore_Server_Ids
Not given
Master_Server_Id2
Not given


Code:

<?
//Configurations
$Servers=Array('SERVER1.YOURDOMAIN.COM', 'SERVER2.YOURDOMAIN.COM'); //List of host names to access mysql servers on. This must be in the order of the replication ring.
$SlaveUserName='SLAVE_RING_USERNAME'; //This assumes all servers are accessible via this username with the same password
$SlavePassword='SLAVE_RING_PASSWORD';

//Get the info for each server
$ServersInfo=Array(); //SERVER_NAME=>Array('Master'=>Array(Col1=>Val1, ...), 'Slave'=>Array(Col1=>Val1, ...)
$ColsNames=Array('Master'=>Array('Server Name'=>0), 'Slave'=>Array('Pretty Status'=>0)); //The column names for the 2 (master and slave) queries. Custom column names are also added here
$CustomFieldNames=array_merge($ColsNames['Master'], $ColsNames['Slave']); //Store the custom column names so they are not HTML escaped later
foreach($Servers as $ServerName)
{
    //Connect to the server
    $Link=@new mysqli($ServerName, $SlaveUserName, $SlavePassword);
    if($Link->connect_error)
        die(EHTML("Connection error to $ServerName server: $Link->connect_error"));

    //Get the replication status info from the server
    $MyServerInfo=$ServersInfo[$ServerName]=Array(
        'Master'=>$Link->Query('SHOW MASTER STATUS')->fetch_array(MYSQLI_ASSOC),
        'Slave'=>$Link->Query('SHOW SLAVE STATUS')->fetch_array(MYSQLI_ASSOC)
    );
    mysqli_close($Link); //Close the connection

    //Gather the column names
    foreach($ColsNames as $ColType => &$ColNames)
        foreach($MyServerInfo[$ColType] as $ColName => $Dummy)
            $ColNames[$ColName]=0;
}
unset($ColNames);

//Gather the pretty statuses
foreach($Servers as $Index => $ServerName)
{
    //Determine the pretty status
    $SlaveInfo=$ServersInfo[$ServerName]['Slave'];
    $MasterInfo=$ServersInfo[$Servers[($Index+1)%count($Servers)]]['Master'];
    if($SlaveInfo['Slave_IO_State']!='Waiting for master to send event')
        $PrettyStatus='Bad state: '.EHTML($SlaveInfo['Slave_IO_State']);
    else if(!isset($MasterInfo['Position']))
        $PrettyStatus='Cannot determine master’s real position';
    else if($SlaveInfo['Master_Log_File']!=$MasterInfo['File'])
        $PrettyStatus='On old master file';
    else
        $PrettyStatus='Bytes behind: '.($MasterInfo['Position']-$SlaveInfo['Read_Master_Log_Pos']);

    //Add the server name and pretty status to the output columns
    $ServersInfo[$ServerName]['Master']['Server Name']='<div class=ServerName>'.EHTML($ServerName).'</div>';
    $ServersInfo[$ServerName]['Slave']['Pretty Status']='<div class=PrettyStatus>'.EHTML($PrettyStatus).'</div>';
}

//Output the document
function EHTML($S) { return htmlspecialchars($S, ENT_QUOTES, 'UTF-8'); } //Escape HTML
?>
<!DOCTYPE html>
<html>
<head>
    <title>Replication Status</title>
    <meta charset="UTF-8">
    <style>
        table { border-collapse:collapse; }
        table tr>* { border:1px solid black; padding:3px; }
        th { text-align:left; font-weight:bold; }
        .ReplicationDirectionType { font-weight:bold; text-align:center; color:blue; }
        .ServerName { font-weight:bold; text-align:center; color:red; }
        .PrettyStatus { font-weight:bold; color:red; }
        .NotGiven { font-weight:bold; }
    </style>
</head>
<body><table>
<?
//Output the final table
foreach($ColsNames as $Type => $ColNames) //Process by direction type (Master/Slave) then columns
{
    print '<tr><td colspan='.(count($Servers)+1).' class=ReplicationDirectionType>'.$Type.'</td></tr>'; //Replication direction (Master/Server) type title column
    foreach($ColNames as $ColName => $Dummy) //Process each column name individually
    {
        print '<tr><th>'.EHTML($ColName).'</th>'; //Column name
        $IsHTMLColumn=isset($CustomFieldNames[$ColName]); //Do not escape HTML on custom fields
        foreach($ServersInfo as $ServerInfo) //Output the column for each server
            if($IsHTMLColumn) //Do not escape HTML on custom fields
                print '<td>'.$ServerInfo[$Type][$ColName].'</td>';
            else //If not a custom field, output the escaped HTML of the value. If the column does not exist for this server (different mysql versions), output "Not given"
                print '<td>'.(isset($ServerInfo[$Type][$ColName]) ? EHTML($ServerInfo[$Type][$ColName]) : '<div class=NotGiven>Not given</div>').'</td>';
        print '</tr>';
    }
}
?>
</table></body>
</html>

One final note. When having this script run, you might need to make sure none of the listed server IPs evaluates to localhost (127.x.x.x), as MySQL may instead then use the local socket pipe, which may not work with users who only have REPLICATION permissions and a wildcard host.


Comments
To add comments, please go to the forum page for this post (guest comments are allowed for the Projects, Posts, and Updates Forums).
Comments are owned by the user who posted them. We accept no responsibility for the contents of these comments.

No comments for this Post