Linux下Zabbix5.0 LTS添加MySQL監控，實現郵件報警并執行預處理操作

依據前文：Linux下Zabbix5.0 LTS監控基礎原理及安裝部署(圖文教程) 環境，繼續添加MySQL應用集。

第一部分：添加Zabbix自帶的MySQL應用集。

在ZabbixClient-01上操作。【官方rpm下載】

# 檢查是否已安裝MySQL，反之下載安裝
[root@ZabbixClient-01 ~] # rpm -qa | grep mysql
[root@ZabbixClient-01 ~] # wget https://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/mysql57-community-release-el7-10.noarch.rpm
[root@ZabbixClient-01 ~] # rpm -Uvh mysql57-community-release-el7-10.noarch.rpm
[root@ZabbixClient-01 ~] # yum list all | grep mysql-community
[root@ZabbixClient-01 ~] # yum install mysql-community-server mysql-community-client -y
[root@ZabbixClient-01 ~] # mysql -V
mysql  Ver 14.14 Distrib 5.7.36,  for Linux (x86_64) using  EditLine wrapper

# 檢查是否正常啟動
[root@ZabbixClient-01 ~] # systemctl start mysqld && systemctl enable mysqld
[root@ZabbixClient-01 ~] # netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      7350 /mysqld

[root@ZabbixClient-01 ~] # ps -ef | grep mysql
mysql      7350      1  0 14:43 ?        00:00:03  /usr/sbin/mysqld --daemonize --pid- file = /var/run/mysqld/mysqld .pid

# MySQL安全初始化
[root@Mysql-Master01 ~] # grep 'temporary password' /var/log/mysqld.log
2021-08-19T04:08:59.720748Z 1 [Note] A temporary password is generated  for root@localhost: .!aTlyih4r2y

[root@Mysql-Master01 ~] # mysql_secure_installation

Securing the MySQL server deployment.

Enter password  for user root:　　 # 輸入MySQL初始密碼 .!aTlyih4r2y

The existing password  for the user account root has expired. Please  set a new password.

New password:　　　　 # 輸入符合復雜密碼策略的新密碼 Client@01

Re-enter new password:　　 # 再次輸入 Client@01
The  'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password  for root.

Estimated strength of the password: 100
Change the password  for root ? ((Press y|Y  for Yes, any other key  for No) : n　　　　 # 上面以已經修改了，無需再修改

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created  for them. This is intended only  for
testing, and to  make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous  users ? (Press y|Y  for Yes, any other key  for No) : y　　　　 # 是否刪除匿名用戶
Success.


Normally, root should only be allowed to connect from
'localhost' . This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y  for Yes, any other key  for No) : y　　 # 是否禁用root遠程登錄
Success.

By default, MySQL comes with a database named  'test' that
anyone can access. This is also intended only  for testing,
and should be removed before moving into a production
environment.


Remove  test database and access to it? (Press y|Y  for Yes, any other key  for No) : y　　 # 是否刪除test庫和對test庫的訪問權限
 - Dropping  test database...
Success.

 - Removing privileges on  test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y  for Yes, any other key  for No) : y　　　 # 是否刷新授權表使修改生效
Success.

All  done !

# root測試登錄MySQL，并創建監控用戶
[root@ZabbixClient-01 ~] # mysql -u root -p
Enter password:　　 # 輸入上面剛更改的密碼 Client@01
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection  id is 113
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and /or its affiliates.

Oracle is a registered trademark of Oracle Corporation and /or its
affiliates. Other names may be trademarks of their respective
owners.

Type  'help;' or  '\h' for help. Type  '\c' to  clear the current input statement.
# 創建用戶zbx_monitor 密碼ZabbixClient@01
mysql> create user  'zbx_monitor' @ 'localhost' identified by  'ZabbixClient@01' ;
Query OK, 0 rows affected (0.00 sec)

# 授予zbx_monitor權限
mysql> grant usage,replication client,process,show databases,show view on *.* to  'zbx_monitor' @ 'localhost' ;
Query OK, 0 rows affected (0.00 sec)
　　或 
mysql> grant all privileges on *.* to  'zbx_monitor' @ 'localhost' ;
Query OK, 0 rows affected (0.00 sec)

