【MySQL】数据库慢查询日志分析--Lepus

[root@wallet04 ~]# wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm[root@wallet04 ~]# rpm -ivh epel-release-latest-6.noarch.rpm[root@wallet04 ~]# rpm -ivh https://mirror.webtatic.com/yum/el6/latest.rpm [root@wallet04 ~]# yum install -y httpd php56w.x86_64 php56w-cli.x86_64 php56w-common.x86_64 php56w-devel.x86_64 \php56w-gd.x86_64 php56w-ldap.x86_64 php56w-mbstring.x86_64 php56w-bcmath.x86_64 php56w-xml.x86_64 \php56w-mysqlnd.x86_64 php56w-pdo.x86_64[root@wallet04 ~]# vi /etc/httpd/conf/httpd.conf ServerName 192.168.1.204:80[root@wallet04 ~]# service httpd startStarting httpd:                                            [  OK  ][root@wallet04 ~]# service httpd statushttpd (pid  1424) is running...[root@wallet04 ~]# vi /etc/php.ini date.timezone = Asia/Shanghai[root@wallet04 ~]# php -vPHP 5.6.40 (cli) (built: Jan 12 2019 09:19:57) Copyright (c) 1997-2016 The PHP GroupZend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies安装MySQL 5.6https://blog.51cto.com/13598811/2349351[root@wallet04 ~]# python -VPython 2.6.6[root@wallet04 ~]# yum install -y python-pip[root@wallet04 ~]# pip -Vpip 7.1.0 from /usr/lib/python2.6/site-packages (python 2.6)[root@wallet04 ~]# pip install MySQL-python[root@wallet04 ~]# cd lepus/python[root@wallet04 python]# python test_driver_mysql.py MySQL python drivier is ok![root@wallet04 ~]# cd lepus/sql[root@wallet04 sql]# mysql -u root -pabcd.1234mysql> create database lepus default character set utf8;Query OK, 1 row affected (0.00 sec)mysql> grant all on lepus.* to 'lepus'@'%' identified by 'lepus';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> use lepusDatabase changedmysql> source lepus_table.sqlmysql> source lepus_data.sqlmysql> alter table mysql_slow_query_review modify checksum varchar(100) not null;Query OK, 0 rows affected (0.95 sec)mysql> alter table mysql_slow_query_review_history modify checksum varchar(100) not null; Query OK, 0 rows affected (0.73 sec)[root@wallet04 ~]# cd lepus/python[root@wallet04 python]# chmod +x install.sh[root@wallet04 python]# ./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.[root@wallet04 ~]# cd /usr/local/lepus[root@wallet04 lepus]# vim etc/config.ini###监控机MySQL数据库连接地址###[monitor_server]host="localhost"port=3306user="lepus"passwd="lepus"dbname="lepus"[root@wallet04 ~]# cd lepus[root@wallet04 lepus]# cp -rf php/* /var/www/html/[root@wallet04 ~]# cd /var/www/html/application/config[root@wallet04 config]# vim database.php $db['default']['hostname'] = 'localhost';$db['default']['port']     = '3306';$db['default']['username'] = 'lepus';$db['default']['password'] = 'lepus';$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;[root@wallet04 ~]# cd /usr/local/lepus[root@wallet04 lepus]# lepus startlepus server start success![root@wallet04 lepus]# lepus statuslepus server is running...[root@wallet04 ~]# vim /var/www/html/application/controllers/lp_mysql.php337行parent::check_privilege('mysql/replication')修改为parent::check_privilege()

[root@wallet01 ~]# wget https://repo.percona.com/yum/percona-release-latest.noarch.rpm[root@wallet01 ~]# rpm -ivh percona-release-latest.noarch.rpm[root@wallet01 ~]# yum install -y percona-toolkit[root@wallet01 ~]# pt-query-digest --versionpt-query-digest 3.1.0[root@wallet01 ~]# cd /usr/local/sbin[root@wallet01 sbin]# vi lepus_slowquery.sh#!/bin/bash#config lepus database serverlepus_db_host="192.168.1.204"lepus_db_port=3306lepus_db_user="lepus"lepus_db_password="lepus"lepus_db_database="lepus"#config mysql servermysql_client=`which mysql`mysql_host="192.168.1.201"mysql_port=3306mysql_user="root"mysql_password="abcd.1234"#config slowquryslowquery_dir="/var/lib/mysql/"slowquery_long_time=2slowquery_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}'`#config server_idlepus_server_id=274#collect mysql slowquery log into lepus database/usr/local/bin/pt-query-digest --user=$lepus_db_user --password=$lepus_db_password --port=$lepus_db_port --charset=utf8 --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#set a new slow query logtmp_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 dayscd $slowquery_dir/usr/bin/find ./ -name 'slowquery_*' -mtime +7|xargs rm -rf ;[root@wallet01 sbin]# chmod a+x lepus_slowquery.sh[root@wallet01 ~]# crontab -l*/5 * * * * sh /usr/local/sbin/lepus_slowquery.sh > /dev/null 2>&1

[root@wallet01 ~]# ls -l oracle-instantclient11.2*-rw-r--r-- 1 root root 59865008 Dec  3 11:06 oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm-rw-r--r-- 1 root root   610222 Dec  3 11:04 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm-rw-r--r-- 1 root root   828333 Dec  3 11:04 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm[root@wallet01 ~]# rpm -ivh oracle-instantclient11.2-*[root@wallet01 ~]# vim /root/.bash_profileexport ORACLE_HOME=/usr/lib/oracle/11.2/client64export PATH=$PATH:$ORACLE_HOME/binexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/usr/local/lib[root@wallet04 ~]# source /root/.bash_profile[root@wallet01 ~]# vim /etc/ld.so.conf include ld.so.conf.d/*.conf/usr/lib/oracle/11.2/client64/lib[root@wallet01 ~]# ldconfig[root@wallet01 ~]# pip install cx-Oracle[root@wallet01 ~]# cd /usr/local/lepus[root@wallet01 lepus]# python test_driver_oracle.pyOracle python drivier is ok!