Linux下Zabbix5.0 LTS添加MySQL监控，实现邮件报警并执行预处理操作

# 检查是否已安装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 -Vmysql  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 mysqltcp6       0      0 :::3306                 :::*                    LISTEN      7350/mysqld[root@ZabbixClient-01 ~]# ps -ef | grep mysqlmysql      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.log2021-08-19T04:08:59.720748Z 1 [Note] A temporary password is generated for root@localhost: .!aTlyih4r2y[root@Mysql-Master01 ~]# mysql_secure_installationSecuring the MySQL server deployment.Enter password for user root:　　# 输入MySQL初始密码 .!aTlyih4r2yThe existing password for the user account root has expired. Please set a new password.New password:　　　　# 输入符合复杂密码策略的新密码 Client@01Re-enter new password:　　# 再次输入 Client@01The validate_password plugin is installed on the server.The subsequent steps will run with the existing configurationof the plugin.Using existing password for root.Estimated strength of the password: 100Change 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 havea user account created for them. This is intended only fortesting, and to make the installation go a bit smoother.You should remove them before moving into a productionenvironment.Remove anonymous users? (Press y|Y for Yes, any other key for No) : y　　　　# 是否删除匿名用户Success.Normally, root should only be allowed to connect fromlocalhost. This ensures that someone cannot guess atthe 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 thatanyone can access. This is also intended only for testing,and should be removed before moving into a productionenvironment.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 changesmade 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 -pEnter password:　　# 输入上面刚更改的密码 Client@01Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 113Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type help; or /h for help. Type /c to clear the current input statement.# 创建用户zbx_monitor 密码ZabbixClient@01mysql> 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 -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or /g.Your MySQL connection id is 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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_monitorpassword=ZabbixClient@01[mysqladmin]user=zbx_monitorpassword=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

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

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

# 增加远程执行命令操作配置[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