mysql数据库监控利器lepus天兔工具安装和部署

时间:2024-03-06 14:47:49


需要的核心包如下:
以下软件包只需要部署在监控机即可。被监控机无需部署。
1.MySQL 5.0及以上(必须,用来存储监控系统采集的数据)
2.Apache 2.2及以上 (必须,WEB服务器运行服务器)
3.PHP 5.3以上 (必须,提供WEB界面支持)
4.Python2 (必须,推荐2.6及以上版本,执行数据采集和报警任务,不支持Python3)
5.Python连接和监控数据库的相关驱动模块包:
MySQLdb for python (Python连接MySQl的接口,用于监控MySQL,此模块必须安装)
cx_oracle for python (Python连接Oracle的接口,非必须,如果需要监控oracle此模块必须安装)
Pymongo for python (Python连接MongoDB的接口,非必须,如果需要监控MongoDB此模块必须安装)
redis-py for python (Python连接Redis的接口,非必须,如果需要监控Redis此模块必须安装)

一.基本环境安装
(1)安装LAMP环境Lepus对MySQL性能还是有一定的影响的,特别是当我们的被监控端越来越多,监控项也比较多时,建议安装mysql5.6二进制版本)

先安装mysql,二进制安装包mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz

groupadd -r mysql
useradd -g mysql -r -s /sbin/nologin mysql
tar -zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz -C /usr/local

cd /usr/local
ln -sv mysql-5.6.36-linux-glibc2.5-x86_64 mysql

