Detecting MySQL Sync State Implementation code (PHP/LINUX)

Source: Internet
Author: User
The code is as follows Copy Code

#!/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

The code is as follows Copy Code

check_rep.php

<!--? php<br/-->if (Empty ($_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

<!--? php<br/-->/*
* 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 (!empty ($row)) {
$r ["result"] = 1;
}else{
$r ["result"] = 2;
}
}
}catch (Exception $e) {
$r ["result"] =-4;
}
return ($R);
}
mysql_instance.php

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

Array ();
$mysql _instances[] = "Remote IP: Port"; $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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.