deploy myawr step by step
怎么快速部署一套myawr，最少资源配置： linux server一台，DBI，DBD，如果没有mysql的话也要mysql安装包。
下面介绍一下怎么部署，这个环境是Oracle Linux Server release 5.8，没有mysql，同时准备把myawr的采集对象和存储数据都放在这台上面，正常情况下采集对象上只需安装perl-dbd-mysql，然后部署监控脚本就行了，他只对所监控的mysql数据库进行查询，不会修改任何东西：
Myawr的架构图:
我的资源列表
[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
以下是安装步骤(下面的操作我是在同一个虚拟机上进行的操作)：
[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 -versionThis 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
最简单的安装法就是使用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信息。
cd DBI-1.615 perl Makefile.PL make make installmkdir /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(namevarchar(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.
执行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);
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
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