php实例分享之mysql数据备份

发布:smiling　来源: PHP粉丝网　添加日期：2020-12-25 16:43:53　浏览: 评论：0　

本代码实现了表结构和数据完全分开，默认有一个文件会记录所有表的结构，然后表中数据的备份如果超过分卷的大小则会分成多个文件，不然则一个文件。

备份:表结构和数据完全分开，默认有一个文件会记录所有表的结构，然后表中数据的备份如果超过分卷的大小则会分成多个文件，不然则一个文件，参考了别人的代码，不过写的嘛，差强人意，以后慢慢改吧。。。

代码如下：

<?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;

 }

 //获取表中的数据

 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(!emptyempty($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(emptyempty($tables)) $this->_showMsg('未能指定要备份的表!!!', true);

 $page = 0;//卷数

 $path = emptyempty($path) ? $_SERVER['DOCUMENT_ROOT'].'/core/Runtime/Data/'.$fileName.'Demo/' : $path;

 if(!file_exists($path)) {

 mkdir($path, 0777, true);

 }

 $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(!emptyempty($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(!emptyempty($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(!emptyempty($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 = emptyempty($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 );

 }

 }

}

?>