PHP method to detect mysql synchronization status under Linux, linuxmysql

PHP method to detect mysql synchronization status under Linux, linuxmysql_PHP tutorial

WBOY

Release： 2016-07-13 10:09:38

Original

850 people have browsed it

How to detect mysql synchronization status in php under linux, linuxmysql

The example in this article describes how PHP detects the synchronization status of MySQL under Linux. Share it with everyone for your reference. The specific analysis is as follows:

Here are two examples to introduce the implementation method of mysql synchronization status detection. The code is as follows:

Copy the code The code is 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 $5}'`
MYSQL_IP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'`
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 $2}'`
SQL_ENV=`echo $MYSQL_SLAVE_STATUS | grep SQL | awk '{print $2}'`
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 example code, the code is as follows:
check_rep.php：

Copy code The code is 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", "username");
define("PASSWORD", "Password");
define("DEBUGMODE", false);

$instances = get_instances();

if($instances){
echo <<

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 to port";
Break;
case -1:
$memo = "Status unknown";
Break;
case 0:
$memo = "OK";
Break;
case 1:
$memo = "Synchronization 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 synchronized";
Break;
}
echo "

n";
}else{
echo "

n";
}
}
echo "

instance	result	Slave_IO_Running	Slave_SQL_Running	Master_Host	Master_Port	Replicate_Do_DB	memo
instance	result	Slave_IO_Running	Slave_SQL_Running	Master_Host	Master_Port	Replicate_Do_DB	Slave_IO_State	Last_IO_Errno	Last_IO_Error	Last_SQL_Errno	Last_SQL_Error
{$host}	{$res['result']}	{$res['Slave_IO_Running']}	{$res['Slave_SQL_Running']}	{$res['Master_Host']}	{$res['Master_Port']}	{$res['Replicate_Do_DB']}	{$memo}
{$host}	{$res['result']}	{$res['Slave_IO_Running']}	{$res['Slave_SQL_Running']}	{$res['Master_Host']}	{$res['Master_Port']}	{$res['Replicate_Do_DB']}	{$res['Slave_IO_State']}	{$res['Last_IO_Errno']}	{$res['Last_IO_Error']}	{$res['Last_SQL_Errno']}	{$res['Last_SQL_Error']}

n";
echo <<

END;
}else{
die("no mysql instances defined.");
}

check_status_api.php：

Copy code The code is as follows:

/*
* Check the synchronization status of mysql server
*/
function check_mysql_replication_status($host, $username, $password)
{
//The default status is 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 The code is as follows:

//GRANT REPLICATION CLIENT ON *.* TO 'username'@'monitoring 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 the virtual directory and access them through URL.

Access method: http://ip/check_repl.php?key=xupeng

I hope this article will be helpful to everyone’s PHP programming design.