Many environments I manage are large and span over multiple data centers across the world. We replicate data across and sometimes need to have a visual aid to help look at the replication flow. This simple PhP script allows me to see the replication flow starting from master to all slaves connected. You can start from any server but the results will be master first.

<?

#################################
######  Mysql Replication Mapper #########
######     Lee Thompson ##############
######      Version 1.0   ###############
#################################

$user = "<username>";
$pass = "<password>";

$masterserver = $_REQUEST['master_server'];

$masterport = $_REQUEST['master_port'];
if ($masterport == '')
$masterport = '3306';

echo "Please enter a master server";
echo "<form method='post' action='$php_self'>";
echo "Server: <input type ='text' name='master_server' value='" . $masterserver . "'><br />";
echo "Port: <input type ='text' name='master_port' value='3306'><br />";
echo "<input type ='submit'><hr />";

$serverlist = array();

// fill data
$serverlist = query_slave_server($masterserver.":".$masterport);

// print data
print "<b>Master Server: </b>".$serverlist." on Port: ".$masterport . '<br />';

$array = query_server($serverlist);
print_server($array, 0);

function query_slave_server($server)
{
  global $user, $pass, $masterport, $masterserver;

  $sql ="show processlist";
        $foundmaster = FALSE;
        while ( $foundmaster === FALSE )
        {
    $con = mysql_connect($server.":".$masterport,$user,$pass) or die($server. mysql_error());
                $result = mysql_query($sql,$con);
                while ($row = mysql_fetch_array($result))
                {
                        $slaveuser = $row['User'];
                        if ($slaveuser=='system user')
                        {
                                $sql2 = "show slave status";
                                $result = mysql_query($sql2,$con);
                                while ($row2 = mysql_fetch_array($result))
                                {
                                        $smasterserver = $row2['Master_Host'];
                                        $smasterport = $row2['Master_Port'];
                                        $server = $row2['Master_Host'];
                                        $masterport = $row2['Master_Port'];
                                }
                                break;
                        }
                }
                if ( $slaveuser != 'system user' )
                {
                        $foundmaster = TRUE;
                }
        }
        return $server;
}

function query_server($mserver)
{
  global $user, $pass, $masterport;

  $sql ="show processlist";

  $con = @mysql_connect($mserver.":".$masterport,$user,$pass) or die(mysql_error());
  $result = @mysql_query($sql,$con);
  while ($row = @mysql_fetch_array($result))
  {
    $slave = $row['Host'];
    $slavehost = ereg_replace(":.*", "", $slave);
    $slavecmd = $row['Command'];
    if ($slavecmd=='Binlog Dump')
    {
      $current[$slavehost] = query_server($slavehost.":".$masterport);
    }
  }
  return $current;
}

function print_server($current, $depth)
{
  global $user, $pass;

  foreach ($current as $slave => $ary)
  {
    $conn2 = mysql_connect($slave,$user,$pass);
    $sql2 = "show slave status";
    $result2 = mysql_query($sql2, $conn2);
    while ($row2 = mysql_fetch_array($result2))
    {
      $slavecmd = $row2['Slave_IO_Running'];
      $slavecmd1 = $row2['Slave_SQL_Running'];
    }

      if ($slavecmd=='Yes' && $slavecmd1=='Yes')
      {
        $slaverunning = "Yes";
      }
      else
      {
        $slaverunning = "<font color ='red'>No</font>";
      }
    $local = "localhost";
    $lconn = mysql_connect($local,$user,$pass);
    $lsql = "select server_name from myman.server where primary_ip = '".ereg_replace(":.*", "", $slave)."'";
    $lresult = mysql_query($lsql, $lconn);
    while($lrow = mysql_fetch_array($lresult))
    {
      if ($depth > 0) print str_repeat('&nbsp;',10 * ($depth - 1));
      echo "&#160---&gt;&#160; ";
      echo $lrow[0]. " (" . $slave . ") Slave Running:" . $slaverunning . "(depth: $depth)<br />\n";
      if ($ary) print_server($ary, $depth + 1);
    }
  }
}
?>

Example Output:

Master Server: 172.1.1.102 on Port: 3306
 ---> testdb02 (172.1.1.103) Slave Running:Yes(depth: 0)
 ---> testdb03 (172.1.1.104) Slave Running:Yes(depth: 0)
 ---> testdb04 (172.1.1.105) Slave Running:Yes(depth: 0)
     ---> testdb04-02 (172.1.1.106) Slave Running:Yes(depth: 1)
     ---> devdb01 (172.1.1.107) Slave Running:Yes(depth: 1)
       ---> dc2testdb (172.1.1.108) Slave Running:Yes(depth: 2)
	  ---> dctestdb-02 (172.2.2.101) Slave Running:Yes(depth: 3)
	  ---> dctestdb-03 (172.2.2.102) Slave Running:Yes(depth: 3)
       ---> dc3testdb (172.1.1.108) Slave Running:Yes(depth: 2)
	 ---> dctestdb-02 (172.2.2.103) Slave Running:Yes(depth: 3)
---> devdb02 (172.3.3.101) Slave Running:Yes(depth: 0)
---> devdb03 (172.3.4.101) Slave Running:Yes(depth: 0)