perl source mysql check script (모니터링)
아직 항목은 많이 만들지 못했습니다. status 항목과 variable 항목을 비교 하여 자동으로
값을 체크해서 확인 하는 방법으로 만들었습니다. 아직 항목을 다 완성하지 못하였으나
아래 와 같은 형식으로 더 붙 일수 있습니다.
현재 검사 항목은
1.초당 평균 쿼리
2.평균 슬로우 쿼리 발생 빈도
3.평균 연결 중지
4.myism 히트율
5.innodb 히트율
[작동 방법 설명]
[root@maria .Admin]# cat mysqlcheck.pl
#!/usr/bin/perl
use DBI;
print 'Enter host for connect: '; chomp(my $host = <STDIN>);
print 'Enter user for connect: '; chomp(my $user = <STDIN>);
print "Enter passwd for : "; chomp(my $pw = <STDIN>);
print "socket for : "; chomp(my $socket = <STDIN>);
my $start= 'mysql';
my $dbh = DBI->connect("DBI:mysql:$start;host=$host;mysql_socket=$socket", $user, $pw,{ RaiseError => 1 });
my $qUseTablespace='SELECT table_schema, SUM((data_length+index_length)/1024/1024) MB FROM information_schema.tables GROUP BY 1';
my $sth=$dbh->prepare($qUseTablespace);
$sth->execute;
print ".............Mysql tablespace Use ............. \n";
while (my $aref = $sth->fetchrow_arrayref) {
print "\n". $aref->[0] .":\t\t". (sprintf "%.2f",($aref->[1])) . "(MB)";
}
my $sth=$dbh->prepare(q{SHOW variables});
$sth->execute;
my %hash;
while( my( $item, $quantity ) = $sth->fetchrow_array() ) {
$hash{ $item } = $quantity;
}
#### Get variable value ###################
$max_conn = sprintf "%.3f",($hash{'max_connections'}) ;
my $sth=$dbh->prepare(q{SHOW STATUS});
$sth->execute;
my %hash;
while( my( $item, $quantity ) = $sth->fetchrow_array() ) {
$hash{ $item } = $quantity;
}
#### Get status value ###################
$QPS = sprintf "%.3f",($hash{'Queries'} / $hash{'Uptime'}) ; # QPS (Queries) / Uptime
$SQuery = sprintf "%.3f",($hash{'Slow_queries'} / $hash{'Uptime'}) ; # Slow_queries / Uptime
$AClient = sprintf "%.3f",($hash{'Aborted_clients'} / $hash{'Uptime'}) ; # Aborted_clients / Uptime
$InHitration = sprintf "%.2f",($hash{'Innodb_buffer_pool_reads'} / $hash{'Innodb_buffer_pool_read_requests'}) *100 ; # innodb hit(%)
#$MyHitration = sprintf "%.2f",($hash{'Key_reads'} / $hash{'Key_read_requests'})*100 ; # myism hit(%)
$AbotConn = sprintf "%.2f",($hash{'Aborted_connects'} / $hash{'Connections'}) * 100 ; # Abort connect(%)
$MaxUsedConn = sprintf "%.2f",($hash{'Max_used_connections'}) ; # max connection
$Lock_wait = sprintf "%.2f", ($hash{'Table_locks_waited'} / ($hash{'Table_locks_immediate'} + $hash{'Table_locks_waited'})) *100 ; # lock wait
$Created_tmp = sprintf "%.2f", ($hash{'Created_tmp_disk_tables'} / ($hash{'Created_tmp_disk_tables'} + $hash{'Created_tmp_tables'})) *100 ; # Create_tmp
$Deadlocks = sprintf "%.2f", $hash{'Innodb_deadlocks'} ;
#### Status value Print ##################
print "\n";
print "\n";
print ".............Mysql Monitor starting ............ \n";
print "\nQueries per second avg : $QPS "; # QPS
print "\nSlow_queries avg : $SQuery "; if ($SQuery > 1 ) { print " CHECKED "; } # Slow_queries
print "\nAborted_clients avg : $AClient "; # Aborted_clients
print "\nInnodb hitration(%) : $InHitration(%) "; if ($InHitration < 50) { print " CHECKED "; } # innodb hitration
print "\nMyIsm hitration(%) : $MyHitration(%) "; if ($MyHitration < 50) { print " CHECKED "; } # Myisam hitration
print "\nAborted_Connect(%) : $AbotConn(%) "; if ($$AbotConn > 10) { print " CHECKED "; } # abort connect(%)
#print "\nMax_Connected : $MaxUsedConn "; if ($MaxUsedConn > $max_conn - 100) { print " CHECKED "; } # max connect(%)
print "\nMax_Connected : $MaxUsedConn(Status) : $max_conn(Limit) "; if ($MaxUsedConn > $max_conn - 100) { print " CHECKED "; } # max connect(%)
print "\nLock_wait(%) : $Lock_wait(%) "; if ($Lock_wait > 90) { print " CHECKED "; } # lock wait(%)
print "\nCreated_tmp_disk(%) : $Created_tmp(%) "; if ($Created_tmp > 90) { print " CHECKED "; } # create_tmp_disk
print "\nInnodb_deadlocks avg : $Deadlocks ";
print "\n";
$dbh->disconnect;
사업자 정보 표시
(주)블루원 | 김홍태 | 서울특별시 용산구 원효로 4가 135 금홍 2빌딩 | 사업자 등록번호 : 106-86-76684 | TEL : 02-3272-7200 | Mail : support_ora@blueone.co.kr | 통신판매신고번호 : 호 | 사이버몰의 이용약관 바로가기
'DB - MySQL' 카테고리의 다른 글
| [TD-MYSQL] maria DB 10 설치 (Azure ppt) (0) | 2017.06.23 |
|---|---|
| MySQL 보안 설정 - #2 audit 설정 (0) | 2017.06.23 |
| MySQL 보안 설정 - #1 계정권한 (0) | 2017.05.29 |
| MySQL 명령어 소개 (0) | 2017.05.12 |
| maria DB 10 설치 (text) (0) | 2017.03.23 |