php实例分享之mysql数据备份
备份:表结构和数据完全分开，默认有一个文件会记录所有表的结构，然后表中数据的备份 如果超过分卷的大小则会分成多个文件，不然则一个文件，参考了别人的代码，不过写的嘛，差强 人意，以后慢慢改吧。。。
代码如下：
<?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(!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);
}
$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 "<p style='font-size:14px;'><span style='color:red;'>error: --- " . $msg . "</span></p>";exit;
}
echo "<p style='font-size:14px;'><span style='color:green;'>ok: --- " . $msg . "</span></p>";
}
// 锁定数据库，以免备份或导入时出错
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 );
}
}
}
?>