随笔 - 633,  文章 - 0,  评论 - 13,  阅读 - 48万
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

如果新系统尚未安装工具pip,可通过以下三步快速安装pip              
1.  yum -y install epel-release               
2.  yum -y install python-pip                
3.  pip --version  查看pip版本
若要更新pip,可以使用命令pip install --upgrade pip

pip install pymongo redis mysql-python    :安装python驱动

 

先执行:
安装MySQLdb for python
yum install MySQL-python
 
#允许所有用户远程访问 修改用户名和密码为你自己的
mysql> grant all privileges on *.* to 'lepus_user'@'%' identified by '123456';
#允许单个ip 修改用户名和密码为你自己的
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
#最后
mysql> FLUSH PRIVILEGES;
 
1. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '';

                                  这里的123456为你给新增权限用户设置的密码,%代表所有主机,也可以具体到你的主机ip地址

2.flush privileges;          这一步一定要做,不然无法成功! 这句表示从mysql数据库的grant表中重新加载权限数据

 
 

一、安装LAMP基础环境

1.

xampp:下载访问地址:https://sourceforge.net/projects/xampp/

$ wget https://sourceforge.net/projects/xampp/files/XAMPP%20Linux/5.5.38/xampp-linux-x64-5.5.38-3-installer.run    (注意:此处最好用5.5.38,不要用最新的版本,最新版本php连接数据库脚本发生变化,会有问题)
$ chmod +x xampp-linux-x64-5.5.38-3-installer.run
$ ./xampp-linux-x64-5.5.38-3-installer.run
2.追加环境变量(可执行文件、库文件和头文件):

 

vim /etc/profile
export PATH=$PATH:/opt/lampp/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/lampp/lib
export C_INCLUDE_PATH=$C_INCLUDE_PATH:/opt/lampp/include
 
:wq!:保存退出
 
3.source /etc/profile    :生效配置
4.启动LAMP:/opt/lampp/lampp start
5.为xampp添加如下软链接:
  $ ln -s /opt/lampp/lampp /etc/init.d/lampp  (添加软链接)
6.添加步骤5的软链接后,可以使用如下命令:
    查看lampp启动状态命令: service  lampp status
            启动lampp: service lampp start
            停止lampp: service lampp stop
 
7.让xampp开机自动启动
  $ ln -s /opt/lampp/lampp /etc/init.d/lampp  (添加软链接)
  $ chkconfig --add lampp
 
8.lampp卸载:
  直接用命令将opt下的lampp文件删除即可:
    cd /opt
    rm -rf lampp
  或者修改文件名:
    mv lampp lampptest    :将lampp文件重命名为lampptest
    mv lampptest lampp    :将lampptest文件重命名为lampp
 
二、安装MySQLdb for python
yum install MySQL-python
 
