mysql master status_开发HTTP接口，获取MySQL show master status信息

#!/usr/bin/python

#This script is used to collect

master's status

#Written by

XiaoHaoTeng,2014/8/21

#coding:utf8

import os

from commands import

getstatusoutput

import sys

def if_conf_file_exist(port):

#if conf file exist

result =

os.path.exists('/etc/snmp/yyms_agent_db_scripts/db_%s.conf'%(str(port)))

if result == True:

pass

else:

#print "Conf file is not

exist"

return {"code":"2","message":"conf

file is not exist","info":""}

def get_master_status(port):

ports=os.popen("netstat -nutpl |

grep mysql | awk '{print $4}' | grep -o ':[0-9]\{1,\}' | awk -F ':'

'{print $2}'").readlines()

str_ports=''.join(ports)

if str(port) not in

str_ports:

return {"code":"3","message":"This

instance is not exist","info":""}

else:

pass

#Get mysql_user

getmysql_user_cmd="cat

/etc/snmp/yyms_agent_db_scripts/db_%s.conf | grep user | awk -F =

'{print $2}'"%(str(port))

#Get mysql_password

getmysql_password_cmd="cat

/etc/snmp/yyms_agent_db_scripts/db_%s.conf | grep password | awk -F

= '{print $2}'"%(str(port))

mysqluser_status,mysqluser_output=getstatusoutput(getmysql_user_cmd)

if mysqluser_status == 0:

mysql_user=mysqluser_output

else:

return

{"code":"4","message":"mysql_user is not exist","info":""}

mysqlpassword_status,mysqlpassword_output=getstatusoutput(getmysql_password_cmd)

if mysqlpassword_status == 0:

mysql_password=mysqlpassword_output

else:

return

{"code":"5","message":"mysql_password is not

exist","info":""}

#Get master_status cmd

host = '127.0.0.1'

master_status_cmd = '''mysql -u{0}

-p{1} -h{2} -P{3} -Bse "show master

status\G;"'''.format(mysql_user,mysql_password,host,port)

get_master_status,get_master_status_output =

getstatusoutput(master_status_cmd)

if get_master_status == 0:

master_status =

get_master_status_output

#print master_status

master_status_tmp =

master_status.split("\n")

del master_status_tmp[0]

master_status_list = []

for item in

master_status_tmp:

item_tmp = item.split(":")

variable_name = item_tmp[0]

variable_value = item_tmp[1]

item_tmp_dict =

{"variable_name":item_tmp[0],"variable_value":item_tmp[1]}

#print item_tmp_dict

master_status_list.append(item_tmp_dict)

print master_status_list

return

{"code":"0","message":"success","info":master_status_list}

else:

return {"code":"6","message":"can

not find master status info","info":""}

if __name__ == '__main__':

get_master_status(sys.argv[1])

if_conf_file_exist(sys.argv[1])

向该脚本传入port参数，就可以获得相应的MySQL实例show

master status信息。

在salt-master中心机器上，执行：salt

"minion_xxxx"

instance_info.get_master_status

6303

其中，instance_info是模块名，其实它就是一个py脚本，该py脚本有一个方法：get_master_status，向该方法传入port为6303。执行结果为：

minion_xxxx:

----------

code:

0

info:

----------

- variable_name:

File

- variable_value:

mysql-bin.003817

----------

- variable_name:

Position

- variable_value:

27539715

----------

- variable_name:

Binlog_Do_DB

- variable_value:

----------

- variable_name:

Binlog_Ignore_DB

- variable_value:

message:

success

但返回的结果并非是json数据，PHP无法解析。需要对该指令进行一次封装，使用salt client

API来调用这些模块。封装脚本在：/data/dbms/tools目录下，脚本为：get_master_status.py。来看看该封装脚本的内容：

#!/usr/bin/python

# -*- coding:utf8 -*-

import sys

import salt.client

#import salt.utils

def

parse_master_status(minion_id,port):

'''

parse show_master_status information

'''

local = salt.client.LocalClient()

information_output =

local.cmd(minion_id,'instance_info.get_master_status',[port],timeout=10)

print information_output

if len(information_output)==0:

information_output={minion_id:{"code":"-1","message":"fail to get

master status","info":""}}

if __name__ == '__main__':

if len(sys.argv) != 3:

print

"get_master_status.py and 2 arguments equal 3"

else:

parse_master_status(sys.argv[1],sys.argv[2])

我们来看看saltstack的一些命令对应的API：

salt --->

salt.client.LocalClient

salt-minion ---> salt.minion.Minion

salt-cp --->

salt.cli.cp.SaltCP

salt-key --->salt.key.KeyCLI

salt-run --->salt.runner.Runner

salt-ssh --->salt.client.ssh.SSH

由此可知，当我们执行了salt、salt-minion、salt-cp、salt-key、salt-run、salt-ssh等命令的时候，就知道salt的后台是怎么处理的了。

local =

salt.client.LocalClient()

information_output =

local.cmd(minion_id,'instance_info.get_master_status',[port],timeout=10)

这个可以理解为执行了：salt “minion_id” instance_info.get_master_status“port”指令

来看看执行封装脚本返回的数据：

/data1/Python-2.7.4/bin/python

/data/dbms/tools/get_master_status.py

minion_xxxx  6303

{'minion_xxxx':

{'info': [{'variable_value': ' mysql-bin.003817', 'variable_name':

'  File'},

{'variable_value': ' 32717806', 'variable_name': '

Position'}, {'variable_value': ' ',

'variable_name': '  Binlog_Do_DB'}, {'variable_value': ' ',

'variable_name': 'Binlog_Ignore_DB'}], 'message': 'success',

'code': '0'}}

由于我们的数据库管理系统需要用户登陆验证，故如果业务运维的同学想要访问该接口，是需要登陆验证的。这样就显得复杂了。为此，我们开放了一个controller，访问该controller的任何Action，都不需要登陆验证。该controller为ServiceController.class.php。所以只需要构建一个Action，该Action如下：

public function

udb_master_statusAction() {

$ip

= $this->get('ip');

$port =

$this->get('port');

if (($ip

== NULL)) {

echo "ip

is empty";

exit();

}

if (($port

== NULL)) {

echo "port

is empty";

exit();

}

$db_model  = $this->model('db_instance');

$ip_port_sql

= "select ip,port from db_instance where

business_model='亚太udb登陆一级库' or business_model='亦庄udb登陆一级库'";

$ip_port_data =

mysql_query($ip_port_sql);

while($row =

mysql_fetch_array($ip_port_data,MYSQL_NUM)) {

//echo

"ip:".$row[0]."

";

$ips_arr[]

= $row[0];

//echo

"port:".$row[1]."

";

$ports_arr[] = $row[1];

}

//print_r($ips_arr);

//print_r($ports_arr);

if(in_array($ip,$ips_arr,true)

&& in_array($port,$ports_arr,true)){

//echo

"success";

$server_id_sql  = "select server_id from

db_instance where ip='$ip' and port='$port'";

$server_id_data =

$db_model->execute($server_id_sql);

$server_id_arr  = $server_id_data[0];

//print_r($server_id_arr);

$server_id

=

$server_id_arr['server_id'];

//echo

$server_id;

$minion_id

= "minion_".$server_id;

//echo

$minion_id;

$salt_cmd

= "sudo /data1/Python-2.7.4/bin/python

/data/dbms/tools/get_master_status.py

\"".$minion_id."\"

\"".$port."\"";

try

{

$json =

shell_exec($salt_cmd);

//json_encode返回字符串，json_decode返回对象，对象才有key/value

$json =

str_replace("'",'"',$json);

//由于返回的json数据key是单引号的，故会导致json_decode()失败。需要替换为双引号。

//echo

$json;

$master_status_json_tmp = json_decode($json);

//json_decode解析出来的是对象，不能用obj['key']的方式访问(数组才可以这样访问)，要用->key的方式访问。

$master_status_json  =

json_encode(($master_status_json_tmp->$minion_id));//json_encode解析出来的是字符串，用echo来显示字符串

//echo

$master_status_json;

$master_status_json_tmp1 =

json_decode(($master_status_json));

$master_status_json_tmp2 =

json_encode(($master_status_json_tmp1->info));

$entity=json_decode($master_status_json_tmp2);

//

var_dump($entity[1]);

//die;

$arr_master_status = array (

"code"

=> 0,

"object" => array(

"File" =>

trim($entity[0]->variable_value),

"Position" =>

trim($entity[1]->variable_value)

),

"message"=>success

);

echo

json_encode($arr_master_status);

}catch (Exception $e) {

echo 'Caught exception: ',

$e->getMessage(), "\n";

return false;

}

}

故该HTTP接口为：

http://dbms1.xxxxxx.com:801/service/udb_master_status?ip=***.***.***.***&port=6303

返回结果为：

{

code: 0,

object: {

File: "mysql-bin.002538",

Position: "30228150"

},

message: "success"

}