在azure雲上面,使用Zabbix監控mysql中,發現在/usr/local/mysql/my.cnf裡面設置的默認用戶名密碼無效,出不來數據,而且在zabbix服務器上,使用zabbix_get也報錯failed,如下
[root@zabbix_serv_121_12 ~]#/usr/local/zabbix/bin/zabbix_get -s 192.168.13.13 -p10050 -kmysql.status[Uptime]
/usr/local/mysql/bin/mysqladmin: connect toserver at 'localhost' failed
error: 'Access denied for user'adminuser'@'localhost' (using password: NO)'
[root@zabbix_serv_121_12 ~]#
在/usr/local/mysql/my.cnf裡面添加所有客戶端都可以使用的[client]選項卡配額好mysql的用戶名密碼
[root@azure_test_dbm2_3_13 mysql]# vim.my.cnf
[client]
MYSQL_USER=zabbix
password=ys_test0418
再去zabbix服務器上,使用get來測試下,
[root@zabbix_serv_121_12 ~]#
[root@zabbix_serv_121_12 ~]#/usr/local/zabbix/bin/zabbix_get -s 192.168.13.13 -p10050 -kmysql.status[Uptime]
/usr/local/mysql/bin/mysqladmin: connect toserver at 'l27.0.0.1' failed
error: 'Unknown MySQL server host 'l27.0.0.1'(0)'
Check that mysqld is running on l27.0.0.1and that the port is 3317.
You can check this by doing 'telnetl27.0.0.1 3317'
[root@zabbix_serv_121_12 ~]#
本文章來源blog地址:http://blog.csdn.net/mchdba/article/details/51344063,謝絕轉載。
Zabbix監控mysql是通過遠程調用mysql服務器的本地的mysqladmin組件來獲取mysql數據庫信息的,這樣只要在本地mysql服務器調試通過了,那麼就應ok了。先在本地免密碼登錄調試通過吧。
嘗試本地mysqladmin,無效:
[root@azure_test_dbm2_3_13 zabbix]#/usr/local/mysql/bin/mysqladmin -hl27.0.0.1 -uzabbix -pys_test0418 -P3317 -S/usr/local/mysql/mysql.sock extended-status grep -w Com_update
Warning: Using a password on the commandline interface can be insecure.
Got error: Unknown MySQL server host'l27.0.0.1' (0)
嘗試本地mysql,無效:
[root@azure_test_dbm2_3_13 zabbix]#/usr/local/mysql/bin/mysql -hl27.0.0.1 -uzabbix -pys_test0418 -P3317
Warning: Using a password on the commandline interface can be insecure.
ERROR 2005 (HY000): Unknown MySQL serverhost 'l27.0.0.1' (0)
[root@azure_test_dbm2_3_13 zabbix]# mysql-hl27.0.0.1 -uzabbix -pys_test0418 -P3317
Warning: Using a password on the commandline interface can be insecure.
ERROR 2005 (HY000): Unknown MySQL server host'l27.0.0.1' (0)
[root@azure_test_dbm2_3_13 zabbix]#mysql -uzabbix -pys_test0418 -P3317
Warning: Using a password on the commandline interface can be insecure.
ERROR 1045 (28000): Access denied for user'zabbix'@'localhost' (using password: YES)
[root@azure_test_dbm2_3_13 zabbix]#
問題在哪裡呢?
看來默認的/usr/local/mysql/my.cnf裡面的配置對於登錄來說無效了,那麼我們需要去看下mysqladmin識別哪些my.cnf以及他們的路徑地址:
[root@azure_test_dbm2_3_13 mysql]#mysqladmin --help
……
Default options are read from the followingfiles in the given order:
/etc/my.cnf /etc/mysql/my.cnf/usr/local/mysql/etc/my.cnf ~/.my.cnf
……
[root@azure_test_dbm2_3_13 mysql]#
再ll下看是否能找到這些配置文件:
[root@azure_test_dbm2_3_13 mysql]# ll/etc/my.cnf
ls: cannot access /etc/my.cnf: No such fileor directory
[root@azure_test_dbm2_3_13 mysql]# ll/etc/mysql/my.cnf
ls: cannot access /etc/mysql/my.cnf: Nosuch file or directory
[root@azure_test_dbm2_3_13 mysql]# ll/usr/local/mysql/etc/my.cnf
ls: cannot access/usr/local/mysql/etc/my.cnf: No such file or directory
[root@azure_test_dbm2_3_13 mysql]# ll~/.my.cnf
ls: cannot access /root/.my.cnf: No suchfile or directory
[root@azure_test_dbm2_3_13 mysql]# ll/usr/local/mysql/etc
ls: cannot access /usr/local/mysql/etc: Nosuch file or directory
[root@azure_test_dbm2_3_13 mysql]#
一個也沒用找到,原來我的mysql是源碼編譯的,編譯路徑是在/usr/local/mysql/my.cnf,但是mysqladmin不識別了。所以需要安裝提示准備一個新的my.cnf吧
在mysqladmin識別的my.cnf路徑中(/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf )選擇一個/usr/local/mysql/etc/my.cnf,然後新建並且在裡面錄制好用戶名密碼,然後調試通過
# 在mysql服務器上面准備新的配置文件my.cnf
[root@azure_test_dbm2_3_13 mysql]# mkdir /usr/local/mysql/etc
[root@azure_test_dbm2_3_13 mysql]#
[root@azure_test_dbm2_3_13 mysql]# vim /usr/local/mysql/etc/my.cnf
[mysqladmin]
user=zabbix
password=ys_test0418
socket=/usr/local/mysql/mysql.sock
# 賦予mysql用戶訪問權限
[root@azure_test_dbm2_3_13 mysql]# chown -R mysql.mysql /usr/local/mysql/etc/my.cnf
[root@azure_test_dbm2_3_13 mysql]# chmod u+x /usr/local/mysql/etc/my.cnf
[root@azure_test_dbm2_3_13 mysql]#
# 重啟下,因為我發現不重啟的話,不生效
[root@azure_test_dbm2_3_13 mysql]# service mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL.. [ OK ]
[root@azure_test_dbm2_3_13 mysql]#
然後去zabbix服務器上驗證,可以獲取到mysql服務器的數據信息:
[root@zabbix_test_121_12 ~]# /usr/local/zabbix/bin/zabbix_get -s 192.168.13.13 -p10050 -k mysql.status[Uptime]
154408
[root@zabbix_test_121_12 ~]#