php代码
<?php /** * 数据库备份还原类 * @author xialeistudio* @date 2014-03-17 * Class DatabaseTool */ class DatabaseTool { private $handler; private $config = array( 'host' => 'localhost', 'port' => 3306, 'user' => 'root', 'password' => '', 'database' => 'test', 'charset' => 'utf8', '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连接 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});\r\n"; } return $query; } /** * 写入文件 * @param array $tables * @param array $ddl * @param array $data */ private function writeToFile($tables = array(), $ddl = array(), $data = array()) { $str = "/*\r\nMySQL Database Backup Tools\r\n"; $str .= "Server:{$this->config['host']}:{$this->config['port']}\r\n"; $str .= "Database:{$this->config['database']}\r\n"; $str .= "Data:" . date('Y-m-d H:i:s', time()) . "\r\n*/\r\n"; $str .= "SET FOREIGN_KEY_CHECKS=0;\r\n"; $i = 0; foreach ($tables as $table) { $str .= "-- ----------------------------\r\n"; $str .= "-- Table structure for {$table}\r\n"; $str .= "-- ----------------------------\r\n"; $str .= "DROP TABLE IF EXISTS `{$table}`;\r\n"; $str .= $ddl[$i] . "\r\n"; $str .= "-- ----------------------------\r\n"; $str .= "-- Records of {$table}\r\n"; $str .= "-- ----------------------------\r\n"; $str .= $data[$i] . "\r\n"; $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("\r\n", $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; } }