PHP数据库备份还原类

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