cd mysql
chown -R root.mysql ./*

mkdir /data/mydata -p
chown -R mysql.mysql /data/mydata
mkdir /data/binlogs
chown -R mysql.mysql /data/binlogs

cd /usr/local
scripts/mysql_install_db --datadir=/data/mydata --user=mysql

将服务脚本拷贝到启动项
cp support-files/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
chkconfig --list mysqld

cp my.cnf /etc/my.cnf

vim /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
port = 3306
innodb_file_per_table = 1
init-connect = \'SET NAMES utf8mb4\'
character-set-server = utf8mb4
default_storage_engine = InnoDB
skip-name-resolve
skip-external-locking

datadir = /data/mydata
log-bin=/data/binlogs/master-bin
binlog_format=row

socket=/tmp/mysql.sock

interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

# 启动服务
service mysqld start

删除匿名用户,配置root密码

mysql> update user set password=PASSWORD(\'AJaC7uiKkYxbytGiEJBn\') where user=\'root\';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> flush privileges;


通过以下方式安装LAP
yum install httpd php -y

(2)开机自启动httpd,mysqld
chkconfig httpd on
chkconfig mysqld on

(3)安装lepus server需要的依赖包
yum install gcc python-devel mysql-devel net-snmp-devel curl-devel perl-DBI php-gd php-mysql php-bcmath php-mbstring php-xml -y


(4)配置httpd并启动
# vim /etc/httpd/conf/httpd.conf
# 修改默认的ServerName
ServerName localhost:8090
监听8090端口
Listen 8090
启动httpd服务
# service httpd start

(5)配置php
sed -i "s/;date.timezone =/date.timezone = Asia\/Shanghai/g" /etc/php.ini
sed -i "s#max_execution_time = 30#max_execution_time = 300#g" /etc/php.ini
sed -i "s#post_max_size = 8M#post_max_size = 32M#g" /etc/php.ini
sed -i "s#max_input_time = 60#max_input_time = 300#g" /etc/php.ini
sed -i "s#memory_limit = 128M#memory_limit = 128M#g" /etc/php.ini
sed -i "/;mbstring.func_overload = 0/ambstring.func_overload = 2\n" /etc/php.ini

验证lamp环境是否Ok

vim /var/www/html/info.php

<?php
phpinfo();
?>

访问http://ip/info.php看是否打印lamp环境信息


(7) 安装MySQLdb-python,解压后,编辑site.cfg,找到mysql_config,把#号去掉,并指定mysql_config的路径
mkdir lepus  
wget https://github.com/farcepest/MySQLdb1/archive/MySQLdb-1.2.5.zip
unzip MySQLdb-python.zip
[root@u04ck04 lepus]# cd MySQLdb1-MySQLdb-1.2.5/
[root@u04ck04 MySQLdb1-MySQLdb-1.2.5]# which mysql_config
/usr/local/mysql/bin/mysql_config(此处查找mysql_config命令的所在,不同方式安装的mysql,可能存在不同的地方!)
[root@vpn MySQLdb1-master]# pwd
/data/mysqldata/lepus/MySQLdb1-MySQLdb-1.2.5

[root@vpn MySQLdb1-master]# vi site.cfg
mysql_config = /usr/local/mysql/bin/mysql_config    (位置填写上一个查找到的位置)

# yum install -y python-pip
# python setup.py build
# python setup.py install

(8)安装redis驱动,如果没有redis需要监控可以跳过此步骤:
# cd /usr/local/src/
# wget https://pypi.python.org/packages/source/r/redis/redis-2.10.3.tar.gz
# tar xf redis-2.10.3.tar.gz
# cd redis-2.10.3
# python setup.py install

(9)安装pymongo模块(如果没有需要监控的Mongodb可以跳过)

# wget https://github.com/mongodb/mongo-python-driver/archive/2.7.2.zip
# upzip 2.7.2.zip
# cd mongo-python-driver-2.7.2
# python setup.py install

测试各个模块安装是否正常运行
[root@u04ck04 lepus]# pwd
/usr/local/lepus
[root@u04ck04 lepus]# python test_driver_mysql.py
MySQL python drivier is ok!
[root@u04ck04 lepus]# python test_driver_mongodb.py
MongoDB python drivier is ok!
[root@u04ck04 lepus]# python test_driver_redis.py
Redis python drivier is ok!

二、安装Lepus采集器
(1) 下载Lepus软件包下载地址:http://www.lepus.cc/soft/17
然后登录数据库创建lepus数据库和MySQL用户
# pwd
/usr/local/src
# unzip Lepus.zip

#创建lepus库并授权
mysql -uroot -pAJaC7uiKkYxbytGiEJBn -e "create database lepus default character set utf8;"
mysql -uroot -pAJaC7uiKkYxbytGiEJBn -e "grant select,insert,update,delete,create on lepus.* to \'lepus\'@\'localhost\' identified by \'lepus\';"
mysql -uroot -pAJaC7uiKkYxbytGiEJBn -e "grant select,insert,update,delete,create on lepus.* to \'lepus\'@\'127.0.0.1\' identified by \'lepus\';"
mysql -uroot -pAJaC7uiKkYxbytGiEJBn -e "flush privileges"

(2) 导入lepus数据库初始化数据,建议使用root用户导,进入到lepus解包以后的目录
cd /usr/local/src/lepus_v3.7/sql
mysql -uroot -pAJaC7uiKkYxbytGiEJBn lepus < lepus_table.sql
mysql -uroot -pAJaC7uiKkYxbytGiEJBn lepus < lepus_data.sql

验证表是否创建成功
# mysql -uroot -pAJaC7uiKkYxbytGiEJBn -e "use lepus;show tables;"
Warning: Using a password on the command line interface can be insecure.
+---------------------------------+
| Tables_in_lepus |
+---------------------------------+
| admin_log |
| admin_menu |
| admin_privilege |
| admin_role |
| admin_role_privilege |
| admin_user |
| admin_user_role |
| alarm |
| alarm_history |
| alarm_temp |
| db_servers_mongodb |
| db_servers_mysql |
| db_servers_oracle |
| db_servers_os |
| db_servers_redis |
| db_status |
| lepus_status |
| mongodb_status |
| mongodb_status_history |
| mysql_bigtable |
| mysql_bigtable_history |
| mysql_connected |
| mysql_processlist |
| mysql_replication |
| mysql_replication_history |
| mysql_slow_query_review |
| mysql_slow_query_review_history |
| mysql_slow_query_sendmail_log |
| mysql_status |
| mysql_status_history |
| options |
| oracle_status |
| oracle_status_history |
| oracle_tablespace |
| oracle_tablespace_history |
| os_disk |
| os_disk_history |
| os_diskio |
| os_diskio_history |
| os_net |
| os_net_history |
| os_status |
| os_status_history |
| redis_replication |
| redis_replication_history |
| redis_status |
| redis_status_history |
+---------------------------------+


(3) 安装lepus并运行脚本install.sh,可以打开看一下,默认安装/usr/local/lepus下,可以通过修改脚本的路径安装在别的目录下。

# pwd
/usr/local/src/lepus_v3.7/python
[root@u04ck04 python]# sh install.sh
[note] lepus will be install on basedir: /usr/local/lepus
[note] /usr/local/lepus directory does not exist,will be created.
[note] /usr/local/lepus directory created success.
[note] wait copy files.......
[note] change script permission.
[note] create links.
[note] install complete.

给全部脚本有执行权限,修改lepus连接数据库的配置文件,选项很简单不多说了
# cd /usr/local/lepus/
# chmod +x *.sh
# chmod +x *.py
# vim etc/config.ini
###监控机MySQL数据库连接地址###
[monitor_server]
host="127.0.0.1"
port=3306
user="lepus"
passwd="lepus"
dbname="lepus"

(4)启动lepus服务,启动时可以看看它的一些使用参数:

[root@u04ck04 lepus]# lepus --help
lepus help:
support-site: www.lepus.cc
====================================================================
start Start lepus monitor server; Command: #lepus start
stop Stop lepus monitor server; Command: #lepus stop
status Check lepus monitor run status; Command: #lepus status

# 启动lepus报错
[root@u04ck04 lepus]# lepus start
nohup: appending output to `nohup.out\'
lepus server start fail!

查看日志:
[root@u04ck04 lepus]# cat nohup.out
Traceback (most recent call last):
File "lepus.py", line 8, in <module>
import MySQLdb
File "/usr/lib64/python2.6/site-packages/MySQL_python-1.2.5-py2.6-linux-x86_64.egg/MySQLdb/__init__.py", line 19, in <module>
import _mysql
ImportError: libmysqlclient.so.18: cannot open shared object file: No such file or directory
错误原因:
找不到libmysqlclient.so.18文件
解决方法:
1.查找libmysqlclient.so.18文件位置:

# find / -name \'libmysqlclient.so.18\' -print
/usr/local/mysql/lib/libmysqlclient.so.18
2.设置软连接:
ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

3.重启系统的动态链接库
ldconfig

# 再次启动Ok
[root@u04ck04 lepus]# lepus start
nohup: appending output to `nohup.out\'
lepus server start success!

三、安装WEB管理台
(1) 去到lepus的解压目录下把php文件夹下的所有文件拷贝/var/www/html下,然后编辑文件/var/www/html/application/config/database.php
# cp -rf /usr/local/src/lepus_v3.7/php/* /var/www/html/
# ll /var/www/html/
total 24
drwxr-xr-x 15 root root 4096 Oct 13 14:08 application
-rw-r--r-- 1 root root 6605 Oct 13 14:08 index.php
-rw-r--r-- 1 root root 20 Oct 13 13:21 info.php
-rw-r--r-- 1 root root 2547 Oct 13 14:08 license.txt
drwxr-xr-x 8 root root 4096 Oct 13 14:08 system

# cd /var/www/html/application/config/

# vim database.php

$db[\'default\'][\'hostname\'] = \'127.0.0.1\';
$db[\'default\'][\'port\'] = \'3306\';
$db[\'default\'][\'username\'] = \'lepus\';
$db[\'default\'][\'password\'] = \'lepus\';
$db[\'default\'][\'database\'] = \'lepus\';
$db[\'default\'][\'dbdriver\'] = \'mysql\';

现在可以打开浏览器,输入ip地址,默认页面是http://ip/php,即可登录系统,默认的管理员账号是admin,密码Lepusadmin,记得修改密码


被监控mysql端创建用户
GRANT SELECT,PROCESS,REPLICATION CLIENT ON *.* TO lepus@\'%\' IDENTIFIED BY \'AJaC7uiKkYxby\';

添加mysql主机,发现部分主机报错,无法被监控:

A PHP Error was encountered

Severity: Notice

Message: Trying to get property of non-object

Filename: controllers/lp_mysql.php

Line Number: 72

跳过查看日志发现部分字段长度不够解决办法:

mysql>use lepus
mysql>alter table db_servers_mysql modify column host varchar(100);
mysql>alter table db_status modify column host varchar(100);
mysql>alter table mysql_status modify column max_connect_errors bigint(18);
mysql>alter table mysql_status_history modify column max_connect_errors bigint(18);

监控redis同样修改db_servers_redis
redis_replication
redis_replication_history
redis_status
redis_status_history

调试连接redis.py的代码:
[root@u04ck04 lepus]# python check_redis.py
2017-10-13 19:17:42 [INFO] check redis controller started.
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
/usr/lib/python2.6/site-packages/redis-2.10.3-py2.6.egg/redis/client.py:404: DeprecationWarning: "charset" is deprecated. Use "encoding" instead
\'"charset" is deprecated. Use "encoding" instead\'))
mysql execute: (1264, "Out of range value for column \'rdb_changes_since_last_save\' at row 1")
mysql execute: (1264, "Out of range value for column \'rdb_changes_since_last_save\' at row 1")
mysql execute: (1264, "Out of range value for column \'rdb_changes_since_last_save\' at row 1")
mysql execute: (1264, "Out of range value for column \'rdb_changes_since_last_save\' at row 1")
2017-10-13 19:17:43 [INFO] check redis controller finished.

在check_redis.py中找到相应的字段,打印对应的sql
connect=1
sql = "insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
param = (server_id,host,port,tags,role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children)
# print sql # 开启打印语句,再次调试可以看到对应的表字段的问题
func.mysql_exec(sql,param)

再次执行Python check_redis.py
其中一个示例:
insert into redis_status(server_id,host,port,tags,redis_role,connect,redis_version,redis_git_sha1,redis_git_dirty,redis_mode,os,arch_bits,multiplexing_api,gcc_version,process_id,run_id,tcp_port,uptime_in_seconds,uptime_in_days,hz,lru_clock,connected_clients,client_longest_output_list,client_biggest_input_buf,blocked_clients,used_memory,used_memory_human,used_memory_rss,used_memory_peak,used_memory_peak_human,used_memory_lua,mem_fragmentation_ratio,mem_allocator,loading,rdb_changes_since_last_save,rdb_bgsave_in_progress,rdb_last_save_time,rdb_last_bgsave_status,rdb_last_bgsave_time_sec,rdb_current_bgsave_time_sec,aof_enabled,aof_rewrite_in_progress,aof_rewrite_scheduled,aof_last_rewrite_time_sec,aof_current_rewrite_time_sec,aof_last_bgrewrite_status,total_connections_received,total_commands_processed,current_commands_processed,instantaneous_ops_per_sec,rejected_connections,expired_keys,evicted_keys,keyspace_hits,keyspace_misses,pubsub_channels,pubsub_patterns,latest_fork_usec,used_cpu_sys,used_cpu_user,used_cpu_sys_children,used_cpu_user_children) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
mysql execute: (1264, "Out of range value for column \'keyspace_misses\' at row 1")

可以看出以下表需要修改

alter table redis_status modify rdb_changes_since_last_save int(10);
alter table redis_status modify keyspace_hits bigint(4);
alter table redis_status modify keyspace_misses bigint(4);
alter table redis_status_history modify keyspace_hits bigint(4);
alter table redis_status_history modify rdb_changes_since_last_save int(10);


修改check_reids.py注释掉151行就解决redis不能监控slave的bug了
vim /usr/local/lepus/check_redis.py

149 except Exception, e:
150 logger_msg="check redis %s:%s : %s" %(host,port,e)
151 #logger.warning(logger_msg)
152
153 try:
154 connect=0
155 sql="insert into redis_status(server_id,host,port,tags,connect) values(%s,%s,%s,%s,%s)"
156 param=(server_id,host,port,tags,connect)
157 func.mysql_exec(sql,param)


mysql调试错误后面解决
[root@u04ck04 lepus]# python check_mysql.py
2017-10-13 19:56:32 [INFO] check mysql controller started.
2017-10-13 19:56:34 [WARNING] check mysql 10.19.150.242:3306 failure: -1 error totally whack
2017-10-13 19:56:34 [WARNING] check mysql 10.19.150.242:3306 failure: sleep 3 seconds and check again.
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
mysql execute: (1406, "Data too long for column \'host\' at row 1")
2017-10-13 19:56:42 [INFO] check mysql controller finished.

 

监控效果图: