/**
* 备份
* @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,-1);
$data = $this->query("SELECT * FROM `{$table}`");
foreach ($data as $value)
{
$datasql = '';
foreach ($value as $v)
{
$datasql .= "'{$v}',";
}
$datasql = substr($datasql,-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;
}
}