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

需要的核心包如下：
以下软件包只需要部署在监控机即可。被监控机无需部署。
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.

 

监控效果图：