PHP's method of detecting MySQL synchronization state under Linux _php tips

This article illustrates the PHP method of detecting MySQL synchronization state under Linux. Share to everyone for your reference. The specific analysis is as follows:

Here two examples are introduced to introduce MySQL synchronization state detection method. The code is as follows:

Copy Code code as follows:

#!/bin/sh

#check Mysql_slave Status
#crontab Time 00:10
Mysql_user= "Root"
Mysql_pwd= "123456"
Mysql_slave_log= "/tmp/check_mysql_slave.log"
Email= "1351010****@139.com"

Mysql_port= ' Netstat-na|grep "LISTEN" |grep "3306" |awk-f[: ""]+ ' {print $} '
mysql_ip= ' ifconfig eth0|grep "inet addr" | awk-f[: ""]+ ' {print $} '
mysql_slave_status=$ (/usr/local/webserver/mysql/bin/mysql-u root-psylc23hua-s/tmp/mysql.sock-e

"Show Slave Statusg" | Grep-i "Running")
Io_env= ' echo $MYSQL _slave_status | grep IO | awk ' {print $} '
Sql_env= ' echo $MYSQL _slave_status | grep SQL | awk ' {print $} '
now=$ (date-d today + '%y-%m-%d%h:%m:%s ')

If ["$MYSQL _port" = "3306"];then
echo "MySQL is running!"
Else
Mail-s "Warn!server: $MYSQL _ip MYSQL is down" "$EMAIL"
Fi

If ["$IO _env" = "yes"-a "$SQL _env" = "yes"];then
echo "Slave is running!"
Else
echo "[$NOW] Slave is not running!" >> "$MYSQL _slave_log"
Cat "$MYSQL _slave_log" | Mail-s "warn! ${mysql_ip}_replicate_error "" $EMAIL "
Fi

Exit 0

PHP instance code, code as follows:
check_rep.php:

Copy Code code as follows:

if (Emptyempty ($_request["key")) Die (':) missing key ');
if ($_request["key"]!= ' Xupeng ') die (':) error key ');

Include ("mysql_instance.php");
Include ("check_status_api.php");

Define ("USERNAME", "User name");
Define ("PASSWORD", "password");
Define ("DebugMode", false);

$instances = Get_instances ();

if ($instances) {
Echo <<

<!--30-minute automatic refresh-->

End;
echo "
n ";
if (! DebugMode) {
echo "
n ";
}else{
echo "
n ";
}
foreach ($instances as $host) {
$res = Check_mysql_replication_status ($host, USERNAME, PASSWORD);
if (! DebugMode) {
Switch ($res ["result"]) {
Case-4:
$memo = "Unknown exception";
Break
Case-3:
$memo = "Query Failed";
Break
Case-2:
$memo = "Unable to connect port";
Break
Case-1:
$memo = "Status Unknown";
Break
Case 0:
$memo = "OK";
Break
Case 1:
$memo = "Sync Failed";
if ($res ["slave_io_running"] <> "Yes") {
$memo. = $res ["Last_io_error"].  "(" . $res

["Last_io_errno"]. ")";
}
if ($res ["slave_sql_running"] <> "Yes") {
$memo. = $res ["Last_sql_error"].  "(" . $res

["Last_sql_errno"]. ")";
}
Break
Case 2:
$memo = "Database is not set to sync";
Break
}
echo "

n ";
}else{
echo "

n ";
}
}
echo "
<table border= "" >
<tbody>
<tr>
<td>instance</td>
<td>result</td>
<td>Slave_IO_Running</td>
<td>Slave_SQL_Running</td>
<td>Master_Host</td>
<td>Master_Port</td>
<td>Replicate_Do_DB</td>
<td>memo</td>
</tr>
<tr>
<td>instance</td>
<td>result</td>
<td>Slave_IO_Running</td>
<td>Slave_SQL_Running</td>
<td>Master_Host</td>
<td>Master_Port</td>
<td>Replicate_Do_DB</td>
<td>Slave_IO_State</td>
<td>Last_IO_Errno</td>
<td>Last_IO_Error</td>
<td>Last_SQL_Errno</td>
<td>Last_SQL_Error</td>
</tr>
<tr>
<td>{$host}</td>
<td>{$res [' Result ']}</td>
<td>{$res [' slave_io_running ']}</td>
<td>{$res [' slave_sql_running ']}</td>
<td>{$res [' Master_host ']}</td>
<td>{$res [' Master_port ']}</td>
<td>{$res [' replicate_do_db ']}</td>
<td>{$memo}</td>
</tr>
<tr>
<td>{$host}</td>
<td>{$res [' Result ']}</td>
<td>{$res [' slave_io_running ']}</td>
<td>{$res [' slave_sql_running ']}</td>
<td>{$res [' Master_host ']}</td>
<td>{$res [' Master_port ']}</td>
<td>{$res [' replicate_do_db ']}</td>
<td>{$res [' Slave_io_state ']}</td>
<td>{$res [' Last_io_errno ']}</td>
<td>{$res [' Last_io_error ']}</td>
<td>{$res [' Last_sql_errno ']}</td>
<td>{$res [' Last_sql_error ']}</td>
</tr>
</tbody>
</table>
n ";
Echo <<

End;
}else{
Die ("No MySQL instances defined.");
}

check_status_api.php:

Copy Code code as follows:

/*
* Check the synchronization status of the MySQL server
*/
function Check_mysql_replication_status ($host, $username, $password)
{
Default state Unknown
$r = Array (
"Result" =>-1
);
try{
$DBH = @mysql_connect ($host, $username, $password);
if (! $dbh) {
Unable to connect
$r ["result"] =-2;
return ($R);
}
$query = "show SLAVE STATUS";
$res = @mysql_query ($query, $DBH);
$err = @mysql_error ();
if ($err) {
Unable to connect
$r ["result"] =-3;
return ($R);
}
$row = Mysql_fetch_array ($res);
$r = $row;
if ($r ["slave_io_running"] = "yes") && ($r ["slave_sql_running"] = = "Yes")
{
$r ["result"] = 0;
}else{
if (!emptyempty ($row)) {
$r ["result"] = 1;
}else{
$r ["result"] = 2;
}
}
}catch (Exception $e) {
$r ["result"] =-4;
}
return ($R);
}

mysql_instance.php:

Copy Code code as follows:

GRANT REPLICATION CLIENT on *.* to ' username ' @ ' monitor host IP ' identified by ' password '; $mysql _instances =

Array ();
$mysql _instances[] = "Remote IP: Port";

function Get_instances ()
{
Global $mysql _instances;
return $mysql _instances;
}

Place the above three PHP files in a virtual directory and then access them through the URL.

Access mode: Http://ip/check_repl.php?key=xupeng

I hope this article will help you with your PHP program design.