php实例之mysql数据备份
程序员文章站
2024-01-29 09:46:16
...
本代码实现了表结构和数据完全分开,默认有一个文件会记录所有表的结构,然后表中数据的备份 如果超过分卷的大小则会分成多个文件,不然则一个文件
备份:表结构和数据完全分开,默认有一个文件会记录所有表的结构,然后表中数据的备份 如果超过分卷的大小则会分成多个文件,不然则一个文件,参考了别人的代码,不过写的嘛,差强 人意,以后慢慢改吧。。。代码如下:
php /* * Created on 2014 * Link for 527891885@qq.com * This is seocheck backup class */ class DbBackUp { private $conn; private $dbName; private $host; private $tag = '_b'; //构造方法 链接数据库 public function __construct($host='localhost', $dbUser='root', $dbPwd='', $dbName="seocheck", $charset='utf8') { @ob_start(); @set_time_limit(0); $this->conn = mysql_connect($host, $dbUser, $dbPwd, true); if(!$this->conn) die("数据库系统连接失败!"); mysql_query("set names ".$charset, $this->conn); mysql_select_db($dbName, $this->conn) or die("数据库连接失败!"); $this->host = $host; $this->dbName = $dbName; } //获取数据库所有表名 public function getTableNames () { $tables = array(); $result = mysql_list_tables($this->dbName, $this->conn); if(!$result) die('MySQL Error: ' . mysql_error()); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } return $tables; } //获取数据库表的字段信息 public function getFieldsByTable ($table) { $fields = array(); $str = ''; $res = mysql_query("SHOW CREATE TABLE `{$table}`", $this->conn); if(!$res) die('MySQL Error: ' . mysql_error()); while($rows = mysql_fetch_assoc($res)) { $str = str_replace("CREATE TABLE `{$table}` (", "", $rows['Create Table']);//DROP TABLE IF EXISTS `{$table}`\n $str = "--\n-- Table structure for table `{$table}`\n--\n\nCREATE TABLE IF NOT EXISTS `{$table}` ( ".$str; $str = str_replace(",", ", ", $str); $str = str_replace("`) ) ENGINE=InnoDB ", "`)\n ) ENGINE=InnoDB ", $str); $str .=";\n\n"; //$str = $str.";\n\n--\n-- Dumping data for table `{$table}`\n--\n\n"; $fields[$rows['Table']] = $str; } return $fields; } www.jbxue.com //获取表中的数据 public function getDataByTable($table) { $data = array(); $str = ''; $res = mysql_query("SELECT * FROM `{$table}`", $this->conn); if(!$res) die('MySQL Error: ' . mysql_error()); while($rows = mysql_fetch_assoc($res)) { if(!empty($rows)) { $data[] = $rows; } } $keys = array_keys($data[0]); foreach ($keys as $k=>$v) { $keys[$k] = '`'.$v.'`'; } $key = join(', ', $keys); $str = "INSERT INTO `{$table}` ({$key}) VALUES\n"; foreach ($data as $k=>$v) { $str.="("; while (list($key, $val) = each($v)) { if(!is_numeric($val)) { $str.= "'".$val."', "; } else { $str.= $val.', '; } } $str = substr($str, 0, -2);// 后边有空格 所以从-2 开始截取 if($k+1 == count($data)) { $str.=");\n\n-- --------------------------------------------------------\n\n"; } else { $str.="),\n"; } } return $str; } //备份数据库 public function getBackUpDataByTable ($tables, $path='', $fileName = 'seocheck', $subsection = '2') { if(empty($tables)) $this->_showMsg('未能指定要备份的表!!!', true); $page = 0;//卷数 $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/'.$fileName.'Demo/' : $path; if(!file_exists($path)) { mkdir($path, 0777, true); } www.jbxue.com $mysql_info = $this->_retrieve(); $fieldsByTable = array(); if(is_array($tables)) { $this->_showMsg('开始备份,数据正在初始化中,请勿关闭浏览器...'); $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $mysql_info, $method="ab+"); if($fw !== false) { $this->_showMsg('备份数据库基本信息成功。。。'); } foreach ($tables as $table) { $tableInfo = $this->getFieldsByTable($table); if(!empty($tableInfo)) { $this->_showMsg('获取表['.$table.']结构成功。。。'); $fw = $this->writeFileByBackUpData($path.$this->dbName.'_table.sql', $tableInfo[$table], $method="ab+"); if($fw === false) { $this->_showMsg('备份表['.$table.']结构失败。。。', true); } else { $this->_showMsg('备份表['.$table.']结构成功,开始获取数据。。。'); }; } else { $this->_showMsg('获取数据库['.$this->dbName.']表结构失败,请稍后再试!。。。', true); } $this->_insertSqlByTableForAll($path, $table, $subsection); } } else { $this->_showMsg('开始备份,数据正在初始化中,请勿关闭浏览器...'); $tableInfo = $this->getFieldsByTable($tables); if(!empty($tableInfo)) { $this->_showMsg('获取表['.$tables.']结构成功。。。'); $fw = $this->writeFileByBackUpData($path.$this->dbName.'_'.$tables.'_table.sql', $mysql_info.$tableInfo[$tables]); if($fw === false) { $this->_showMsg('备份表['.$tables.']结构失败。。。', true); } else { $this->_showMsg('备份表['.$tables.']结构成功,开始获取数据。。。'); } } else { $this->_showMsg('获取表['.$tables.']结构失败,请稍后再试!。。。', true); } $res = $this->_insertSqlByTableForAll($path, $tables, $subsection); } } //数据库基本信息 private function _retrieve() { $backUp = ''; $backUp .= '--' . "\n"; $backUp .= '-- MySQL database dump' . "\n"; $backUp .= '-- Created by DbBackUp class, Power By chujiu. ' . "\n"; $backUp .= '--' . "\n"; $backUp .= '-- 主机: ' . $this->host . "\n"; $backUp .= '-- 生成日期: ' . date ( 'Y' ) . ' 年 ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . "\n"; $backUp .= '-- MySQL版本: ' . mysql_get_server_info () . "\n"; $backUp .= '-- PHP 版本: ' . phpversion () . "\n"; $backUp .= "\n\n"; $backUp .= "SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n"; $backUp .= "SET time_zone = '+00:00';\n\n"; $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;\n"; $backUp .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;\n"; $backUp .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;\n"; $backUp .= "/*!40101 SET NAMES utf8*/;\n\n"; $backUp .= "--\n-- Database: `{$this->dbName}`\n--\n\n-- --------------------------------------------------------\n\n"; return $backUp; } /** * 插入单条记录 * * @param string $row */ private function _insertSql($row, $table) { // sql字段逗号分割 $insert = ''; $insert .= "INSERT INTO `" . $table . "` VALUES("; foreach($row as $key=>$val) { $insert .= "'".$val."',"; } $insert = substr($insert, 0 ,-1); $insert .= ");" . "\n"; return $insert; } /** * 生成一个表的inser语句 * @param string $table * @param string $subsection 分卷大小 */ private function _insertSqlByTableForAll($path, $table, $subsection) { $i = 0; $insertSqlByTable = ''; $res = mysql_query("SELECT * FROM `{$table}`", $this->conn); if(!$res) die('MySQL Error: ' . mysql_error()); while($rows = mysql_fetch_assoc($res)) { $insertSqlByTable .= $this->_insertSql($rows, $table); $size = strlen($insertSqlByTable); if($size > $subsection*1024*1024) { $fw = $this->writeFileByBackUpData($path.$table.$i.$this->tag.'.sql', $insertSqlByTable); if($fw === false) $this->_showMsg('数据库表['.$table.'],卷 '.$i.' 写入文件失败,请稍后再试!!!',true); $this->_showMsg('数据库表['.$table.'],卷 '.$i.' 备份成功!备份文件:[ '.$path.$table.$i.$this->tag.'.sql ]'); $insertSqlByTable = ''; $i+=1; } } // insertSqlByTable大小不够分卷大小 if ($insertSqlByTable != "") { $fw = $this->writeFileByBackUpData($path.$table.$this->tag.'.sql', $insertSqlByTable); if($fw === false) $this->_showMsg('数据库表['.$table.']写入文件失败,请稍后再试!!!备份文件:[ '.$path.$table.$this->tag.'.sql ]',true); $this->_showMsg('数据库表['.$table.'] 备份成功!备份文件:[ '.$path.$table.$this->tag.'.sql ]'); } $this->_showMsg('数据库表['.$table.']全部备份成功!'); } // 写入文件 public function writeFileByBackUpData($fileName, $data, $method="rb+", $iflock=1, $check=1, $chmod=1){ $check && @strpos($fileName, '..')!==false && exit('Forbidden'); @touch($fileName); $handle = @fopen($fileName, $method); if($iflock) { @flock($handle,LOCK_EX); } $fw = @fwrite($handle,$data); if($method == "rb+") ftruncate($handle, strlen($data)); fclose($handle); $chmod && @chmod($fileName,0777); return $fw; } /** * path: 生成压缩包的路径 * fileName : 要压缩的文件名 通常和path 同一目录 */ public function createZipByBackUpFile($path) { $db_base_files = $this->getFileByBackUpDir($path); if(!empty($db_base_files)) { $zip = new ZipArchive; if($zip->open($path.$this->dbName.date('Ymd').'.zip', ZipArchive::CREATE | ZIPARCHIVE::OVERWRITE) !== true) die ("cannot open".$this->dbName.date('Ymd')."zip for writing."); foreach ($db_base_files as $key => $value) { if(is_file($value)) { $file_name = basename($value); $info[] = $zip->addFile($value, $file_name);// 避免压缩包里有文件的路径 } } $zip->close(); if(file_exists($path.$this->dbName.date('Ymd').'.zip')) foreach ($db_base_files as $val) { unlink($val); } if(count(array_filter($info)) > 0) return true; } return false; } //获取文件 public function getFileByBackUpDir($path) { $info = array(); $db_base_files = array(); if( @file_exists($path) && is_dir($path) ) { if ($dh = opendir($path)) { while (($file = readdir($dh)) !== false) { if($file != '.' && $file != '..') { if( strripos($file, 'seocheck') !== false ) { $db_base_files[] = $path.$file; } } } closedir($dh); } } return $db_base_files; } /** * @path: 生成压缩包的路径 * @fileName : 要解压的文件名 默认解压到path 目录 */ public function uncompressZip($path, $zipName) { $path = empty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/' : $path; $zip = new ZipArchive; if ($zip->open($path.$zipName) === TRUE) { $zip->extractTo($path); $zip->close(); return true; } else { return false; } } //导入数据库 public function importingDataBySqlFile () { } // 及时输出信息 private function _showMsg($msg,$err=false){ if($err === true) { echo "ERROR: --- " . $msg . "
";exit; } echo "OK: --- " . $msg . "
"; } // 锁定数据库,以免备份或导入时出错 private function lock($table, $op = "WRITE") { if (mysql_query ( "lock tables " . $table . " " . $op )) return true; else return false; } // 解锁 private function unlock() { if (mysql_query ( "unlock tables" )) return true; else return false; } // 析构 public function __destruct() { if($this->conn){ mysql_query ( "unlock tables", $this->conn ); mysql_close ( $this->conn ); } } } ?>
上一篇: Oracle数据库提升效率,用3PAR
下一篇: Linux环境下完全删除Oracle