這篇文章主要介紹了php數(shù)據(jù)庫備份還原類,需要的朋友可以參考下

代碼如下:

<?php

/**

* 數(shù)據(jù)庫備份還原類

* @author xialeistudio<admin@xialeistudio.net>

* Class DatabaseTool

*/

class DatabaseTool

{

private $handler;

private $config = array(

'host' => 'localhost',

'port' => 3306,

'user' => 'root',

'password' => '',

'database' => 'test',

'charset' => 'utf-8',

'target' => 'sql.sql'

);

private $tables = array();

private $error;

private $begin; //開始時(shí)間

/**

* 架構(gòu)方法

* @param array $config

*/

public function __construct($config = array())

{

$this->begin = microtime(true);

$config = is_array($config) ? $config : array();

$this->config = array_merge($this->config, $config);

//啟動(dòng)PDO連接

if (!$this->handler instanceof PDO)

{

try

{

$this->handler = new PDO("mysql:host={$this->config['host']}:{$this->config['port']};dbname={$this->config['database']}", $this->config['user'], $this->config['password']);

}

catch (PDOException $e)

{

$this->error = $e->getMessage();

return false;

}

catch (Exception $e)

{

$this->error = $e->getMessage();

return false;

}

}

}

/**

* 備份

* @param array $tables

* @return bool

*/

public function backup($tables = array())

{

//存儲(chǔ)表定義語句的數(shù)組

$ddl = array();

//存儲(chǔ)數(shù)據(jù)的數(shù)組

$data = array();

$this->setTables($tables);

if (!empty($this->tables))

{

foreach ($this->tables as $table)

{

$ddl[] = $this->getDDL($table);

$data[] = $this->getData($table);

}

//開始寫入

$this->writeToFile($this->tables, $ddl, $data);

}

else

{

$this->error = '數(shù)據(jù)庫中沒有表!';

return false;

}

}

/**

* 設(shè)置要備份的表

* @param array $tables

*/

private function setTables($tables = array())

{

if (!empty($tables) && is_array($tables))

{

//備份指定表

$this->tables = $tables;

}

else

{

//備份全部表

$this->tables = $this->getTables();

}

}

/**

* 查詢

* @param string $sql

* @return mixed

*/

private function query($sql = '')

{

$stmt = $this->handler->query($sql);

$stmt->setFetchMode(PDO::FETCH_NUM);

$list = $stmt->fetchAll();

return $list;

}

/**

* 獲取全部表

* @return array

*/

private function getTables()

{

$sql = 'SHOW TABLES';

$list = $this->query($sql);

$tables = array();

foreach ($list as $value)

{

$tables[] = $value[0];

}

return $tables;

}

/**

* 獲取表定義語句

* @param string $table

* @return mixed

*/

private function getDDL($table = '')

{

$sql = "SHOW CREATE TABLE `{$table}`";

$ddl = $this->query($sql)[0][1] . ';';

return $ddl;

}

/**

* 獲取表數(shù)據(jù)

* @param string $table

* @return mixed

*/

private function getData($table = '')

{

$sql = "SHOW COLUMNS FROM `{$table}`";

$list = $this->query($sql);

//字段

$columns = '';

//需要返回的SQL

$query = '';

foreach ($list as $value)

{

$columns .= "`{$value[0]}`,";

}

$columns = substr($columns, 0, -1);

$data = $this->query("SELECT * FROM `{$table}`");

foreach ($data as $value)

{

$dataSql = '';

foreach ($value as $v)

{

$dataSql .= "'{$v}',";

}

$dataSql = substr($dataSql, 0, -1);

$query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});rn";

}

return $query;

}

/**

* 寫入文件

* @param array $tables

* @param array $ddl

* @param array $data

*/

private function writeToFile($tables = array(), $ddl = array(), $data = array())

{

$str = "/*rnMySQL Database Backup Toolsrn";

$str .= "Server:{$this->config['host']}:{$this->config['port']}rn";

$str .= "Database:{$this->config['database']}rn";

$str .= "Data:" . date('Y-m-d H:i:s', time()) . "rn*/rn";

$str .= "SET FOREIGN_KEY_CHECKS=0;rn";

$i = 0;

foreach ($tables as $table)

{

$str .= "-- ----------------------------rn";

$str .= "-- Table structure for {$table}rn";

$str .= "-- ----------------------------rn";

$str .= "DROP TABLE IF EXISTS `{$table}`;rn";

$str .= $ddl[$i] . "rn";

$str .= "-- ----------------------------rn";

$str .= "-- Records of {$table}rn";

$str .= "-- ----------------------------rn";

$str .= $data[$i] . "rn";

$i++;

}

echo file_put_contents($this->config['target'], $str) ? '備份成功!花費(fèi)時(shí)間' . (microtime(true) - $this->begin) . 'ms' : '備份失敗!';

}

/**

* 錯(cuò)誤信息

* @return mixed

*/

public function getError()

{

return $this->error;

}

public function restore($path = '')

{

if (!file_exists($path))

{

$this->error('SQL文件不存在!');

return false;

}

else

{

$sql = $this->parseSQL($path);

try

{

$this->handler->exec($sql);

echo '還原成功!花費(fèi)時(shí)間', (microtime(true) - $this->begin) . 'ms';

}

catch (PDOException $e)

{

$this->error = $e->getMessage();

return false;

}

}

}

/**

* 解析SQL文件為SQL語句數(shù)組

* @param string $path

* @return array|mixed|string

*/

private function parseSQL($path = '')

{

$sql = file_get_contents($path);

$sql = explode("rn", $sql);

//先消除--注釋

$sql = array_filter($sql, function ($data)

{

if (empty($data) || preg_match('/^--.*/', $data))

{

return false;

}

else

{

return true;

}

});

$sql = implode('', $sql);

//刪除/**/注釋

$sql = preg_replace('//*.**//', '', $sql);

return $sql;

}

}

更多信息請(qǐng)查看IT技術(shù)專欄