PHP数据库备份还原类

php代码

<?php
/**
 * 数据库备份还原类
 * @author xialeistudio* @date 2014-03-17
 * Class DatabaseTool
 */
class DatabaseTool
{
	private $handler;
	private $config = array(
		&#39;host&#39; => &#39;localhost&#39;,
		&#39;port&#39; => 3306,
		&#39;user&#39; => &#39;root&#39;,
		&#39;password&#39; => &#39;&#39;,
		&#39;database&#39; => &#39;test&#39;,
		&#39;charset&#39; => &#39;utf8&#39;,
		&#39;target&#39; => &#39;sql.sql&#39;
	);
	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[&#39;host&#39;]}:{$this->config[&#39;port&#39;]};dbname={$this->config[&#39;database&#39;]}", $this->config[&#39;user&#39;], $this->config[&#39;password&#39;]);
			}
			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 = &#39;数据库中没有表!&#39;;
			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 = &#39;&#39;)
	{
		$stmt = $this->handler->query($sql);
		$stmt->setFetchMode(PDO::FETCH_NUM);
		$list = $stmt->fetchAll();
		return $list;
	}

	/**
	 * 获取全部表
	 * @return array
	 */
	private function getTables()
	{
		$sql = &#39;SHOW TABLES&#39;;
		$list = $this->query($sql);
		$tables = array();
		foreach ($list as $value)
		{
			$tables[] = $value[0];
		}
		return $tables;
	}

	/**
	 * 获取表定义语句
	 * @param string $table
	 * @return mixed
	 */
	private function getDDL($table = &#39;&#39;)
	{
		$sql = "SHOW CREATE TABLE `{$table}`";
		$ddl = $this->query($sql)[0][1] . &#39;;&#39;;
		return $ddl;
	}

	/**
	 * 获取表数据
	 * @param string $table
	 * @return mixed
	 */
	private function getData($table = &#39;&#39;)
	{
		$sql = "SHOW COLUMNS FROM `{$table}`";
		$list = $this->query($sql);
		//字段
		$columns = &#39;&#39;;
		//需要返回的SQL
		$query = &#39;&#39;;
		foreach ($list as $value)
		{
			$columns .= "`{$value[0]}`,";
		}
		$columns = substr($columns, 0, -1);
		$data = $this->query("SELECT * FROM `{$table}`");
		foreach ($data as $value)
		{
			$dataSql = &#39;&#39;;
			foreach ($value as $v)
			{
				$dataSql .= "&#39;{$v}&#39;,";
			}
			$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[&#39;host&#39;]}:{$this->config[&#39;port&#39;]}\r\n";
		$str .= "Database:{$this->config[&#39;database&#39;]}\r\n";
		$str .= "Data:" . date(&#39;Y-m-d H:i:s&#39;, 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[&#39;target&#39;], $str) ? &#39;备份成功!花费时间&#39; . (microtime(true) - $this->begin) . &#39;ms&#39; : &#39;备份失败!&#39;;
	}

	/**
	 * 错误信息
	 * @return mixed
	 */
	public function getError()
	{
		return $this->error;
	}

	public function restore($path = &#39;&#39;)
	{
		if (!file_exists($path))
		{
			$this->error(&#39;SQL文件不存在!&#39;);
			return false;
		}
		else
		{
			$sql = $this->parseSQL($path);
			try
			{
				$this->handler->exec($sql);
				echo &#39;还原成功!花费时间&#39;, (microtime(true) - $this->begin) . &#39;ms&#39;;
			}
			catch (PDOException $e)
			{
				$this->error = $e->getMessage();
				return false;
			}
		}
	}

	/**
	 * 解析SQL文件为SQL语句数组
	 * @param string $path
	 * @return array|mixed|string
	 */
	private function parseSQL($path = &#39;&#39;)
	{
		$sql = file_get_contents($path);
		$sql = explode("\r\n", $sql);
		//先消除--注释
		$sql = array_filter($sql, function ($data)
		{
			if (empty($data) || preg_match(&#39;/^--.*/&#39;, $data))
			{
				return false;
			}
			else
			{
				return true;
			}
		});
		$sql = implode(&#39;&#39;, $sql);
		//删除/**/注释
		$sql = preg_replace(&#39;/\/\*.*\*\//&#39;, &#39;&#39;, $sql);
		return $sql;
	}
}