- <?php
- /**
- * 数据库备份还原类
- * @author xialeistudio<admin@xialeistudio.net>
- * @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;
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/030420149228.html
来源: http://www.codesnippet.cn/detail/030420149228.html