[MySQL] Database slow query log analysis-- 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 vi 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 install 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 (python2.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### Monitor MySQL Database connection address # # [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 [' 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') is changed to 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 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 Dempster pushroom database review-- history mysterious pushroom database review-- history mysterious pushroom database review-- length length (\ $event- > {arg}) and\ $event- > {serverid} = $lepus_server_id "$slowquery_file#set a new slowquery 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 aquix lepus_ query.sh [root @ wallet01 ~] # crontab-lump 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.x86mm 64.rwkw Dec 3 11:06 oracle-instantclient11.2-basic-11.2.0.4.0-1 root root 610222 Dec 3 11:04 oracle-instantclient11.2-devel-11.2.0.4.0-1.x86y64.rpm Mustang RW Mustang-1 root root 828333 Dec 3 11 04 oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x8664.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!