### 쓴것 : PHP 명령모드 스크립트를 이용해서 Mysql 테이블 점검
### 쓴이 : 독거청년 nonots@hanmail.net
### 쓴때 : 2012-03-16


1. 테스트 환경
- OS  : 리눅스 CentOS 4,5,6
- Mysql : 4.x, 5.x
- Php : 5.x

2. 목적
  리눅스 서버의 쉘모드에서 실행되는 php 스크립트를 이용해서
  mysql 각 테이블들의 상태를 점검한다.
  상태를 점검 한다는 말은,
  테이블의 크기, Row 수, 오버헤드 를 많은(큰) 순으로 출력해서
  테이블이 최적화 안되어 공간활용율이 떨어지거나
  스팸 공격으로 쓸모없는 Row 가 엄청 쌓이거나 한 테이블을
  찾아서 보여준다.
  그냥 목록을 보여주기만한다 !!!
..
  만약  오버헤드가 많다면 optimize table 명령어로 최적화하고
  만약 스팸이 엄청 쌓인거라면 게시물을 삭제하거나 접근권한등을
  수정한다.
  ..
  이 스크립트는 엄청 많은 DB 와 테이블을 운영하는 서버에
  유용할수 있다.
  만약 얼마 안되는 테이블을 운영중이라면 phpmyadmin 모드에서
  간단하게 사용가능하는게 낫다.
  ..
  이건 단순히 show table status; 결과 값을 통해 출력된
  데이타를 처리했을 뿐이다.


3. 방법
  아래 첨부한 소스 시작, 끝 부분을 복사해서
  리눅스 서버의 적당한 곳에 파일로 저장한후 실행 퍼미션을 준다.
root 권한으로만 접근가능한 곳에 두는 것이 좋다.
  예를 들면
  /root/bin/check_mysql_disk.sh 라는 이름으로 저장했다면
  chmod 755 /root/bin/check_mysql_disk.sh 로 실행권한 준다.
  ..
  그런후 각 사이트에 맞게 상단 부분에 수정한다.
  예를 들면 첫줄
  #!/usr/bin/php 와 같이 된 부분을
  어떤 서버는 컴파일해서 php 명령어 위치가 /usr/local/bin/php 등과 같이
  되어 있다면 #!/usr/local/bin/php 로 수정한다.
  ..
  그리고 mysql root 비번을 직접 적어서 사용하려면
>> //$mysql_root_pwd="kqka##091";//만약 mysql root 비번을 직접 넣는다면 이 라인 사용
이 라인을 주석풀고
mysql_root 비번이 별도 파일 $r_file 에 적어 넣었다면
>> $r_file = "/root/bin/.mysqlinfo"; // mysql root 비밀번호 달랑 적힌 파일
>> $mysql_root_pwd=`cat $r_file`; // 위 달랑 적힌 파일 불러와 사용하려면 이 라인 사용
와 같이 불러오게 한다.


4. 실행예

일단 인수 없이 그냥 실행하면 1,2,3 중에 하나를 인수로 넘기라고 나온다.
  여기서 오버헤드 큰 파일들을 보려면 1 을 넣는다


[root@mywww bin]# ./check_mysql_disk.sh

## 사용법 :: 명령어 인수로 1,2,3 중에 하나를 넘겨야 함.
..
오버헤드 많은 테이블 순서로 보려면 : ./check_mysql_disk.sh 1
데이타 크기 큰 테이블 순서로 보려면 : ./check_mysql_disk.sh 2
데이타 Row 많은 테이블 순서로 보려면 : ./check_mysql_disk.sh 3

[root@mywww bin]# ./check_mysql_disk.sh 1

### 오버헤드 많은 순서로 출력 ###

오버헤드: 25,217,520 전체크기: 25,508,732 Row수: 171 ::: [jura_db : zetyx_board_j2]
오버헤드: 21,730,568 전체크기: 22,058,496 Row수: 90 ::: [jc_db : zetyx_board_propose_01]
오버헤드: 11,366,084 전체크기: 11,516,944 Row수: 77 ::: [bang_db : g4_write_pds]
...


위에서 jura_db 라는 DB 의 zetyz_board_12 라는 테이블이 오버헤드가 25 메가가 넘는다.
    이걸 최적화 하려면 optimize table 명령어로 최적화 하면 된다.


