deploy myawr step by step - noodba/myawr GitHub Wiki

怎么快速部署一套myawr,最少资源配置: linux server一台,DBI,DBD,如果没有mysql的话也要mysql安装包。

下面介绍一下怎么部署,这个环境是Oracle Linux Server release 5.8,没有mysql,同时准备把myawr的采集对象和存储数据都放在这台上面,正常情况下采集对象上只需安装perl-dbd-mysql,然后部署监控脚本就行了,他只对所监控的mysql数据库进行查询,不会修改任何东西:

Myawr的架构图: myawr_archit1

myawr_archit2

我的资源列表

[root@oel58 myawr]# ls -al
total 75848
drwxr-xr-x  2 root root     4096 Jul  7 17:01 .
drwxr-x--- 18 root root     4096 Jul  7 17:01 ..
-rw-r--r--  1 root root   133427 Jul  7 17:01 DBD-mysql-4.018.tar.gz
-rw-r--r--  1 root root   571379 Jul  7 17:01 DBI-1.615.tar.gz
-rw-r--r--  1 root root    32485 Jul  7 17:01 myawr.pl
-rw-r--r--  1 root root    64206 Jul  7 17:01 myawrrpt.pl
-rw-r--r--  1 root root    35706 Jul  7 17:01 myawrsrpt.pl
-rw-r--r--  1 root root 17899890 Jul  7 17:01 MySQL-client-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root  3606645 Jul  7 17:01 MySQL-devel-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root 54638727 Jul  7 17:01 MySQL-server-5.5.29-2.rhel5.i386.rpm
-rw-r--r--  1 root root   549438 Jul  7 17:01 pt-query-digest

以下是安装步骤(下面的操作我是在同一个虚拟机上进行的操作):

1 环境检查:

[root@oel58 ~]# rpm -qa | grep -i mysql
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbd
[root@oel58 ~]# 
[root@oel58 ~]# rpm -qa | grep -i dbi
[root@oel58 ~]# 
[root@oel58 ~]# perl -version

This is perl, v5.8.8 built for i386-linux-thread-multi

Copyright 1987-2006, Larry Wall

[root@oel58 ~]# uname -a Linux oel58 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:22:40 EST 2012 i686 i686 i386 GNU/Linux [root@oel58 ~]# [root@oel58 ~]# [root@oel58 ~]# getconf LONG_BIT 32

2 安装mysql

最简单的安装法就是使用rpm安装,这个可以在
http://downloads.mysql.com/archives.php?p=mysql-5.5上下载。

[root@oel58 ~]# rpm -ivh MySQL-server-5.5.29-2.rhel5.i386.rpm Preparing... ########################################### [100%] 1:MySQL-server ########################################### [100%]

[root@oel58 ~]# [root@oel58 ~]# [root@oel58 ~]# rpm -ivh MySQL-client-5.5.29-2.rhel5.i386.rpm Preparing... ########################################### [100%] 1:MySQL-client ########################################### [100%]

[root@oel58 ~]# rpm -ivh MySQL-devel-5.5.29-2.rhel5.i386.rpm Preparing... ########################################### [100%] 1:MySQL-devel ########################################### [100%] [root@oel58 ~]# service mysql start Starting MySQL... [ OK ] [root@oel58 ~]# /usr/bin/mysqladmin -u root password '123456'

[root@oel58 ~]# cat /etc/my.cnf [mysqld] performance_schema log_bin=on innodb_file_per_table=1 slow_query_log=1 long_query_time=1 log_queries_not_using_indexes=0

MySQL 5.5新增PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。包括等待的信息,事件汇总信息等。 myawr不是强制要求开启PERFORMANCE_SCHEMA的,开启的话,他会采集一些数据,在展示时会有top events信息。

3 安装mysql dbd(依赖dbi)

cd DBI-1.615
perl Makefile.PL 
make
make install

