开发mysql监控脚本
需要了解的命令:
ss命令展示
xiao123@xiao123:~/Downloads/shscripts$ ss -tunlp
Netid State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
udp UNCONN 0 0 0.0.0.0:40660 0.0.0.0:*
udp UNCONN 0 0 127.0.0.53%lo:53 0.0.0.0:*
udp UNCONN 0 0 0.0.0.0:5353 0.0.0.0:*
udp UNCONN 0 0 0.0.0.0:631 0.0.0.0:*
udp UNCONN 0 0 [::]:5353 [::]:*
udp UNCONN 0 0 [fe80::a78d:cba6:9b80:882a]%enp0s3:546 [::]:*
udp UNCONN 0 0 [::]:35362 [::]:*
tcp LISTEN 0 128 127.0.0.1:631 0.0.0.0:*
tcp LISTEN 0 4096 127.0.0.53%lo:53 0.0.0.0:*
tcp LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
tcp LISTEN 0 128 [::1]:631 [::]:*
tcp LISTEN 0 128 [::]:22 [::]:*
xiao123@xiao123:~/Downloads/shscripts$
netstat命令展示
xiao123@xiao123:~/Downloads/shscripts$ sudo netstat -tunlp
[sudo] password for xiao123:
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN 104162/cupsd
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 431/systemd-resolve
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 3105/sshd: /usr/sbi
tcp6 0 0 ::1:631 :::* LISTEN 104162/cupsd
tcp6 0 0 :::22 :::* LISTEN 3105/sshd: /usr/sbi
udp 0 0 0.0.0.0:40660 0.0.0.0:* 627/avahi-daemon: r
udp 0 0 127.0.0.53:53 0.0.0.0:* 431/systemd-resolve
udp 0 0 0.0.0.0:5353 0.0.0.0:* 627/avahi-daemon: r
udp 0 0 0.0.0.0:631 0.0.0.0:* 104163/cups-browsed
udp6 0 0 :::5353 :::* 627/avahi-daemon: r
udp6 0 0 fe80::a78d:cba6:9b8:546 :::* 632/NetworkManager
udp6 0 0 :::35362 :::* 627/avahi-daemon: r
xiao123@xiao123:~/Downloads/shscripts$
nmap命令展示
xiao123@xiao123:~/Downloads/shscripts$ nmap 127.0.0.1 -p 22
Starting Nmap 7.80 ( https://nmap.org ) at 2023-02-27 22:42 CST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000058s latency).
PORT STATE SERVICE
22/tcp open ssh
Nmap done: 1 IP address (1 host up) scanned in 0.02 seconds
xiao123@xiao123:~/Downloads/shscripts$
本地检测mysql运行状态
netstat -tunlp | grep mysql | wc -l #显示为1就是端口存活
ss -tunlp | grep mysql | wc -l #显示为1就是端口存活
lsof -i tcp:3360 | wc -l #返回结果不为0则证明端口存活
远程检测mysql运行状态
apt-get install telnet nmap nc
or yum install telnet nmap nc -y
nmap 127.0.0.1 -p 3360 | grep open | wc -l # 显示为1就是端口存活
echo -e “\n” |telnet 127.0.0.1 222 2> /dev/null | grep Connected|wc -l # 显示为1就是端口存活
ps -ef | grep mysql | grep -v grep | wc -l #显示为1就是进程存活
通过编程语言连接mysql
通过访问应用程序接口,读取数据库,查看是否能拿到数据,从而得知mysql是否运行。所使用到的语言有php
和python
。
php连接mysql
1. 准备php的环境依赖
sudo apt-get install php-mysql php php-mysqlnd
or yum remove php-mysql; yum install php php-mysqlnd
2. 开发php连接mysql的代码
xiao123@xiao123:~/Downloads/shscripts$ cat mysql_test.php
<?php
$mysql_id=mysqli_connect("localhost", "root", "chaoge888") or mysql_error();
if ($mysql_id){
echo "mysql connection successful,chaoge 666~~~~~";
}else{
echo mysql_error();
}
xiao123@xiao123:~/Downloads/shscripts$
python连接mysql
1. 准备python的环境依赖
sudo apt-get install python3 python3-devl python3-pip
yum install python3 python3-devl python3-pip
2. 通过python包管理工具安装mysql依赖
pip install pymysql
3. 开发python连接mysql的代码
```python
xiao123@xiao123:~/Downloads/shscripts$ cat ./mysql_test.py
import pymysql
db = pymysql.connect(
host="localhost",
port=3380,
user="root",
password="chaoge888",
db='mysql',
charset='utf8'
)
cursor=db.cursor()
cursor.excute("select version()")
data=cursor.fetchone()
print("数据库连接正确,数据库版本是%s" %data)
db.close()
xiao123@xiao123:~/Downloads/shscripts$
shell脚本监控mysql运行状态
# 方法1
if [ `netstat -tunlp | grep mysql | wc -l` = 1 ]; then
echo "mysql is running."
else
echo "restart mysql service."
systemctl restart mysql
fi
# 方法2
if [ `ss -tunlp | grep mysql | wc -l` = 1 ]; then
echo "mysql is running."
else
echo "restart mysql service."
systemctl restart mysql
fi
# 方法3
if [ `ss -tunlp | grep mysql | wc -l` != 0 ]; then
echo "mysql is running."
else
echo "restart mysql service."
systemctl restart mysql
fi
# 方法4
php mysql_test.php
if [ "$?" = 0 ];then
echo "mysql is running."
else
echo "restart mysql service."
systemctl restart mysql
fi
# 方法5
python mysql_test.py
if [ "$?" = 0 ];then
echo "mysql is running."
else
echo "restart mysql service."
systemctl restart mysql
fi