Zabbix monitoring MySQL database and master-slave database

Zabbix monitoring MySQL master-slave database in the footsteps of the user name and password will appear the following error " warning:using A password on theCommand Line interface can insecure",The error is due to the MySQL 5.6 version of the password security policy, the previous version can be used in the command line with the password will be forced to error, so when using Zabbix monitoring MySQL, will be due to receiveZabbix Client LogsErrorInformation. combined with the solution of users, will be sorted out for your reference.

One, thezabbix is monitored by the settings of the end:

1, first configure the mysql database, configure the mysql --login-pathde Secure Login:

Set --login-path:

[[email protected] conf]# mysql_config_editor set--login-path=local--host=localhost--user=zabbix-p

Enter Password:

[Email protected] conf]# mysql_config_editor print--all

[Local]

user = Zabbix

Password = * * * *

host = localhost

Command explanation:

--login-path is set to access the name, I set the local;

--host is the host address that specifies the allowed access, which is configured when you grant;

--user is a user name and is also configured for grant time;

-P is the specified password, which is also the grant configuration

2, enter mysql, modify the Zabbix account permissions and password:

mysql> create user ' Zabbix ' identified by ' Zabbix '

;

Query OK, 0 rows Affected (0.00 sec)

Mysql> select User,host from Mysql.user;

+------------+--------------+

| user | Host |

+------------+--------------+

| Databak | %            |

| ppt | %            |

| Root | %            |

| Slave_user | %            |

| Zabbix | %            |

| Slave_user | 192.168.1.49 |

+------------+--------------+

6 rows in Set (0.00 sec)

Mysql> GRANT all privileges in ' Zabbix '. * to ' zabbix ' @ '% ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> commit;

3, test MySQL login-path=local

[[email protected] conf]# MySQL--login-path=local

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 542

Server Version:5.6.25-log Source Distribution

Copyright (c), Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

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

Mysql> quit

The security access mode is configured as well.

Second, in the client, it is necessary to display the content of the monitoring JSON , and then the server can be filtered through the regular expression of the results, footstep content as follows, and then put to /usr/local/zabbix/bin Folder.

#!/bin/bash#fucation:mysql low-level discovery#script_name mysql_low_discovery.shmysql ()  {             port= ($ (sudo /bin/netstat - tpln | awk -f  "[ :]+"   '/[m]ysql/ {print $4} ')              printf  ' {\ n '              printf  ' \ t ' data: [\ n '                 for key in ${!port[@]}                    do                        if  [[  "${#port [@]}"  -gt 1 &&  "${key}"  -ne  "$ ((${#port [@]}-1)]"  ]];then&nbsP;             socket= ' ps aux|grep  ${port[${key}]}|grep -v grep|awk -f  ' = '   ' {print $10} ' |cut -d  '   '  -f 1 '                            printf  ' \t {\n '                             printf  "\t\t\t\" {#MYSQLPORT}\ ": \" ${port[${key}]}\ "},\n"                        else [[  "${key}"  -eq  "((${#port [@]}-1))"  ]]               socket= ' Ps aux|grep ${port[${key}]}|grep -v grep |awk -f  ' = '   ' {print $10} ' |cut -d  '   '  -f 1 '                             printf  ' \t {\n '                            printf  "\t\t\t\" {#MYSQLPORT}\ ": \" ${port[${key}]}\ "}\n"                         fi                done                           printf  ' \ t  ]\n '                            printf  '}\n '}$1 

Results such as:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/75/DC/wKioL1ZEA_bj5sUEAAAzatpIZ30009.png "title=" 1.png " alt= "Wkiol1zea_bj5sueaaazatpiz30009.png"/>

2. Assign executable permissions to script low_level_discovery

[Email protected] conf]# chmod +x/usr/local/zabbix/bin/low_level_discovery

[Email protected] conf]# Ll/usr/local/zabbix/bin/low_level_discovery

-rwxr-xr-x 1 root root 1055 Nov 15:45/usr/local/zabbix/bin/low_level_discovery

3, allow Zabbix user to run Mysql,netstat without password ,/usr/local/mysql/bin is the MySQL program address, can be freely modified according to the situation.

[[email protected] bin]# echo "Zabbix all= (Root) nopasswd:/usr/local/mysql/bin/mysql,/bin/netstat" >>/etc/ Sudoers

As shown:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/75/DC/wKioL1ZEBJKw2ur_AADfbJxIIWc562.png "style=" float: none; "title=" 1.png "alt=" Wkiol1zebjkw2ur_aadfbjxiiwc562.png "/>

4, disable requiretty does not close the words will not be able to obtain data,Zabbix log will also error .

[Email protected] bin]# sed-i ' s/^defaults.*.requiretty/#Defaults requiretty/'/etc/sudoers

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M00/75/DE/wKiom1ZEBErCTox2AADNej1K3ZE259.png "style=" float: none; "title=" 2.png "alt=" Wkiom1zeberctox2aadnej1k3ze259.png "/>

5, modify the zabbix_agentd.conf file to add the last content:

Userparameter=zabbix_low_discovery[*],/bin/bash/usr/local/zabbix/bin/low_level_discovery $

Userparameter=mysql_stats_5.6[*],sudo/usr/local/mysql/bin/mysql--login-path=local-p $1-e "show Global Status" |grep "\<$2\>" |cut-f2

Userparameter=mysql_stats_slave_5.6[*],sudo/usr/local/mysql/bin/mysql--login-path=local-p $1-e "show slave status\ G "|grep" \<$2\> "|awk" {if ($NF = = "Yes") {print 1} else {print 0}} '

6, test on the service side.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/75/DC/wKioL1ZEBcWCHqv2AAA4TKr1NsQ715.png "title=" 3.png " alt= "Wkiol1zebcwchqv2aaa4tkr1nsq715.png"/>

Third, Zabbix server-side Web settings:

1, template import

template mysql  auto discovery import to ----- Span style= "font-family: ' The song Body '; > template ------ load ------- select file.

2, set regular expression: Manage ----- General ------ Regular Expressions -------- new regular expressions ------- Enter a name and test string -------- Test -------- saved.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/75/DE/wKiom1ZEBgKA91wdAAHwjtswHkU509.png "title=" 1.png " alt= "Wkiom1zebgka91wdaahwjtswhku509.png"/>

3, set the update interval of the template and associate the host to the template (if the settings are too small, one is the server pressure, the other is that you detect the port suddenly down, have not come to the urgent alarm, the host through the Jsonlai To obtain this information, Would think that without this port, the template will automatically close the contents of this monitoring item)

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/75/DE/wKiom1ZEBkmTsByWAADCWIfG4SI816.png "title=" 2.png " alt= "Wkiom1zebkmtsbywaadcwifg4si816.png"/>

4, the final monitoring content is as follows:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/75/DE/wKiom1ZEBtqgQIvSAAI8ZcrnT-c387.png "title=" 3.png " alt= "Wkiom1zebtqgqivsaai8zcrnt-c387.png"/>

Finally very grateful to 51cto dl528888 (http://dl528888.blog.51cto.com/2382721/1677545) blogger, most of this article is reference to his article, once again expressed thanks, just started to have problems, Bloggers are very enthusiastic for me to solve the remote, again expressed very very grateful.

This article is from the "Breght Moon" blog, make sure to keep this source http://liqingbiao.blog.51cto.com/3044896/1712080

Zabbix monitoring MySQL database and master-slave database