mkdir /tmp/mysqldbd-install cp /usr/lib/mysql/*.a /tmp/mysqldbd-install cd DBD-mysql-4.018 perl Makefile.PL --libs="-L/tmp/mysqldbd-install -lmysqlclient" make make test make install

检查安装模块:

[root@oel58 aaa]# cat listmodle.pl #!usr/bin/perl use strict; use warnings;

use ExtUtils::Installed;

my $inst = ExtUtils::Installed->new(); print join "\n", $inst->modules();

[root@oel58 ~]# perl listmodle.pl DBD::mysql DBI Perl

检查DBD的可用性:

CREATE TABLE test ( name varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test (name) VALUES ('hello world');

[root@oel58 ~]# cat dbd.pl #!/usr/bin/perl

use DBI;

my $dbh = DBI->connect( "DBI:mysql:database=test;host=localhost", "root", "123456", { 'RaiseError' => 1 } ); #my $rows = $dbh->do("INSERT INTO test (name) VALUES ('hello world')"); my $query = $dbh->prepare("SELECT name FROM test"); $query->execute();

while ( $ary = $query->fetchrow_arrayref() ) { print ( $ary->[0] . "\n"); }

如果mysql是自己编译安装或者二进制安装的,可以直接: 下面这个因为我是rpm安装的,有冲突

[root@oel58 ~]# yum install perl-DBD-MySQL Loaded plugins: rhnplugin, security This system is not registered with ULN. ULN support will be disabled. Setting up Install Process Resolving Dependencies --> Running transaction check ---> Package perl-DBD-MySQL.i386 0:3.0007-2.el5 set to be updated --> Processing Dependency: perl(DBI) for package: perl-DBD-MySQL --> Processing Dependency: libmysqlclient.so.15(libmysqlclient_15) for package: perl-DBD-MySQL --> Processing Dependency: libmysqlclient.so.15 for package: perl-DBD-MySQL --> Running transaction check ---> Package mysql.i386 0:5.0.95-1.el5_7.1 set to be updated ---> Package perl-DBI.i386 0:1.52-2.el5 set to be updated --> Processing Conflict: MySQL-server conflicts mysql --> Processing Conflict: MySQL-client conflicts mysql --> Processing Conflict: mysql conflicts MySQL --> Finished Dependency Resolution MySQL-client-5.5.29-2.rhel5.i386 from installed has depsolving problems --> MySQL-client conflicts with mysql mysql-5.0.95-1.el5_7.1.i386 from el5_latest has depsolving problems --> mysql conflicts with MySQL-server MySQL-server-5.5.29-2.rhel5.i386 from installed has depsolving problems --> MySQL-server conflicts with mysql Error: MySQL-server conflicts with mysql Error: mysql conflicts with MySQL-server Error: MySQL-client conflicts with mysql You could try using --skip-broken to work around the problem You could try running: package-cleanup --problems package-cleanup --dupes rpm -Va --nofiles --nodigest The program package-cleanup is found in the yum-utils package.

4 初始化数据库(注意:这个是在你要保存信息的mysql数据库上部署)

执行myawr.sql
增加配置信息:
INSERT INTO `myawr_host`(id,host_name,ip_addr,port,db_role,version, running_thread_threshold,times_per_hour) VALUES (6, 'db2.11', '192.168.2.11', 3306, 'master', '5.5.27',10000,0);

5 job(注意:这个是在你想要采集信息的mysql数据库上部署):

grant all on *.* to 'superuser'@'localhost' identified by "111111";

在把下列脚本部署成job前,可以先手工运行一下:

          • perl /root/myawr/myawr.pl -u superuser -p 111111 -lh localhost -P 3306 -tu myuser -tp 111111 -TP 3306 -th 192.168.137.9 -n eth0 -d sda3 -I 6 >> /root/myawr/myawr_pl.log 2>&1

下面的脚本可以模拟有lock wait的情况: use test; create table txtest (id int primary key , name varchar(200)) engine=innodb default charset = utf8 ;

insert into txtest values(1, 'xxxxxxxxxxxxxxxxxxxx'); insert into txtest values(2, 'zzzzzzzzzzzzzzzzzzzzzzz'); insert into txtest values(3, 'sssssssssssssssssssss');

commit;

###事物一 start transaction; update txtest set name='ddddddddd' where id =1 ;

update txtest set name='wwwwwwwwwwwddddddddd' where id =2 ;

###事物二 start transaction; update txtest set name='kkkkkkkkkkkkkkkkkk' where id =1 ;

###事物二 select sleep(300);

在把下列脚本部署成job前,可以先手工运行一下: ./pt-query-digest --user=myuser --password=111111 --review h=192.168.137.9,D=myawr,t=myawr_query_review --review-history h=192.168.137.9,D=myawr,t=myawr_query_review_history --no-report --limit=100% --filter=" $event->{add_column} = length($event->{arg}) and $event->{hostid}=6" /var/lib/mysql/oel58-slow.log

6 生成report:

perl myawrrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6
perl myawrsrpt.pl -u myuser -p 111111 -P 3306 -lh 192.168.137.9 -I 6 -s 29
⚠️ **GitHub.com Fallback** ⚠️