DB백업코드 정보
MySQL DB백업코드본문
MySQL에서 디비를 백업하는 클래스를 소개합니다.
<?php
define("DB_USER", 'root'); // 디비유저
define("DB_PASSWORD", ''); // 패스워드
define("DB_NAME", 'evening2'); // 디비명
define("DB_HOST", 'localhost'); // 디비호스트
define("BACKUP_DIR", '.'); // 백업저장용 폴더
define("TABLES", '*'); // 백업하려는 테이블명 (* 은 디비안의 모든 테이블을 백업)
//define("TABLES", 'table1, table2, table3'); // table1, table2, table3 만 백업
define("CHARSET", 'utf8');
define("GZIP_BACKUP_FILE", true);
/**
* 디비백업 클래스
*/
class Backup_Database {
var $host;
var $username;
var $passwd;
var $dbName;
var $charset;
var $conn;
var $backupDir;
var $backupFile;
var $gzipBackupFile;
var $output;
public function __construct($host, $username, $passwd, $dbName, $charset = 'utf8') {
$this->host = $host;
$this->username = $username;
$this->passwd = $passwd;
$this->dbName = $dbName;
$this->charset = $charset;
$this->conn = $this->initializeDatabase();
$this->backupDir = BACKUP_DIR ? BACKUP_DIR : '.';
$this->backupFile = 'myphp-backup-'.$this->dbName.'-'.date("Ymd_His", time()).'.sql';
$this->gzipBackupFile = defined('GZIP_BACKUP_FILE') ? GZIP_BACKUP_FILE : true;
$this->output = '';
}
protected function initializeDatabase() {
try {
$conn = mysqli_connect($this->host, $this->username, $this->passwd, $this->dbName);
if (mysqli_connect_errno()) {
throw new Exception('ERROR connecting database: ' . mysqli_connect_error());
die();
}
if (!mysqli_set_charset($conn, $this->charset)) {
mysqli_query($conn, 'SET NAMES '.$this->charset);
}
} catch (Exception $e) {
print_r($e->getMessage());
die();
}
return $conn;
}
public function backupTables($tables = '*') {
try {
/**
* Tables to export
*/
if($tables == '*') {
$tables = array();
$result = mysqli_query($this->conn, 'SHOW TABLES');
while($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', str_replace(' ', '', $tables));
}
$sql = 'CREATE DATABASE IF NOT EXISTS `'.$this->dbName."`;\n\n";
$sql .= 'USE `'.$this->dbName."`;\n\n";
/**
* Iterate tables
*/
foreach($tables as $table) {
$this->obfPrint("Backing up `".$table."` table...".str_repeat('.', 50-strlen($table)), 0, 0);
/**
* CREATE TABLE
*/
$sql .= 'DROP TABLE IF EXISTS `'.$table.'`;';
$row = mysqli_fetch_row(mysqli_query($this->conn, 'SHOW CREATE TABLE `'.$table.'`'));
$sql .= "\n\n".$row[1].";\n\n";
/**
* INSERT INTO
*/
$row = mysqli_fetch_row(mysqli_query($this->conn, 'SELECT COUNT(*) FROM `'.$table.'`'));
$numRows = $row[0];
// Split table in batches in order to not exhaust system memory
$batchSize = 1000; // Number of rows per batch
$numBatches = intval($numRows / $batchSize) + 1; // Number of while-loop calls to perform
for ($b = 1; $b <= $numBatches; $b++) {
$query = 'SELECT * FROM `'.$table.'` LIMIT '.($b*$batchSize-$batchSize).','.$batchSize;
$result = mysqli_query($this->conn, $query);
$numFields = mysqli_num_fields($result);
for ($i = 0; $i < $numFields; $i++) {
$rowCount = 0;
while($row = mysqli_fetch_row($result)) {
$sql .= 'INSERT INTO `'.$table.'` VALUES(';
for($j=0; $j<$numFields; $j++) {
if (isset($row[$j])) {
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n","\\n",$row[$j]);
$sql .= '"'.$row[$j].'"' ;
} else {
$sql.= 'NULL';
}
if ($j < ($numFields-1)) {
$sql .= ',';
}
}
$sql.= ");\n";
}
}
$this->saveFile($sql);
$sql = '';
}
$sql.="\n\n\n";
$this->obfPrint(" OK");
}
if ($this->gzipBackupFile) {
$this->gzipBackupFile();
} else {
$this->obfPrint('Backup file succesfully saved to ' . $this->backupDir.'/'.$this->backupFile, 1, 1);
}
} catch (Exception $e) {
print_r($e->getMessage());
return false;
}
return true;
}
protected function saveFile(&$sql) {
if (!$sql) return false;
try {
if (!file_exists($this->backupDir)) {
mkdir($this->backupDir, 0777, true);
}
file_put_contents($this->backupDir.'/'.$this->backupFile, $sql, FILE_APPEND | LOCK_EX);
} catch (Exception $e) {
print_r($e->getMessage());
return false;
}
return true;
}
protected function gzipBackupFile($level = 9) {
if (!$this->gzipBackupFile) {
return true;
}
$source = $this->backupDir . '/' . $this->backupFile;
$dest = $source . '.gz';
$this->obfPrint('Gzipping backup file to ' . $dest . '... ', 1, 0);
$mode = 'wb' . $level;
if ($fpOut = gzopen($dest, $mode)) {
if ($fpIn = fopen($source,'rb')) {
while (!feof($fpIn)) {
gzwrite($fpOut, fread($fpIn, 1024 * 256));
}
fclose($fpIn);
} else {
return false;
}
gzclose($fpOut);
if(!unlink($source)) {
return false;
}
} else {
return false;
}
$this->obfPrint('OK');
return $dest;
}
public function obfPrint ($msg = '', $lineBreaksBefore = 0, $lineBreaksAfter = 1) {
if (!$msg) {
return false;
}
$output = '';
if (php_sapi_name() != "cli") {
$lineBreak = "<br />";
} else {
$lineBreak = "\n";
}
if ($lineBreaksBefore > 0) {
for ($i = 1; $i <= $lineBreaksBefore; $i++) {
$output .= $lineBreak;
}
}
$output .= $msg;
if ($lineBreaksAfter > 0) {
for ($i = 1; $i <= $lineBreaksAfter; $i++) {
$output .= $lineBreak;
}
}
// Save output for later use
$this->output .= str_replace('<br />', '\n', $output);
echo $output;
if (php_sapi_name() != "cli") {
ob_flush();
}
$this->output .= " ";
flush();
}
public function getOutput() {
return $this->output;
}
}
error_reporting(E_ALL);
set_time_limit(900); // 15 minutes
if (php_sapi_name() != "cli") {
echo '<div style="font-family: monospace;">';
}
$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$result = $backupDatabase->backupTables(TABLES, BACKUP_DIR) ? 'OK' : 'KO';
$backupDatabase->obfPrint('Backup result: ' . $result, 1);
$output = $backupDatabase->getOutput();
if (php_sapi_name() != "cli") {
echo '</div>';
}
한번 테스트해보시면 보다 잘 이해되십니다.
!-->
추천
6
6
댓글 5개
감사합니다.
감사합니다.
우왕~ 얼른 그누가 oop로 바꼈스면 좋겠네요
감사합니다~
신고가 접수된 글입니다.
신고 횟수가 1회 이상이면 글을 확인하지 못합니다.
감사합니다.
天安门相关活动进展顺利。
天安门相关活动进展顺利。