如何检测MYSQL仍正常提供运行

首先，楼主，mysql的连接被占光，出现这种现象，极有可能是你的程序有问题，或者你的业务设计的就有问题，怎么可能会占光呢？莫非是一个业务重新建立一个连接，而不是建立一个连接池，业务通过连接池去操作mysql？

检查mysql僵死有多种办法，snmp可以通过walk或者get来获得对应的状态，hrSWRunStatus后面的就是进程号，hrSWRunPath可以获得到你的mysql执行的命令，同时也有对应的进程号

snmpwalk -v 1 -c public localhost hrSWRunPath

然后可以获得如下内容

HOST-RESOURCES-MIB::hrSWRunPath.13364 = STRING: "redis-server"

HOST-RESOURCES-MIB::hrSWRunPath.14061 = STRING: "php-fpm: master process (/usr/local/php/etc/php-fpm.conf)"

HOST-RESOURCES-MIB::hrSWRunPath.15015 = STRING: "/usr/sbin/httpd"

HOST-RESOURCES-MIB::hrSWRunPath.15819 = STRING: "sleep"

HOST-RESOURCES-MIB::hrSWRunPath.20330 = STRING: "php-fpm: pool www"

HOST-RESOURCES-MIB::hrSWRunPath.21724 = STRING: "php-fpm: pool www"

然后比如我想检查redis-server的运行状态，那么就这么执行

[StevenLiu@liudeMacBook-Pro ~]$ snmpwalk -v 1 -c public localhost hrSWRunStatus.13364

HOST-RESOURCES-MIB::hrSWRunStatus.13364 = INTEGER: runnable(2)

[StevenLiu@liudeMacBook-Pro ~]$

上面是snmp的检测方式，runnable可以判断是否为僵尸进程

如果觉得那么做不方便，还是先检查为啥连接都被占空，这样才是解决根本

然后通过mysql连接接口去连接mysql检测就可以了，具体C代码实现

片段

#include

#include "./get_password.c"

MYSQL *mysql;

MYSQL_RES *result;

MYSQL_ROW sqlrow;

extern char *input_password(char pd[]);

void display_row(MYSQL *ptr);

char *get_name( char name[])

{

int i=0;

char ch;

while((ch=getchar())!='\n')

{

name[i++]=ch;

}

name[i]='\0';

return name;

}

int main()

