php数据库备份还原类分享

<?php
/**
 * 数据库备份还原类
 * @author xialeistudio<admin@xialeistudio.net>
 * class databasetool
 */
class databasetool
{
 private $handler;
 private $config = array(
  'host' => 'localhost',
  'port' => 3306,
  'user' => 'root',
  'password' => '',
  'database' => 'test',
  'charset' => 'utf-8',
  '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连接
  if (!$this->handler instanceof 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;
 }
}