mysql 数据备份，恢复,恢复没写，这里只写了备份。。。 先暂作记录吧！
备份:表结构和数据完全分开，默认有一个文件会记录所有表的结构，然后表中数据的备份 如果超过分卷的大小则会分成多个文件，不然则一个文件，参考了别人的代码，不过写的嘛，差强 人意，以后慢慢改吧。。。
代码如下：
1 <?php 2 /* 3 * Created on 2014 4 * Link for 527891885@qq.com 5 * This is seocheck backup class 6 */ 7 class DbBackUp { 8 private $conn; 9 private $dbName; 10 private $host; 11 private $tag = '_b'; 12 //构造方法 链接数据库 13 public function __construct($host='localhost', $dbUser='root', $dbPwd='', $dbName="seocheck", $charset='utf8') { 14 @ob_start(); 15 @set_time_limit(0); 16 $this->conn = mysql_connect($host, $dbUser, $dbPwd, true); 17 if(!$this->conn) die("数据库系统连接失败！"); 18 mysql_query("set names ".$charset, $this->conn); 19 mysql_select_db($dbName, $this->conn) or die("数据库连接失败！"); 20 $this->host = $host; 21 $this->dbName = $dbName; 22 } 23 24 //获取数据库所有表名 25 public function getTableNames () { 26 $tables = array(); 27 $result = mysql_list_tables($this->dbName, $this->conn); 28 if(!$result) die('MySQL Error: ' . mysql_error()); 29 while($row = mysql_fetch_row($result)) { 30 $tables[] = $row[0]; 31 } 32 return $tables; 33 } 34 35 //获取数据库表的字段信息 36 public function getFieldsByTable ($table) { 37 $fields = array(); 38 $str = ''; 39 $res = mysql_query("SHOW CREATE TABLE `{$table}`", $this->conn); 40 if(!$res) die('MySQL Error: ' . mysql_error()); 41 while($rows = mysql_fetch_assoc($res)) { 42 $str = str_replace("CREATE TABLE `{$table}` (", "", $rows['Create Table']);//DROP TABLE IF EXISTS `{$table}`n 43 $str = "--n-- Table structure for table `{$table}`n--nnCREATE TABLE IF NOT EXISTS `{$table}` ( ".$str; 44 $str = str_replace(",", ", ", $str); 45 $str = str_replace("`) ) ENGINE=InnoDB ", "`)n ) ENGINE=InnoDB ", $str); 46 $str .=";nn"; 47 //$str = $str.";nn--n-- Dumping data for table `{$table}`n--nn"; 48 $fields[$rows['Table']] = $str; 49 } 50 return $fields; 51 } 52 53 //获取表中的数据 54 public function getDataByTable($table) { 55 $data = array(); 56 $str = ''; 57 $res = mysql_query("SELECT * FROM `{$table}`", $this->conn); 58 if(!$res) die('MySQL Error: ' . mysql_error()); 59 while($rows = mysql_fetch_assoc($res)) { 60 if(!empty($rows)) { 61 $data[] = $rows; 62 } 63 } 64 $keys = array_keys($data[0]); 65 foreach ($keys as $k=>$v) { 66 $keys[$k] = '`'.$v.'`'; 67 } 68 $key = join(', ', $keys); 69 $str = "INSERT INTO `{$table}` ({$key}) VALUESn"; 70 foreach ($data as $k=>$v) { 71 $str.="("; 72 while (list($key, $val) = each($v)) { 73 if(!is_numeric($val)) { 74 $str.= "'".$val."', "; 75 } else { 76 $str.= $val.', '; 77 } 78 } 79 $str = substr($str, 0, -2);// 后边有空格 所以从-2 开始截取 80 if($k+1 == count($data)) { 81 $str.=");nn-- --------------------------------------------------------nn"; 82 } else { 83 $str.="),n"; 84 } 85 } 86 return $str; 87 } 88 89 //备份数据库 90 public function getBackUpDataByTable ($tables, $path='', $fileName = 'seocheck', $subsection = '2') { 91 if(empty($tables)) $this->_showMsg('未能指定要备份的表!!!', true); 92 $page = 0;//卷数 93 $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/'.$fileName.'Demo/' : $path; 94 if(!file_exists($path)) { 95 mkdir($path, 0777, true); 96 } 97 $mysql_info = $this->_retrieve(); 98 $fieldsByTable = array(); 99 if(is_array($tables)) {100 $this->_showMsg('开始备份,数据正在初始化中,请勿关闭浏览器...');101 $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $mysql_info, $method="ab+");102 if($fw !== false) {103 $this->_showMsg('备份数据库基本信息成功。。。');104 }105 foreach ($tables as $table) {106 $tableInfo = $this->getFieldsByTable($table);107 if(!empty($tableInfo)) {108 $this->_showMsg('获取表['.$table.']结构成功。。。');109 $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $tableInfo[$table], $method="ab+");110 if($fw === false) {111 $this->_showMsg('备份表['.$table.']结构失败。。。', true);112 } else {113 $this->_showMsg('备份表['.$table.']结构成功,开始获取数据。。。');114 };115 } else {116 $this->_showMsg('获取数据库['.$this->dbName.']表结构失败,请稍后再试！。。。', true);117 }118 $this->_insertSqlByTableForAll($path, $table, $subsection);119 }120 } else {121 $this->_showMsg('开始备份,数据正在初始化中,请勿关闭浏览器...');122 $tableInfo = $this->getFieldsByTable($tables);123 if(!empty($tableInfo)) {124 $this->_showMsg('获取表['.$tables.']结构成功。。。');125 $fw = $this->writeFileByBackUpData($path.$this->dbName.'_'.$tables.'_table.sql', $mysql_info.$tableInfo[$tables]);126 if($fw === false) {127 $this->_showMsg('备份表['.$tables.']结构失败。。。', true);128 } else {129 $this->_showMsg('备份表['.$tables.']结构成功,开始获取数据。。。');130 }131 } else {132 $this->_showMsg('获取表['.$tables.']结构失败,请稍后再试！。。。', true);133 }134 $res = $this->_insertSqlByTableForAll($path, $tables, $subsection);135 }136 }137 138 //数据库基本信息139 private function _retrieve() {140 $backUp = '';141 $backUp .= '--' . "n";142 $backUp .= '-- MySQL database dump' . "n";143 $backUp .= '-- Created by DbBackUp class, Power By chujiu. ' . "n";144 $backUp .= '--' . "n";145 $backUp .= '-- 主机: ' . $this->host . "n";146 $backUp .= '-- 生成日期: ' . date ( 'Y' ) . ' 年 ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . "n";147 $backUp .= '-- MySQL版本: ' . mysql_get_server_info () . "n";148 $backUp .= '-- PHP 版本: ' . phpversion () . "n";149 $backUp .= "nn";150 $backUp .= "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';n";151 $backUp .= "SET time_zone = '+00:00';nn";152 $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;n";153 $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;n";154 $backUp .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;n";155 $backUp .= "/*!40101 SET NAMES utf8*/;nn";156 $backUp .= "--n-- Database: `{$this->dbName}`n--nn-- --------------------------------------------------------nn";157 return $backUp;158 }159 160 /**161 * 插入单条记录162 *163 * @param string $row164 */165 private function _insertSql($row, $table) {166 // sql字段逗号分割167 $insert = '';168 $insert .= "INSERT INTO `" . $table . "` VALUES(";169 foreach($row as $key=>$val) {170 $insert .= "'".$val."',";171 }172 $insert = substr($insert, 0 ,-1);173 $insert .= ");" . "n";174 return $insert;175 }176 177 /**178 * 生成一个表的inser语句179 * @param string $table180 * @param string $subsection 分卷大小181 */182 private function _insertSqlByTableForAll($path, $table, $subsection) {183 $i = 0;184 $insertSqlByTable = '';185 $res = mysql_query("SELECT * FROM `{$table}`", $this->conn);186 if(!$res) die('MySQL Error: ' . mysql_error());187 while($rows = mysql_fetch_assoc($res)) {188 $insertSqlByTable .= $this->_insertSql($rows, $table);189 $size = strlen($insertSqlByTable);190 if($size > $subsection*1024*1024) {191 $fw = $this->writeFileByBackUpData($path.$table.$i.$this->tag.'.sql', $insertSqlByTable);192 if($fw === false) $this->_showMsg('数据库表['.$table.'],卷 '.$i.' 写入文件失败,请稍后再试！！！',true);193 $this->_showMsg('数据库表['.$table.'],卷 '.$i.' 备份成功！备份文件:[ '.$path.$table.$i.$this->tag.'.sql ]');194 $insertSqlByTable = '';195 $i+=1;196 }197 }198 // insertSqlByTable大小不够分卷大小199 if ($insertSqlByTable != "") {200 $fw = $this->writeFileByBackUpData($path.$table.$this->tag.'.sql', $insertSqlByTable);201 if($fw === false) $this->_showMsg('数据库表['.$table.']写入文件失败,请稍后再试！！！备份文件:[ '.$path.$table.$this->tag.'.sql ]',true);202 $this->_showMsg('数据库表['.$table.'] 备份成功！备份文件:[ '.$path.$table.$this->tag.'.sql ]');203 }204 $this->_showMsg('数据库表['.$table.']全部备份成功！');205 }206 207 // 写入文件208 public function writeFileByBackUpData($fileName, $data, $method="rb+", $iflock=1, $check=1, $chmod=1){209 $check && @strpos($fileName, '..')!==false && exit('Forbidden');210 @touch($fileName);211 $handle = @fopen($fileName, $method);212 if($iflock) {213 @flock($handle,LOCK_EX);214 }215 $fw = @fwrite($handle,$data);216 if($method == "rb+") ftruncate($handle, strlen($data));217 fclose($handle);218 $chmod && @chmod($fileName,0777);219 return $fw;220 }221 222 /**223 * path: 生成压缩包的路径224 * fileName : 要压缩的文件名 通常和path 同一目录225 */226 public function createZipByBackUpFile($path) {227 $db_base_files = $this->getFileByBackUpDir($path);228 if(!empty($db_base_files)) {229 $zip = new ZipArchive;230 if($zip->open($path.$this->dbName.date('Ymd').'.zip', ZipArchive::CREATE | ZIPARCHIVE::OVERWRITE) !== true) 231 die ("cannot open".$this->dbName.date('Ymd')."zip for writing.");232 foreach ($db_base_files as $key => $value) {233 if(is_file($value)) {234 $file_name = basename($value);235 $info[] = $zip->addFile($value, $file_name);// 避免压缩包里有文件的路径236 }237 }238 $zip->close();239 if(file_exists($path.$this->dbName.date('Ymd').'.zip'))240 foreach ($db_base_files as $val) {241 unlink($val);242 }243 if(count(array_filter($info)) > 0) return true;244 }245 return false;246 }247 248 //获取文件249 public function getFileByBackUpDir($path) {250 $info = array();251 $db_base_files = array();252 if( @file_exists($path) && is_dir($path) ) {253 if ($dh = opendir($path)) {254 while (($file = readdir($dh)) !== false) {255 if($file != '.' && $file != '..') {256 if( strripos($file, 'seocheck') !== false ) {257 $db_base_files[] = $path.$file;258 }259 }260 }261 closedir($dh);262 }263 }264 return $db_base_files;265 }266 267 /**268 * @path: 生成压缩包的路径269 * @fileName : 要解压的文件名 默认解压到path 目录270 */271 public function uncompressZip($path, $zipName) {272 $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/' : $path;273 $zip = new ZipArchive;274 if ($zip->open($path.$zipName) === TRUE) {275 $zip->extractTo($path);276 $zip->close();277 return true;278 } else {279 return false;280 }281 }282 283 //导入数据库284 public function importingDataBySqlFile () {285 286 }287 288 // 及时输出信息289 private function _showMsg($msg,$err=false){290 if($err === true) {291 echo "<p style='font-size:14px;'><span style='color:red;'>ERROR: --- " . $msg . "</span></p>";exit;292 }293 echo "<p style='font-size:14px;'><span style='color:green;'>OK: --- " . $msg . "</span></p>";294 }295 296 // 锁定数据库，以免备份或导入时出错297 private function lock($table, $op = "WRITE") {298 if (mysql_query ( "lock tables " . $table . " " . $op ))299 return true;300 else301 return false;302 }303 304 // 解锁305 private function unlock() {306 if (mysql_query ( "unlock tables" ))307 return true;308 else309 return false;310 }311 312 // 析构313 public function __destruct() {314 if($this->conn){315 mysql_query ( "unlock tables", $this->conn );316 mysql_close ( $this->conn );317 }318 }319 }320 ?>