這篇文章主要介紹了php數據庫備份還原類,需要的朋友可以參考下
代碼如下: <?php /** * 數據庫備份還原類 * @author xialeistudio<[email protected]> * 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; //開始時間 /** * 架構方法 * @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); //啟動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()) { //存儲表定義語句的數組 $ddl = array(); //存儲數據的數組 $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 = '數據庫中沒有表!'; return false; } } /** * 設置要備份的表 * @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; } /** * 獲取表數據 * @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) ? '備份成功!花費時間' . (microtime(true) - $this->begin) . 'ms' : '備份失敗!'; } /** * 錯誤信息 * @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 '還原成功!花費時間', (microtime(true) - $this->begin) . 'ms'; } catch (PDOException $e) { $this->error = $e->getMessage(); return false; } } } /** * 解析SQL文件為SQL語句數組 * @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; } }