三、lepus安装采集器(lepus官网下载:http://www.lepus.cc/soft/17)

1.unzip lepus3.7.zip

unzip Lepus.zip

$ unzip Lepus3.8_Beta.zip
$ cd Lepus_v3.8_beta
 
mv lepus_v3.7 /opt/lepus/
cd lepus
 
#配置数据库(注意:一定要配置,不然会启动失败
$ which mysql_config
/opt/lampp/bin/mysql_config
$ vim site.cfg
mysql_config = /opt/lampp/bin/mysql_config
保存退出命令为    :wq!  
 
 
2.在监控机创建监控数据库并授权
mysql -u root 
mysql> create database lepus default character set utf8;
mysql> grant select,insert,update,delete,create on lepus.* to 'lepus_user'@'localhost' identified by '123456';
mysql> grant select,insert,update,delete,create on lepus.* to 'lepus_user'@'%' identified by '123456';
mysql> flush privileges;
 
按 ctrl+z:退出数据库
注意:语法没错,如果无法进行远程连接,记得开启防火墙端口3306或者关闭防火墙;
 
3.导入SQL文件夹里的SQL文件(表结构和数据文件)
mysql lepus < sql/lepus_table.sql
mysql lepus < sql/lepus_data.sql
4.被监控库的数据库只需创建lepus_monitor账号即可
mysql -u root 

mysql> grant select,super,process,reload,show databases,replication client on *.* to'lepus_monitor'@'%' identified by 'MANAGER';

mysql> flush privileges;

5.安装Lpeus程序

进入到软件包的python文件夹:cd python/

授予install.sh可执行权限:chmod +x install.sh
执行安装:./install.sh
 
6.修改配置文件:
进入到安装目录,默认为/usr/local/lepus: cd  /usr/local/lepus
$ cd /usr/local/lepus/
$ cat etc/config.ini
   vim etc/config.ini  或者 vim /usr/local/lepus/etc/config.ini
###监控机MySQL数据库连接地址###
[monitor_server]
host="192.168.100.198"      #机子IP
port=3306
user="lepus_user"
passwd="123456"
dbname="lepus"
7.启动Lepus (可选)
$ lepus start  :启动命令
lepus server start success!
$ lepus status :查看启动状态命令
lepus server is running.
  lepus stop   :停止命令
 
四、安装WEB管理台
 4.1. 在/opt/lampp/htdocs/ 文件夹下 新建目录 lepus:
  命令:mkdir /opt/lampp/htdocs/lepus
     或者  cd /opt/lampp/htdocs/
         mkdir lepus
4.2.将lepus原文件中的php下的文件内容复制到/opt/lampp/htdocs/lepus文件夹下:
  进入到lepus原文件路径:cd /opt/lepus
  复制其下的php文件夹下的所有内容到/opt/lampp/htdocs/lepus文件夹下:
    命令:cp -fr php/* /opt/lampp/htdocs/lepus/
(处于/opt/lepus/路径下:cd /opt/lepus
复制PHP文件夹里的文件到Apache对应的网站虚拟目录(备注:根本不同的安装方式,这个目录是不一样的,如果采用xampp安装的Apache环境,则默认程序目录为/opt/lampp/htdocs/):[root@localhost lepus_v3.7]# cp -fr php/* /opt/lampp/htdocs/)
4.3.打开/opt/lampp/htdocs/lepus文件夹下application/config/database.php文件,修改PHP连接监控服务器的数据库信息.:
 
$ cat /opt/lampp/htdocs/lepus/application/config/database.php
vim /opt/lampp/htdocs/lepus/application/config/database.php
 
$db['default']['hostname'] = '192.168.100.198';     #本机IP
$db['default']['port']     = '3306';
$db['default']['username'] = 'lepus_user';
$db['default']['password'] = '123456';
$db['default']['database'] = 'lepus';
$db['default']['dbdriver'] = 'mysql';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;
4.4.登录进行添加主机和监控

通过浏览器输入网址如:http://192.168.100.198/lepus,即可登录系统。.默认管理员账号密码admin/Lepusadmin登录后请修改管理员密码,增加普通账号。

4.5.新版本有权限问题,需要配置一下:

  步骤: cd  /opt/lampp/etc/extra

      vim  httpd-xampp.conf

    

找到#
<Directory "/opt/lampp/phpmyadmin">
AllowOverride AuthConfig Limit
Require localhost
ErrorDocument 403 /error/XAMPP_FORBIDDEN.html.var
</Directory>
将其中的"Require localhost"改为"Require all granted"
保存,重启Xampp即可~

 

      

      

五.进行redis监控,监控机(不是被监控机)要安装redis驱动:

 

$ wget http://cdn.lepus.cc/cdncache/software/redis-py-2.10.3.tar.gz
$ tar zxvf redis-py-2.10.3.tar.gz
$ cd redis-2.10.3/
$ python setup.py install

 

六.数据库慢查询配置:

天兔安装的数据库,数据库的配置文件为:/opt/lampp/etc/my.cnf

使用命令:mysql  --help   :可以显示数据库配置文件位置

天兔安装的数据库,数据库的mysql目录为:/opt/lampp/var/mysql/

 

  默认的慢查询日志:localhost-slow.log   就是在这个目录下,实际慢查询记录的日志文件为:/opt/lampp/var/mysql/localhost-slow.log

修改数据库配置文件配置慢查询:

  永久开启慢查询,需要修改/opt/lampp/etc/my.cnf文件

  cd /opt/lampp/etc/

  vim my.cnf

  原配置内容:

复制代码
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /opt/lampp/var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /opt/lampp/var/mysql/mysql.sock
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /opt/lampp/var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /opt/lampp/var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
user = mysql
port=3306
socket          = /opt/lampp/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Where do all the plugins live
plugin_dir = /opt/lampp/lib/mysql/plugin/

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
# 
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin deactivated by default since XAMPP 1.4.11
#log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin


# Point the following paths to different dedicated disks
#tmpdir         = /tmp/         
#log-update     = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Comment the following if you are using InnoDB tables
#skip-innodb
innodb_data_home_dir = /opt/lampp/var/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /opt/lampp/var/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
# Deprecated in 5.6
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
复制代码

 

 加上慢查询配置,在[mysqld]下添加配置:

      slow_query_log = ON
      slow_query_log_file = /opt/lampp/var/mysql/slow_query.log  (此处可以先不配置此项,使用默认即可)
      long_query_time = 1

  例如:
[mysqld]
slow_query_log = ON
slow_query_log_file = /opt/lampp/var/mysql/slow_query.log
long_query_time = 1

 

  修改后的文件:
复制代码
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /opt/lampp/var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /opt/lampp/var/mysql/mysql.sock
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /opt/lampp/var/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /opt/lampp/var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
user = mysql
port=3306
socket          = /opt/lampp/var/mysql/mysql.sock
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

#慢查询配置
slow_query_log = ON 
slow_query_log_file = /opt/lampp/var/mysql/slow_query.log
long_query_time = 1
#配置结束 # Where do all the plugins live plugin_dir = /opt/lampp/lib/mysql/plugin/ # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # log-bin deactivated by default since XAMPP 1.4.11 #log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended #log-bin=mysql-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 10000 # Comment the following if you are using InnoDB tables #skip-innodb innodb_data_home_dir = /opt/lampp/var/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /opt/lampp/var/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M # Deprecated in 5.6 #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
复制代码

 

 

步骤一:

  1.查看Mariadb数据库慢查询是否开启:

    mysql

    show variables like 'log_slow_queries';    (如果结果为ON则是开启了,如果为OFF则表示禁用了。)      ---mysql查询命令

    show variables like 'slow_query%';     ----Mariadb查询命令(可以查询慢查询开启状态及慢查询的日志名称)

    show variables like 'slow_query_log';   ----Mariadb查询命令(Mariadb慢查询的日志的变量名为slow_query_log)

  2.设置慢查询:

    set global log_slow_queries = on;               ---mysql设置开启慢查询日志命令

    set global slow_query_log = on;                  ---Mariadb设置开启慢查询日志命令

    flush hosts;   ---使配置生效命令

  3.查询慢查询开启状态:

    设置后重新使用  mysql 命令进入数据库查看慢查询设置

 

    mysql

    show variables like 'log_slow_queries';    (如果结果为ON则是开启了,如果为OFF则表示禁用了。)      ---mysql查询命令

    show variables like 'slow_query_log';   ----Mariadb查询命令(Mariadb慢查询的日志的变量名为slow_query_log)

    

 

  4.查看慢查询设置的时间:

    show global  variables like 'long_query_time';    (默认设置为10秒)

    show variables like 'long_query_%';

  5.设置慢查询时间:

    set global long_query_time =1;   (将慢查询时间设置为1秒)

  6.设置慢查询日志路径:

    set global slow_query_log_file='/opt/lampp/var/mysql/slow_query.log';     #此处一定要设置到文件slow_query.log,不能只设置到路径,否者报错

    

  6.测试慢查询:

    select sleep(2);     (查询2秒)

  

  

 

在监控机(即安装天兔服务的机子):

  which pt-query-digest

  显示:

  /usr/local/bin/pt-query-digest

  查看mysql客户端位置:

    which mysql

  出现如:

    /opt/lampp/bin/mysql

1.进入到 lepus_slowquery.sh文件所在的 目录下:

  cd /usr/local/lepus/client/mysql/

  cat  lepus_slowquery.sh

 

2.将 lepus服务器(及监控机)下的lepus_slowquery.sh (即/usr/local/lepus/client/mysql/lepus_slowquery.sh)文件copy到被监控机(不是监控机)下 的 /usr/local/sbin/ 目录下

  如果监控机和被监控机时同一个机子,可以使用命令:

  cp  lepus_slowquery.sh   /usr/local/sbin/

 

3.编辑 /usr/local/sbin/下的lepus_slowquery.sh:(被监控机上的配置)

  3.1.被监控机:需要安装percona-toolkit工具进行慢查询日志的统计

  使用命令安装:yum groupinstall perl*     (不安装解析会有问题)

 

  PT工具安装:

  下载地址:https://www.percona.com/downloads/percona-toolkit/2.2.20/deb/percona-toolkit_2.2.20-1.tar.gz

          https://www.percona.com/downloads/percona-toolkit/LATEST/     选择2.2.6版本

  wget http://www.percona.com/get/percona-toolkit.tar.gz

  下载成功后上传到centos7系统:

    rz   上传文件名              :上传即可

  然后,解压运行,命令:

     [root@HE1~]## yum -y install perl-IO-Socket-SSL
     [root@HE1~]## yum -y install perl-DBI
     [root@HE1~]## yum -y install perl-DBD-MySQL
     [root@HE1~]## yum -y install perl-Time-HiRes 
     [root@HE1~]# tar xvf percona-toolkit-2.2.12.tar.gz
     [root@HE1~]# cd percona-toolkit-2.2.12
     [root@HE1percona-toolkit-2.2.12]# perl Makefile.PL

            报错解决方案:
              安装缺少的包
              yum install perl-DBD-MySQL
              然后,重新编译
              perl Makefile.PL              
              Writing Makefile for percona-toolkit
              继续
              make && make test && make install  (直接用make && make install,不加make test也可以,如果加make test报错的话)

        报错解决方法:blog.sina.com.cn/s/blog_616b428f01018fry.html

     [root@HE1percona-toolkit-2.2.12]# make && make install

 

    下载 percona-toolkit-2.2.6-1.noarch.rpm文件后,上传到系统:

    安装命令:rpm -ivh percona-toolkit-2.2.6-1.noarch.rpm

 

  which pt-query-digest

  显示:

  /usr/local/bin/pt-query-digest

      

 

  查看mysql客户端位置:

    which mysql

  出现如:

    /opt/lampp/bin/mysql

 

  3.2.安装pt-query-diges完成后,配置被监控机上的/usr/local/sbin/lepus_slowquery.sh文件

  vim /usr/local/sbin/lepus_slowquery.sh

复制代码
#!/bin/bash
#****************************************************************#
# ScriptName: /usr/local/sbin/lepus_slowquery.sh
# Create Date: 2014-03-25 10:01
# Modify Date: 2014-03-25 10:01
#***************************************************************#

#config lepus database server    #lepus服务器
lepus_db_host="192.168.100.198"
lepus_db_port=3306
lepus_db_user="lepus_user"
lepus_db_password="123456"
lepus_db_database="lepus"

#config mysql server   #被监控的mysql
mysql_client="/opt/lampp/bin/mysql"
mysql_host="192.168.100.198"
mysql_port=3306
mysql_user="lepus_user"
mysql_password="123456"

#config slowqury
slowquery_dir="/opt/lampp/var/mysql/"   #慢查询日志根目录
slowquery_long_time=1   #慢查询时间设置
slowquery_file=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password  -e "show variables like 'slow_query_log_file'"|grep log|awk '{print $2}'`
pt_query_digest="/usr/local/bin/pt-query-digest"   #实际你本地的pt-query-digest路径

#config server_id
lepus_server_id=1   #打开天兔网址后,进入天兔web中的数据库配置中的数据库前的ID值

#collect mysql slowquery log into lepus database
$pt_query_digest --user=$lepus_db_user --password=$lepus_db_password --port=$lepus_db_port --review h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review  --history h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review_history  --no-report --limit=100% --filter=" \$event->{add_column} = length(\$event->{arg}) and \$event->{serverid}=$lepus_server_id " $slowquery_file > /tmp/lepus_slowquery.log

##### set a new slow query log ###########
tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`

#config mysql slowquery
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;"
$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log_file = '$tmp_log'; "

#delete log before 7 days
cd $slowquery_dir
/usr/bin/find ./ -name 'slowquery_*' -mtime +7|xargs rm -rf ;

####END####
复制代码

 

  :wq!:保存退出

  cd /usr/local/sbin/    :进入目录

  chmod +x lepus_slowquery.sh   :设置可执行权限,如果已经是可执行权限,可以不设置

  ./lepus_slowquery.sh   :运行一下,看看效果(手动运行查看是否正常运行:sh /usr/local/sbin/lepus_slowquery.sh

  3.3.被监控机配置定时任务,定时统计产生慢查询日志:

  查看所有定时任务: crontab -l

  添加定时任务: crontab -e

           */5 * * * * sh /usr/local/sbin/lepus_slowquery.sh > /dev/null 2>&1

                         :wq!:保存退出

  3.4. 查看被监控机 /opt/lampp/var/mysql/ (慢查询日志根目录)下是否有生成 类似于这种 slowquery_2019111516.log 的日志

    如果有,说明慢查询统计生效

 

4.在被监控机上开启并可以统计慢查询日志后,在监控机(即搭建天兔服务的机子)上 配置表空间分析:

  4.1.在 /usr/local/lepus/lepus.py 文件中,在monitor_mysql中添加(注意python代码格式):

    

job = Process(target = job_run, args = ('check_mysql_bigtable',int(frequency_monitor)*120)
joblist.append(job)
job.start()

 

  未添加时的代码如下;

复制代码
def main():
    logger.info("lepus controller start.")
    monitor = str(func.get_option('monitor'))
    monitor_mysql = str(func.get_option('monitor_mysql'))
    monitor_mongodb = str(func.get_option('monitor_mongodb'))
    monitor_oracle = str(func.get_option('monitor_oracle'))
    monitor_redis = str(func.get_option('monitor_redis'))
    monitor_os = str(func.get_option('monitor_os'))
    alarm = str(func.get_option('alarm'))
    frequency_monitor = func.get_option('frequency_monitor')
    frequency_monitor_alarm = int(frequency_monitor)+10

    joblist = []
    if monitor=="1":
        if monitor_mysql=="1":
            job = Process(target = job_run, args = ('check_mysql',frequency_monitor))
            joblist.append(job)
            job.start()


        time.sleep(3)
        if monitor_oracle=="1":
            job = Process(target = job_run, args = ('check_oracle',frequency_monitor))
            joblist.append(job)
            job.start()
复制代码

  添加后的代码如下:

复制代码
def main():
    logger.info("lepus controller start.")
    monitor = str(func.get_option('monitor'))
    monitor_mysql = str(func.get_option('monitor_mysql'))
    monitor_mongodb = str(func.get_option('monitor_mongodb'))
    monitor_oracle = str(func.get_option('monitor_oracle'))
    monitor_redis = str(func.get_option('monitor_redis'))
    monitor_os = str(func.get_option('monitor_os'))
    alarm = str(func.get_option('alarm'))
    frequency_monitor = func.get_option('frequency_monitor')
    frequency_monitor_alarm = int(frequency_monitor)+10

    joblist = []
    if monitor=="1":
        if monitor_mysql=="1":
            job = Process(target = job_run, args = ('check_mysql',frequency_monitor))
            joblist.append(job)
            job.start()
            job = Process(target = job_run, args = ('check_mysql_bigtable',int(frequency_monitor)*120)   #添加的部分
            joblist.append(job)                                                                          #添加的部分
            job.start()                                                                                  #添加的部分

        time.sleep(3)
        if monitor_oracle=="1":
            job = Process(target = job_run, args = ('check_oracle',frequency_monitor))
            joblist.append(job)
            job.start()
复制代码

  4.2.监控机(即搭建天兔服务的机子)建立定时任务,定期采集数据,命令:

    crontab -e

    */5 * * * * python /usr/local/lepus/check_mysql_bigtable.py

    :wq!:保存退出

 

    查看定时任务命令: crontab -l

    

      

  

    

  

  

  

 

 
 
 
 
 
 
 
参考文档:https://www.jianshu.com/p/ff62001d9f1c
     https://www.cnblogs.com/huhyoung/p/10965105.html
       https://www.cnblogs.com/hankyoon/p/11011769.html
       http://www.mamicode.com/info-detail-1675184.html
     pt工具安装:https://www.cnblogs.com/itfenqing/p/6131732.html
             http://www.mamicode.com/info-detail-2031733.html
             https://www.cnblogs.com/adba/p/5279551.html
        ·   https://www.jianshu.com/p/58951f6201a7
     Mariadb开启慢查询:https://blog.csdn.net/zhezhebie/article/details/74991975
              https://blog.csdn.net/hj12312/article/details/87951922
posted on   大话人生  阅读(507)  评论(0编辑  收藏  举报
编辑推荐:
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· AI与.NET技术实操系列:向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
阅读排行:
· TypeScript + Deepseek 打造卜卦网站:技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗?
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维:从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句:使用策略模式优化代码结构
点击右上角即可分享
微信分享提示