Mysql备份类

<?php

namespace maowenke\mysql;
/**
 * Mysql备份类
 */
class Backup {
	/**
	 * 数据库配置
	 * @var array
	 */
	protected $database = [];

	/**
	 * pdo连接对象
	 * @var null
	 */
	protected $pdo = null;

	/**
	 * 要备份的数据表
	 * @var array
	 */
	protected $tables = [];

	/**
	 * 输出目录 /结尾
	 * @var string
	 */
	protected $output_path = '';

	/**
	 * 文件名
	 * @var string
	 */
	protected $filename = '';

	/**
	 * 格式后缀
	 * @var string
	 */
	protected $ext = 'sql';

	/**
	 * 默认仅结构，为true转储为结构和数据
	 * @var bool
	 */
	protected $with_data = false;

	/**
	 * 创建数据表语句 如果表存在就删除
	 * @var bool
	 */
	protected $drop_table_exists = true;

	/**
	 * 创建数据表语句 判断如果表不存在才创建
	 * @var bool
	 */
	protected $create_if_not_exists = true;

	/**
	 * 错误信息
	 * @var string
	 */
	protected $error = '';

	/**
	 * 构造方法
	 * @param array $database
	 *    [
	 *        'username'=>'',
	 *        'password'=>'',
	 *        'database'=>'',
	 *        'host'=>''
	 *    ]
	 */
	public function __construct($database) {
		$this->database = $database;
	}

	/**设置要导出的表
	 * @param array $tables
	 */
	public function setTables($tables) {
		$this->tables = $tables;
		return $this;
	}

	/**设置导出存放的地址
	 * @param string $output_path
	 */
	public function setOutputPath($output_path) {
		$this->output_path = $output_path;
		return $this;
	}

	/**设置文件名
	 * @param string $filename
	 */
	public function setFilename($filename) {
		$this->filename = $filename;
		return $this;
	}

	/**
	 * 设置文件名为默认值
	 */
	private function defaultFileName() {
		$timezone = date_default_timezone_get();
		if ('PRC' !== $timezone) {
			date_default_timezone_set('PRC');
		}
		$this->filename = 'backup_' . date('Ydm_His');
	}

	/**设置文件后缀
	 * @param string $ext
	 */
	public function setExt($ext) {
		$this->ext = $ext;
		return $this;
	}

	/**设置是否需要导出数据
	 * @param bool $with_data
	 */
	public function setWithData($with_data) {
		$this->with_data = $with_data;
		return $this;
	}

	/**设置是否需要删除表
	 * @param bool $drop_table_exists
	 */
	public function setDropTableExists($drop_table_exists) {
		$this->drop_table_exists = $drop_table_exists;
		return $this;
	}

	/**
	 * @param bool $create_if_not_exists
	 */
	public function setCreateIfNotExists($create_if_not_exists) {
		$this->create_if_not_exists = $create_if_not_exists;
		return $this;
	}

	/**
	 * 连接数据库
	 */
	private function getConnection() {
		$dsn = 'mysql:host=' . $this->database['host'] . ';dbname=' . $this->database['database'];
		$username = $this->database['username'];
		$password = $this->database['password'];
		$driver_options = [
			\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
			\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
		];

		try {
			$this->pdo = new \PDO($dsn, $username, $password, $driver_options);
		} catch (\PDOException $e) {
			$this->error = $e->getMessage();
			return false;
		}
	}

	/**
	 * 执行备份 转储到文件
	 * @param string|array 条件sql $where
	 * @return mixed false 或 文件存储地址
	 */
	public function dump($where='') {
		/*链接数据库*/
		if (false === $this->getConnection()) {
			return false;
		}

		/*如果未指定数据表 则指定全部数据表*/
		if (0 == count($this->tables)) {
			$sth = $this->pdo->query('SHOW TABLES');
			while ($row = $sth->fetch()) {
				$this->tables[] = $row[0];
			}
		}

		/*组装文件名*/
		if (empty($this->filename)) {
			$this->defaultFileName();       //设置默认文件名
		}
		$ouput_file = $this->output_path . $this->filename . '.' . $this->ext;
		$this->writeInfo($ouput_file);
		/*sql按表输出到文件*/
		foreach ($this->tables as $key=>$table) {
		    if(is_array($where)){
		        if(isset($where[$key])){
                    $this->ouputToFile($table, $ouput_file,$where[$key]);
                }else{
                    $this->ouputToFile($table, $ouput_file,'');
                }
            }else{
                $this->ouputToFile($table, $ouput_file,$where);
            }
		}
		return [$ouput_file, getcwd() . DIRECTORY_SEPARATOR . $ouput_file];
	}

	public function writeInfo($file) {
		$info = "-- --------------------------------------------------\n";
		$info .= "--      备份分工具\n";
		$info .= "--           \n";
		$info .= "--           \n";
//		file_put_contents($file, $info, FILE_APPEND);
		file_put_contents($file, $info);
	}

	/**
	 * sql按表输出到文件
	 * @param $table
	 * @param $file
	 * @param $where
	 */
	public function ouputToFile($table, $file,$where='') {
		$sql = '';
		/*头注释*/
		$sql .= "-- --------------------------------------------------\n";
		$sql .= "--          数据表 " . $table . "\n";
		$sql .= "-- --------------------------------------------------\n\n";

		/*是否删除已存在的数据表*/
		if (true === $this->drop_table_exists) {
			$sql .= 'DROP TABLE IF EXISTS `' . $table . '`;';
			$sql .= "\n\n";
		}

		/*获取建表语句*/
		$sth = $this->pdo->query('SHOW CREATE TABLE `' . $table . '`');
		$create_sql = $sth->fetch();

		/*表存在就不再新建表*/
		if (true === $this->create_if_not_exists) {
			$sql .= preg_replace('/^CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_sql[1]);
			$sql .= ";\n\n";
		}

		/*如果需要导出数据*/
		if (true === $this->with_data) {
			/*可以优化为1000条取一次，降低数据量大时的负载*/
            if(empty($where)){
                $sth = $this->pdo->query('SELECT * FROM ' . $table);
            }else{
                $sth = $this->pdo->query('SELECT * FROM ' . $table.' '.$where);
            }
//            $sth = $this->pdo->query('SELECT * FROM ' . $table);
			while ($row = $sth->fetch(\PDO::FETCH_ASSOC)) {
				$sql .= 'INSERT INTO `' . $table . '` VALUES(';
				$fields = [];
				foreach ($row as $field) {
					if (null !== $field) {
						$fields[] = $this->pdo->quote($field);
					} else {
						$fields[] = ' null';
					}
				}
				$sql .= implode(',', $fields);
				$sql .= ");\n";
			}
		}
		$sql .= "\n\n";
		/**
		 * 写入文件
		 */
		file_put_contents($file, $sql, FILE_APPEND);
	}

	/**
	 * 下载文件
	 */
	public function download() {
		/*组装文件名*/
		if (empty($this->filename)) {
			$this->defaultFileName();       //设置默认文件名
		}
		/*发送响应头*/
		header('Content-disposition: attachment; filename="' . $this->filename . '.' . $this->ext . '"');
		header('Content-type: application/octet-stream');
		/*转储到文件*/
		$filepath = $this->dump();
		echo @readfile($filepath[1]);
		unlink($filepath[1]);
	}

	/**
	 * 释放连接
	 */
	private function releaseConnection() {
		$this->pdo = null;
	}

	/**
	 * 析构方法
	 */
	public function __destruct() {
		$this->releaseConnection();
	}
}