{

int res;

char name[20],

pd[20];

mysql = mysql_init(NULL);

if(mysql)

printf("mysql init succeed!\n");

else

{

printf("mysql init failed!\n");

return -1;

}

printf("Input you name:\n");

get_name(name);

printf("Input Password:\n");

input_password(pd);

printf("\n");

mysql=mysql_real_connect(mysql, "localhost",name, pd, "test",0,NULL,0);

if(mysql)

printf("mysql connect succeed!\n");

else

{

printf("mysql connect failed!\n");

return -2;

}

res=mysql_query(mysql, "INSERT INTO STUDENT_TBL (id,name,age) values ('','sbsb1','20'),('','sbsb2','21'),('','sbsb3','19')");

if(!res)

printf("Insert %lu rows.\n",(unsigned long)mysql_affected_rows(mysql));

else

{

printf("Insert failed:%d %s\n",mysql_errno(mysql),mysql_error(mysql));

return -3;

}

res=mysql_query(mysql, "select * from STUDENT_TBL where age='21'");

if(res)

{

printf("action failed:%d %s\n",mysql_errno(mysql),mysql_error(mysql));

return -4;

}

result = mysql_store_result(mysql);

if(result)

{

printf("Retrieved %lu rows.\n",(unsigned long)mysql_num_rows(result));

while (sqlrow=mysql_fetch_row(result) )

{

// printf("fetching data...\n");

display_row(mysql);

}

if(mysql_errno(mysql))

{

printf("Retrive error:%s\n",mysql_error(mysql));

return -5;

}

mysql_free_result(result);

mysql_close(mysql);

printf("connection closed!\n");

getchar();

}

void display_row(MYSQL *ptr)

{

unsigned int field_num=0;

while (field_num

{

printf("%s\t",sqlrow[field_num++]);

}

printf("\n");

}

snmpwalk -v 1 -c public localhost hrSWRunPath

然后可以获得如下内容

HOST-RESOURCES-MIB::hrSWRunPath.13364 = STRING: "redis-server"

HOST-RESOURCES-MIB::hrSWRunPath.14061 = STRING: "php-fpm: master process (/usr/local/php/etc/php-fpm.conf)"

HOST-RESOURCES-MIB::hrSWRunPath.15015 = STRING: "/usr/sbin/httpd"

HOST-RESOURCES-MIB::hrSWRunPath.15819 = STRING: "sleep"

HOST-RESOURCES-MIB::hrSWRunPath.20330 = STRING: "php-fpm: pool www"

HOST-RESOURCES-MIB::hrSWRunPath.21724 = STRING: "php-fpm: pool www"

然后比如我想检查redis-server的运行状态，那么就这么执行

[StevenLiu@liudeMacBook-Pro ~]$ snmpwalk -v 1 -c public localhost hrSWRunStatus.13364

HOST-RESOURCES-MIB::hrSWRunStatus.13364 = INTEGER: runnable(2)

[StevenLiu@liudeMacBook-Pro ~]$

上面是snmp的检测方式，runnable可以判断是否为僵尸进程

如果觉得那么做不方便，还是先检查为啥连接都被占空，这样才是解决根本

然后通过mysql连接接口去连接mysql检测就可以了，具体C代码实现

片段

#include

#include "./get_password.c"

MYSQL *mysql;

MYSQL_RES *result;

MYSQL_ROW sqlrow;

extern char *input_password(char pd[]);

void display_row(MYSQL *ptr);

char *get_name( char name[])

{

int i=0;

char ch;

while((ch=getchar())!='\n')

{

name[i++]=ch;

}

name[i]='\0';

return name;

}

int main()

{

int res;

char name[20],

pd[20];

mysql = mysql_init(NULL);

if(mysql)

printf("mysql init succeed!\n");

else

{

printf("mysql init failed!\n");

return -1;

}

printf("Input you name:\n");

get_name(name);

printf("Input Password:\n");

input_password(pd);

printf("\n");

mysql=mysql_real_connect(mysql, "localhost",name, pd, "test",0,NULL,0);

if(mysql)

printf("mysql connect succeed!\n");

else

{

printf("mysql connect failed!\n");

return -2;

}

res=mysql_query(mysql, "INSERT INTO STUDENT_TBL (id,name,age) values ('','sbsb1','20'),('','sbsb2','21'),('','sbsb3','19')");

if(!res)

printf("Insert %lu rows.\n",(unsigned long)mysql_affected_rows(mysql));

else

{

printf("Insert failed:%d %s\n",mysql_errno(mysql),mysql_error(mysql));

return -3;

}

res=mysql_query(mysql, "select * from STUDENT_TBL where age='21'");

if(res)

{

printf("action failed:%d %s\n",mysql_errno(mysql),mysql_error(mysql));

return -4;

}

result = mysql_store_result(mysql);

if(result)

{

printf("Retrieved %lu rows.\n",(unsigned long)mysql_num_rows(result));

while (sqlrow=mysql_fetch_row(result) )

{

// printf("fetching data...\n");

display_row(mysql);

}

if(mysql_errno(mysql))

{

printf("Retrive error:%s\n",mysql_error(mysql));

return -5;

}

mysql_free_result(result);

mysql_close(mysql);

printf("connection closed!\n");

getchar();

}

void display_row(MYSQL *ptr)

{

unsigned int field_num=0;

while (field_num

{

printf("%s\t",sqlrow[field_num++]);

}

printf("\n");

}

如何检测MYSQL仍正常提供运行

相关文章