linux shell 入门学习笔记17 mysql脚本开发

开发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  sshNmap 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-mysqlndor 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-pipyum 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 pymysqldb = 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 ]; thenecho "mysql is running."
elseecho "restart mysql service."systemctl restart mysql
fi# 方法2
if [ `ss -tunlp | grep mysql | wc -l` = 1 ]; thenecho "mysql is running."
elseecho "restart mysql service."systemctl restart mysql
fi# 方法3
if [ `ss -tunlp | grep mysql | wc -l` != 0 ]; thenecho "mysql is running."
elseecho "restart mysql service."systemctl restart mysql
fi# 方法4
php mysql_test.php
if [ "$?" = 0 ];thenecho "mysql is running."
elseecho "restart mysql service."systemctl restart mysql
fi# 方法5
python mysql_test.py
if [ "$?" = 0 ];thenecho "mysql is running."
elseecho "restart mysql service."systemctl restart mysql
fi