【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 start Starting httpd: [ OK ] [root@wallet04 ~]# service httpd status httpd (pid 1424) is running... [root@wallet04 ~]# vi /etc/php.ini date.timezone = Asia/Shanghai [root@wallet04 ~]# php -v PHP 5.6.40 (cli) (built: Jan 12 2019 09:19:57) Copyright (c) 1997-2016 The PHP Group Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies 安装MySQL 5.6 https://blog.51cto.com/13598811/2349351 [root@wallet04 ~]# python -V Python 2.6.6 [root@wallet04 ~]# yum install -y python-pip [root@wallet04 ~]# pip -V pip 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.1234 mysql> 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 lepus Database changed mysql> source lepus_table.sql mysql> source lepus_data.sql mysql> 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=3306 user="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 start lepus server start success! [root@wallet04 lepus]# lepus status lepus server is running... [root@wallet04 ~]# vim /var/www/html/application/controllers/lp_mysql.php 337行 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 --version pt-query-digest 3.1.0 [root@wallet01 ~]# cd /usr/local/sbin [root@wallet01 sbin]# vi lepus_slowquery.sh #!/bin/bash #config lepus database server lepus_db_host="192.168.1.204" lepus_db_port=3306 lepus_db_user="lepus" lepus_db_password="lepus" lepus_db_database="lepus" #config mysql server mysql_client=`which mysql` mysql_host="192.168.1.201" mysql_port=3306 mysql_user="root" mysql_password="abcd.1234" #config slowqury slowquery_dir="/var/lib/mysql/" slowquery_long_time=2 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}'` #config server_id lepus_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 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 ; [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_profile export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export PATH=$PATH:$ORACLE_HOME/bin export 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.py Oracle python drivier is ok!
分享标题:【MySQL】数据库慢查询日志分析--Lepus
标题来源:http://ybzwz.com/article/pejdgh.html