# 刷新授權，使其立即生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>  select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| zbx_monitor   | localhost |
+---------------+-----------+
4 rows  in set (0.00 sec)

mysql> show grants  for 'zbx_monitor' @ 'localhost' ;
+----------------------------------------------------------+
| Grants  for zbx_monitor@localhost                         |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO  'zbx_monitor' @ 'localhost' |
+----------------------------------------------------------+
1 row  in set (0.00 sec)

# zbx_monitor用戶測試登錄，并查看已有權限
[root@ZabbixClient-01 ~] # mysql -u zbx_monitor -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection  id is 6
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and /or its affiliates.

Oracle is a registered trademark of Oracle Corporation and /or its
affiliates. Other names may be trademarks of their respective
owners.

Type  'help;' or  '\h' for help. Type  '\c' to  clear the current input statement.

mysql> show grants  for current_user();
+----------------------------------------------------------+
| Grants  for zbx_monitor@localhost                         |
+----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO  'zbx_monitor' @ 'localhost' |
+----------------------------------------------------------+
1 row  in set (0.00 sec)

# /etc/zabbix下新增一個文件 .my.cnf
[root@ZabbixClient-01 ~] # vim /etc/zabbix/.my.cnf
[mysql]
user=zbx_monitor
password=ZabbixClient@01

[mysqladmin]
user=zbx_monitor
password=ZabbixClient@01

# 搜索zabbix-agent自帶的MySQL Key配置文件
[root@ZabbixClient-01 ~] # find / -iname userparameter_mysql*
/usr/share/doc/zabbix-agent-5 .0.17 /userparameter_mysql .conf
[root@ZabbixClient-01 ~] # cp /usr/share/doc/zabbix-agent-5.0.17/userparameter_mysql.conf /etc/zabbix/zabbix_agentd.d/

# 修改userparameter_mysql.conf配置文件(其實只多了一個 HOME=/etc/zabbix)
[root@ZabbixClient-01 ~] # sed -i_bak$(date +%Y%m%d) -e 's#],#], HOME=/etc/zabbix#g' /etc/zabbix/zabbix_agentd.d/userparameter_mysql.conf
[root@ZabbixClient-01 ~] # systemctl restart zabbix-agent && systemctl restart mysqld

 Zabbix Web設置，添加MySQL監控集。

第二部分： 動作觸發實現163.com郵箱報警功能。

 發送手機短信獲取163郵箱授權碼。

Zabbix Web設置。

qq郵箱，已收到測試郵件。

創建二級管理員用戶群組。

 創建二級管理員用戶。

 創建動作觸發。

ZabbixClient-01測試停用MySQL服務，驗證結果 。

[root@ZabbixClient-01 ~]  # systemctl stop mysqld
[root@ZabbixClient-01 ~]  # ps -ef | grep mysqld
root      71766   1141  0 11:30 pts  /0 00:00:00   grep --color=auto mysqld

ZabbixClient-01測試啟用MySQL服務，驗證結果。

[root@ZabbixClient-01 ~] # systemctl start mysqld
[root@ZabbixClient-01 ~] # ps -ef | grep mysqld
mysql     96660      1  0 11:58 ?        00:00:00  /usr/sbin/mysqld --daemonize --pid- file = /var/run/mysqld/mysqld .pid
root      96816   1141  0 12:06 pts /0 00:00:00  grep --color=auto mysqld

第三部分：執行預處理操作。

設置MySQL告警停用后，自動執行重啟MySQL操作。

# 增加遠程執行命令操作配置
[root@ZabbixClient-01 ~] # sed -i -e '$a\EnableRemoteCommands=1\nLogRemoteCommands=1' /etc/zabbix/zabbix_agentd.conf
解析:EnableRemoteCommands=1     #允許遠程執行命令
　　 LogRemoteCommands=1       #開啟遠程執行命令操作日志

# 配置zabbix客戶端的sudo權限
[root@ZabbixClient-01 ~] # visudo
#....
# 末行添加以下兩行內容
# allows 'zabbix' user to restart mysqld without password.
zabbix ALL=NOPASSWD: /usr/bin/systemctl restart mysqld

至此，已完成所有部署設置。