PHP - MYSQL DB의 모든 테이블에서 문자열 검색 하기

대규모의 DB에서, 게다가 해당 DB에 대한 정보가 부족한 상태일때, 해당 DB에서 특정 컬럼의 어떤값을 다른 값으로 바꿔야 하는 때가 있다면, 이 글이 도움이 될 것이다.

우선 해당 작업을 하기 위해선 해당 문자열이 대체 어느 테이블의 어떤 컬럼에 위치 하는지 찾아야 할 것이다. 그래야 update를 할 수 있기 때문이다.

일단, 어떤 테이블에 해당 문자열이 들어가는지를 검색해보고 싶다면, 해당 리눅스서버에 접속해서, mysql이 설치되어 있는폴더에서 직접 grep으로 검색해 볼 수 있다.
보통 mysql에서 테이블은

테이블명.MYI (인덱스파일)
테이블명.MYD (데이터파일)
테이블명.frm (형식-스키마파일)

위 3가지 파일로 이루어 지기 때문에, mysql이 설치된 폴더를 찾아서

grep 찾을문자열 *.MYD

위와 같은 명령어를 쳐주면, 자신이 찾을 문자열이 어느 테이블에 있는지는 알 수 있다. 하지만 해당 파일은 바이너리로 되어 있기 때문에, 해당 파일을 직접 건드리거나.. 하는건.. 무리일 것이다.

그리고, 보통 해당 컬럼의 값을 다른 값으로 변경해야 하는 작업이 닥치곤 하기에.. 필자는 php 프로그램으로 직접 짜보았다.

$hostName = "localhost";
$userName = "root"; //스키마테이블을 건드려야하기 때문에 root로 접속해야한다. 
$userPassword = "비밀번호";
$dbName = "information_schema"; 

include_once "DB.php"; //필자는 pear클래스로 접속하여 아래 프로그램을 짰다.

//연결
$dsn = "mysql://$userName:$userPassword@$hostName/$dbName"; 
$conn = DB::connect($dsn);
if(DB::isError($conn)) die ($conn->getMessage());

$table_schema = '데이터베이스이름'; //검색할 DB
$sqry = " like '문자열'"; //찾을 조건

$query = "select * from COLUMNS where TABLE_SCHEMA = '".$table_schema."' ";
$result= $conn->query( $query );
if(DB::isError($result)) die($result->getMessage());

//해당 DB의 모든 테이블과 컬럼을 배열로 만든다
while($d = $result->fetchRow(DB_FETCHMODE_ASSOC)) $tables[$d['TABLE_NAME']] []= $d['COLUMN_NAME'];

ob_start();
ob_implicit_flush(true);
$r = str_repeat("\r", 4096 ); //화면 표시를 위한 버버채우기용 문자

foreach( $tables as $tkey1=>$tval1 )
{
  foreach( $tval1 as $tkey2=>$tval2 )
  {
    $query = "select count(*) from ".$table_schema.".".$tkey1." where `".$tval2."`".$sqry." limit 1";
    $find = $conn->getOne( $query );
    if(DB::isError($find)) die($find->getMessage());
    if( $find )
    {
      echo "Table : <b>".$tkey1."</b> Column : <b>".$tval2."</b><br>";
      
      //업데이트도 해주려면 아래처럼 해주면 된다.
//      $uqry = "update ".$table_schema.".".$tkey1." set `".$tval2."` = '바꿀문자열' where `".$tval2."`".$sqry;
//      $result= $conn->query( $uqry );
//      if(DB::isError($result)) die($result->getMessage());
      
      echo $r ;
      ob_flush();
      usleep(100000);
    }
  }
}

ob_end_flush();

$conn->disconnect();

위 소스에 대해서 간단히 설명하자면, information_schema데이터베이스의 COLUMNS테이블에서 해당 DB의 모든 테이블과 컬럼값을 사용하기 쉽게 배열로 만든뒤에, foreach문으로 돌려서 하나하나 검색해서 결과를 표시해주는.. 덕분에 시간이 오래 걸려서 ob_start()로 차근차근 화면에 뿌려주는 역활을 하는 소스이다.
그리고 주석을 친 부분을 풀어주면, 원하는 값으로 검색 문자열을 모두 치환 할 수도 있다.

좀 무식하고 시간이 걸리는 방법이긴 해도.. 뭐 직접 하나 하나 하는것 보다는 빠르고 나을것이다.

MYSQL DB의 모든 테이블에서 문자열 검색 하기

단축키

Articles