搜索了不少资料，东拼西凑总算完成了。下面贴出来代码，原理就不讲解了，代码挺简单的，重要部分我都做了注释。
<?php use Think\Db; /** * 数据库备份还原类 * @author luoe.cn<[email protected]> * @date 2016-04-29 * Class BackupDb */ class BackupDb{ private $handler; private $config = array( 'host' => '127.0.0.1', 'port' => 3306, 'user' => '', 'password' => '', 'database' => '', 'charset' => 'UTF8', ); private $filename = "./db/"; private $tables = array(); private $error; private $begin; //开始时间 /** * 架构方法 * @param array $config */ public function __construct($filename) { $this -> filename = $filename; $this ->config['host'] = C('DB_HOST'); $this ->config['port'] = C('DB_PORT'); $this ->config['user'] = C('DB_USER'); $this ->config['password'] = C('DB_PWD'); $this ->config['database'] = C('DB_NAME'); $this ->config['charset'] = C('DB_CHARSET'); $this->begin = microtime(true); } /** * 备份 * @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); } //开始写入 $r = $this->writeToFile($this->tables, $ddl, $data); return $r; } 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 = '') { $list = M() -> query($sql); return $list; } /** * 获取全部表 * @return array */ private function getTables() { $sql = 'SHOW TABLES'; $list = $this->query($sql); $tables = array(); foreach ($list as $value) { $tables[] = current($value); } return $tables; } /** * 获取表定义语句 * @param string $table * @return mixed */ private function getDDL($table = '') { $sql = "SHOW CREATE TABLE `{$table}`"; $ddl = $this->query($sql); $ddl = $ddl[0]['create table'].";"; return $ddl; } /** * 获取表数据 * @param string $table * @return mixed */ private function getData($table = '') { $sql = "SHOW COLUMNS FROM `{$table}`"; $list = $this->query($sql); //字段 $data = $this->query("SELECT * FROM `{$table}`"); foreach ($data as $value) { $dataSql = ''; foreach ($value as $v) { $dataSql .= "'" . mysql_escape_string ( $v ) . "',"; } $dataSql = substr($dataSql, 0, -1); $query .= "INSERT INTO `{$table}` 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++; } $filename = $this -> filename."backup_".time().".sql"; $r = file_put_contents($filename, $str) ? '备份成功!花费时间' . (microtime(true) - $this->begin) . 'ms' : '备份失败!'; return $r; } /** * 错误信息 * @return mixed */ public function getError() { return $this->error; } /** * 数据库还原 * @param string $path */ public function restore($path = '') { $path = $this -> filename.$path; //return $path; if (!file_exists($path)) { $this->error = 'SQL文件不存在!'; return false; } else { $sql = $this->ParseSqlArr($path); try { foreach($sql as $val){ M() -> execute($val); //循环写入数据库 } $r = '还原成功!花费时间'. (microtime(true) - $this->begin) . 'ms'; return $r; }catch (PDOException $e){ $this->error = $e->getMessage(); return false; } } } /** * 解析SQL文件为SQL语句数组 * @param string $path * @return array|mixed|string */ private function ParseSqlArr($sqlfile){ // sql文件包含的sql语句数组 $sqls = array (); $f = fopen ( $sqlfile, "rb" ); // 创建表缓冲变量 $create = ''; while ( ! feof ( $f ) ) { // 读取每一行sql $line = fgets ( $f ); // 如果包含'-- '等注释，或为空白行，则跳过 if (trim ( $line ) == '' || preg_match ( '/^--*?/', $line, $match )) { continue; } // 如果结尾包含';'(即为一个完整的sql语句，这里是插入语句)，并且不包含'ENGINE='(即创建表的最后一句)， if (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) { // 将本次sql语句与创建表sql连接存起来 $create .= $line; // 如果包含了创建表的最后一句 if (preg_match ( '/ENGINE=/', $create, $match )) { // 则将其合并到sql数组 $create = preg_replace('/\/\*[^\*\/]*\*\//i', "", $create);//删除多行注释 $sqls [] = $create; // 清空当前，准备下一个表的创建 $create = ''; } // 跳过本次 continue; } $sqls [] = $line; } fclose ( $f ); return $sqls; } }
转载请注明：七彩悠悠博客 | 心悠悠 情悠悠 » 基于thinkphp的数据库在线备份还原
