DB 백업은 원래 mysqldump 명령 한 줄이면 끝나지만, 공유 호스팅이나 NAS처럼 셸을 쓸 수 없는 환경도 많습니다. phpMyAdmin 내보내기는 DB가 여러 개면 일일이 눌러야 하고요. 그래서 PHP 파일 하나를 올려 두고 주소로 호출하면 DB 구조와 데이터를 .sql 파일로 떨어뜨리게 만들어 둔 스크립트입니다.

백업 스크립트

db_nm 으로 DB 이름을, tb_nm 으로 테이블 이름을 받습니다. 둘 다 생략하면 * 가 되어 서버의 모든 DB, 모든 테이블을 백업하고, 쉼표로 여러 개를 넘길 수도 있습니다. 위쪽 주석은 실제로 호출하던 주소 형태입니다.

광고
<?php

//https://your-domain.com/db_backup.php?db_nm=alltogreen
//https://your-domain.com/db_backup.php?db_nm=aserpacific
//https://your-domain.com/db_backup.php?db_nm=jeju
//https://your-domain.com/db_backup.php?db_nm=mysql
//https://your-domain.com/db_backup.php?db_nm=information_schema

/**
* Updated: IT HUB. heesung byeon
* Website: program1472.com
*/

//MySQL server and database
$dbhost = 'localhost';
$dbuser = '로그인아이디';
$dbpass = '로그인비번';
$dbname = isset($_GET['db_nm'])?$_GET['db_nm']:'*';
$tables = isset($_GET['tb_nm'])?$_GET['tb_nm']:'*';

//Call the core function
backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables);

//Core function
function backup_tables($host, $user, $pass, $databases = '*', $tables = '*') {
    $con = mysqli_connect($host,$user,$pass);
    // Check connection
    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit;
    }

    if($databases == '*'){
        $databases = array();
        $result = mysqli_query($con, 'SHOW DATABASES');
        while($row = mysqli_fetch_row($result))
        {
            $databases[] = $row[0];
        }
    } else {
        $databases = is_array($databases) ? $databases : explode(',',$databases);
    }

    foreach($databases as $database)
    {
        mysqli_select_db($con, $database);
        mysqli_query($con, "SET NAMES 'utf8'");
        $table_array = array();
        //get all of the tables
        if($tables == '*')
        {
            $result = mysqli_query($con, 'SHOW TABLES');
            while($row = mysqli_fetch_row($result))
            {
                $table_array[] = $row[0];
            }
        }
        else
        {
            $table_array = is_array($tables) ? $tables : explode(',',$tables);
        }

        $return = '';
        //cycle through
        foreach($table_array as $table)
        {
            $result = mysqli_query($con, 'SELECT * FROM '.$table);
            if ($result){
                $num_fields = mysqli_num_fields($result);
                $num_rows = mysqli_num_rows($result);

                $return.= 'DROP TABLE IF EXISTS '.$table.';';
                $row2 = mysqli_fetch_row(mysqli_query($con, 'SHOW CREATE TABLE '.$table));
                $return.= "\n\n".$row2[1].";\n\n";
                $counter = 1;

                //Over tables
                for ($i = 0; $i < $num_fields; $i++)
                {   //Over rows
                    while($row = mysqli_fetch_row($result))
                    {
                        if($counter == 1){
                            $return.= 'INSERT INTO '.$table.' VALUES(';
                        } else{
                            $return.= '(';
                        }

                        //Over fields
                        for($j=0; $j<$num_fields; $j++)
                        {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = str_replace("\n","\\n",$row[$j]);
                            if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                            if ($j<($num_fields-1)) { $return.= ','; }
                        }

                        if($num_rows == $counter){
                            $return.= ");\n";
                        } else{
                            $return.= "),\n";
                        }
                        ++$counter;
                    }
                }
                $return.="\n\n\n";
            }
        }

        //save file
        // $fileName = 'db-backup-'.$database.'-'.time().'-'.(md5(implode(',',$tables))).'.sql';
        $fileName = 'db/backup-'.$database.'-'.time().'.sql';
        $handle = fopen($fileName,'w+');
        fwrite($handle,$return);
        if(fclose($handle)){
            echo "Done, the file name is: ".$fileName.'\r\n<br>';
            // exit;
        }
    }
}

만들어지는 파일 모양

테이블마다 DROP TABLE IF EXISTS 와 SHOW CREATE TABLE 결과를 먼저 적고, 그 아래에 데이터를 INSERT INTO 테이블 VALUES(...),(...),...; 형태의 한 문장으로 이어 붙입니다. 행마다 INSERT를 따로 쓰지 않고 하나로 묶기 때문에 파일 크기도 작고 복원할 때 훨씬 빠릅니다. DB 하나당 db/backup-DB이름-타임스탬프.sql 파일이 하나씩 생기니, 스크립트 옆에 db 폴더를 미리 만들고 쓰기 권한을 줘야 합니다. 폴더가 없으면 fopen 이 실패합니다.

바깥의 for ($i ...) 루프는 이름과 달리 실제로는 한 번만 의미가 있습니다. 첫 바퀴에서 while 이 결과의 모든 행을 읽어 버리니, 두 번째 바퀴부터는 꺼낼 행이 없어 그냥 지나갑니다. 결과에는 영향이 없지만 읽을 때 헷갈리는 부분이라 적어 둡니다.

그대로 쓰면 곤란한 점

NULL이 빈 문자열로 바뀝니다. addslashes() 를 거치면 NULL 도 "" 가 되기 때문에, 바로 다음 줄의 isset 은 항상 참이고 백업 파일에는 NULL 대신 "" 가 들어갑니다. 날짜나 숫자 컬럼에 NULL이 있었다면 복원할 때 엄격 모드에서 오류가 나거나 0, 0000-00-00 같은 값으로 바뀔 수 있습니다. 그리고 PHP 8.1 이상에서는 addslashes() 에 null을 넘기면 사용 중단 경고도 나옵니다. NULL을 지키려면 addslashes 전에 is_null($row[$j]) 를 먼저 확인해서 따옴표 없는 NULL 을 적도록 바꿔야 합니다.

아무나 호출할 수 있습니다. 인증 없이 주소만 알면 실행되고, db_nm, tb_nm 값이 그대로 쿼리에 들어갑니다. 백업 파일도 웹에서 접근 가능한 db/ 폴더에 쌓이니, 파일 이름만 맞히면 DB 전체를 내려받을 수 있는 구조입니다. 실제로 쓴다면 최소한 비밀 키 파라미터를 확인하거나, 파일은 웹 루트 밖에 저장하거나, db 폴더에 접근 차단 .htaccess 를 두세요. 쓰고 나서 스크립트를 지우는 것도 방법입니다.

큰 DB에는 맞지 않습니다. 테이블 전체를 SELECT * 로 읽고 결과 문자열을 메모리에 모아 한 번에 쓰기 때문에, 수십 MB 이상 되는 DB는 memory_limit 이나 max_execution_time 에 걸려 중간에 끊깁니다. 그 정도 규모라면 테이블별로 나눠 호출하거나, 행을 일정 개수씩 파일에 바로 쓰는 식으로 고쳐야 합니다.

그 밖에 SET NAMES 'utf8' 은 4바이트 문자(이모지 등)를 제대로 못 다루니 지금이라면 utf8mb4 로 바꾸는 게 맞고, information_schema 처럼 읽기 전용 시스템 DB는 백업해도 복원할 수 없으니 * 로 전체를 돌릴 때는 결과 파일에서 빼고 보시면 됩니다.