5.  마무리
약간이라도 도움이 되길..
안되면 될 때까지..


== 자르기 시작 ================================
#!/usr/bin/php

<?php

error_reporting(E_ALL ^ E_NOTICE);

//$mysql_root_pwd="kqka##091";//만약 mysql root 비번을 직접 넣는다면 이 라인 사용
$r_file = "/root/bin/.mysqlinfo"; // mysql root 비밀번호 달랑 적힌 파일
$mysql_root_pwd=`cat $r_file`; // 위 달랑 적힌 파일 불러와 사용하려면 이 라인 사용

$data_free_limit = 1000000; // 테이블별 오버헤드 출력기준(byte)
$data_size_limit = 10000000; // 테이블별 데이타 크기 출력 기준(byte)
$data_rows_limit = 100000; // 테이블별  데이타 갯수(row) 출력 기준

if ( $_SERVER['argv'][1] == ''){
die("
## 사용법 :: 명령어 인수로 1,2,3 중에 하나를 넘겨야 함.
..
오버헤드 많은 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 1
데이타 크기 큰 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 2
데이타 Row 많은 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 3
\n");
}

if ( $_SERVER['argv'][1] == '2'){
$coln = 'total';
$title = "데이타 크기 순서로 출력";
}else if ( $_SERVER['argv'][1] == '3'){
$coln = 'Rows';
$title = "데이타 Row 많은 순서로 출력";
}else{
$coln = 'Data_free';
$title = "오버헤드 많은 순서로 출력";
}

echo "### $title ###\n\n";

//print_r(array_sort($people, 'age', SORT_DESC)); // Sort by oldest first
function array_sort($array, $on, $order=SORT_ASC) {
    $new_array = array();
    $sortable_array = array();

    if (count($array) > 0) {
        foreach ($array as $k => $v) {
            if (is_array($v)) {
                foreach ($v as $k2 => $v2) {
                    if ($k2 == $on) {
                        $sortable_array[$k] = $v2;
                    }
                }
            } else {
                $sortable_array[$k] = $v;
            }
        }

        switch ($order) {
            case SORT_ASC:
                asort($sortable_array);
            break;
            case SORT_DESC:
                arsort($sortable_array);
            break;
        }

        foreach ($sortable_array as $k => $v) {
            $new_array[$k] = $array[$k];
        }
    }
    return $new_array;
}

$conn = mysql_connect("localhost","root",trim($mysql_root_pwd));
mysql_select_db("mysql");

$result = mysql_query("select Db from db");
while($arr = mysql_fetch_array($result)) {
    $dbarr[] = $arr['Db'];
}

foreach($dbarr as $k){
    #$result = mysql_query("SHOW TABLE STATUS FROM $k where Data_free > 100000 or Data_length > 100000;");
    #$result = mysql_query("SHOW TABLE STATUS FROM $k where Data_free > $data_free_limit;");
    $result = mysql_query("SHOW TABLE STATUS FROM $k");

if ( $result ){
while($array = mysql_fetch_array($result,MYSQL_ASSOC)){
$array['total'] = $array[Data_length]+$array[Index_length];
$array['Dbname'] = $k;

if ( $coln == 'Rows' ){
if ( $array[Rows] > $data_rows_limit ) $dinfo[] = $array;
}elseif ( $coln == 'total' ){
if ( $array[total] > $data_size_limit ) $dinfo[] = $array;
}else{
if ( $array[Data_free] > $data_free_limit ) $dinfo[] = $array;
}
}
}
}

$dinfo2=array_sort($dinfo,$coln,SORT_DESC);

foreach($dinfo2 as  $vv){
$total=$data_free=$rows='';
$total = number_format($vv['total']);
$data_free = number_format($vv['Data_free']);
$rows = number_format($vv['Rows']);
echo "오버헤드: ".$data_free."\t전체크기: ".$total."\tRow수: ".$rows."\t:::\t";
echo "[".$vv['Dbname']." : ".$vv[Name]."] \n\n";
}
mysql_close();
?>
== 자르기 끝 ================================

### 쓴것 : PHP 명령모드 스크립트를 이용해서 Mysql 테이블 점검 

### 쓴이 : 독거청년 nonots@hanmail.net 

### 쓴때 : 2012-03-16 





1. 테스트 환경 

- OS  : 리눅스 CentOS 4,5,6 

- Mysql : 4.x, 5.x 

- Php : 5.x 



2. 목적 

  리눅스 서버의 쉘모드에서 실행되는 php 스크립트를 이용해서 

  mysql 각 테이블들의 상태를 점검한다. 

  상태를 점검 한다는 말은, 

  테이블의 크기, Row 수, 오버헤드 를 많은(큰) 순으로 출력해서 

  테이블이 최적화 안되어 공간활용율이 떨어지거나 

  스팸 공격으로 쓸모없는 Row 가 엄청 쌓이거나 한 테이블을 

  찾아서 보여준다. 

  그냥 목록을 보여주기만한다 !!! 

.. 

  만약  오버헤드가 많다면 optimize table 명령어로 최적화하고 

  만약 스팸이 엄청 쌓인거라면 게시물을 삭제하거나 접근권한등을 

  수정한다. 

  .. 

  이 스크립트는 엄청 많은 DB 와 테이블을 운영하는 서버에 

  유용할수 있다. 

  만약 얼마 안되는 테이블을 운영중이라면 phpmyadmin 모드에서 

  간단하게 사용가능하는게 낫다. 

  .. 

  이건 단순히 show table status; 결과 값을 통해 출력된 

  데이타를 처리했을 뿐이다. 





3. 방법 

  아래 첨부한 소스 시작, 끝 부분을 복사해서 

  리눅스 서버의 적당한 곳에 파일로 저장한후 실행 퍼미션을 준다. 

root 권한으로만 접근가능한 곳에 두는 것이 좋다. 

  예를 들면 

  /root/bin/check_mysql_disk.sh 라는 이름으로 저장했다면 

  chmod 755 /root/bin/check_mysql_disk.sh 로 실행권한 준다. 

  .. 

  그런후 각 사이트에 맞게 상단 부분에 수정한다. 

  예를 들면 첫줄 

  #!/usr/bin/php 와 같이 된 부분을 

  어떤 서버는 컴파일해서 php 명령어 위치가 /usr/local/bin/php 등과 같이 

  되어 있다면 #!/usr/local/bin/php 로 수정한다. 

  .. 

  그리고 mysql root 비번을 직접 적어서 사용하려면 

>> //$mysql_root_pwd="kqka##091";//만약 mysql root 비번을 직접 넣는다면 이 라인 사용 

이 라인을 주석풀고 

mysql_root 비번이 별도 파일 $r_file 에 적어 넣었다면 

>> $r_file = "/root/bin/.mysqlinfo"; // mysql root 비밀번호 달랑 적힌 파일 

>> $mysql_root_pwd=`cat $r_file`; // 위 달랑 적힌 파일 불러와 사용하려면 이 라인 사용 

와 같이 불러오게 한다. 





4. 실행예 



일단 인수 없이 그냥 실행하면 1,2,3 중에 하나를 인수로 넘기라고 나온다. 

  여기서 오버헤드 큰 파일들을 보려면 1 을 넣는다 





[root@mywww bin]# ./check_mysql_disk.sh 



## 사용법 :: 명령어 인수로 1,2,3 중에 하나를 넘겨야 함. 

.. 

오버헤드 많은 테이블 순서로 보려면 : ./check_mysql_disk.sh 1 

데이타 크기 큰 테이블 순서로 보려면 : ./check_mysql_disk.sh 2 

데이타 Row 많은 테이블 순서로 보려면 : ./check_mysql_disk.sh 3 



[root@mywww bin]# ./check_mysql_disk.sh 1 



### 오버헤드 많은 순서로 출력 ### 



오버헤드: 25,217,520	전체크기: 25,508,732	Row수: 171	:::	[jura_db : zetyx_board_j2] 

오버헤드: 21,730,568	전체크기: 22,058,496	Row수: 90	:::	[jc_db : zetyx_board_propose_01] 

오버헤드: 11,366,084	전체크기: 11,516,944	Row수: 77	:::	[bang_db : g4_write_pds] 

... 





위에서 jura_db 라는 DB 의 zetyz_board_12 라는 테이블이 오버헤드가 25 메가가 넘는다. 

    이걸 최적화 하려면 optimize table 명령어로 최적화 하면 된다. 





5.  마무리 

약간이라도 도움이 되길.. 

안되면 될 때까지.. 





== 자르기 시작 ================================

#!/usr/bin/php



<?php



error_reporting(E_ALL ^ E_NOTICE);



//$mysql_root_pwd="kqka##091";//만약 mysql root 비번을 직접 넣는다면 이 라인 사용

$r_file = "/root/bin/.mysqlinfo"; // mysql root 비밀번호 달랑 적힌 파일

$mysql_root_pwd=`cat $r_file`; // 위 달랑 적힌 파일 불러와 사용하려면 이 라인 사용



$data_free_limit = 1000000; // 테이블별 오버헤드 출력기준(byte)

$data_size_limit = 10000000; // 테이블별 데이타 크기 출력 기준(byte)

$data_rows_limit = 100000; // 테이블별  데이타 갯수(row) 출력 기준



if ( $_SERVER['argv'][1] == ''){

	die("

	## 사용법 :: 명령어 인수로 1,2,3 중에 하나를 넘겨야 함.

	..

	오버헤드 많은 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 1

	데이타 크기 큰 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 2

	데이타 Row 많은 테이블 순서로 보려면 : ". $_SERVER['argv'][0] ." 3

	\n");

}



if ( $_SERVER['argv'][1] == '2'){

	$coln = 'total';

	$title = "데이타 크기 순서로 출력"; 

}else if ( $_SERVER['argv'][1] == '3'){

	$coln = 'Rows';

	$title = "데이타 Row 많은 순서로 출력"; 

}else{

	$coln = 'Data_free';

	$title = "오버헤드 많은 순서로 출력"; 

}



echo "### $title ###\n\n";



//print_r(array_sort($people, 'age', SORT_DESC)); // Sort by oldest first

function array_sort($array, $on, $order=SORT_ASC) {

    $new_array = array();

    $sortable_array = array();



    if (count($array) > 0) {

        foreach ($array as $k => $v) {

            if (is_array($v)) {

                foreach ($v as $k2 => $v2) {

                    if ($k2 == $on) {

                        $sortable_array[$k] = $v2;

                    }

                }

            } else {

                $sortable_array[$k] = $v;

            }

        }



        switch ($order) {

            case SORT_ASC:

                asort($sortable_array);

            break;

            case SORT_DESC:

                arsort($sortable_array);

            break;

        }



        foreach ($sortable_array as $k => $v) {

            $new_array[$k] = $array[$k];

        }

    }

    return $new_array;

}



$conn = mysql_connect("localhost","root",trim($mysql_root_pwd));

mysql_select_db("mysql");



$result = mysql_query("select Db from db");

while($arr = mysql_fetch_array($result)) {

    $dbarr[] = $arr['Db'];

}



foreach($dbarr as $k){

    #$result = mysql_query("SHOW TABLE STATUS FROM $k where Data_free > 100000 or Data_length > 100000;");

    #$result = mysql_query("SHOW TABLE STATUS FROM $k where Data_free > $data_free_limit;");

    $result = mysql_query("SHOW TABLE STATUS FROM $k");



	if ( $result ){

		while($array = mysql_fetch_array($result,MYSQL_ASSOC)){

			$array['total'] = $array[Data_length]+$array[Index_length];

			$array['Dbname'] = $k;



			if ( $coln == 'Rows' ){

				if ( $array[Rows] > $data_rows_limit ) $dinfo[] = $array;

			}elseif ( $coln == 'total' ){

				if ( $array[total] > $data_size_limit ) $dinfo[] = $array;

			}else{

				if ( $array[Data_free] > $data_free_limit ) $dinfo[] = $array;

			}

		}

	}

}



$dinfo2=array_sort($dinfo,$coln,SORT_DESC);



foreach($dinfo2 as  $vv){

	$total=$data_free=$rows='';

	$total = number_format($vv['total']);

	$data_free = number_format($vv['Data_free']);

	$rows = number_format($vv['Rows']);

	echo "오버헤드: ".$data_free."\t전체크기: ".$total."\tRow수: ".$rows."\t:::\t";

	echo "[".$vv['Dbname']." : ".$vv[Name]."] \n\n";

}

mysql_close();

?>

== 자르기 끝 ================================