PHP数据库备份还原类-PHP源码

 &＃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;
	}
}