mysql data backup shared by php instance
- 2021-06-28 11:23:21
- OfStack
Backup: The table structure is completely separate from the data. By default, one file will record the structure of all tables. Then the backup of the data in the table will be divided into multiple files if it exceeds the size of the volume. Otherwise, one file will refer to someone else's code, but what you write is not satisfactory. Change it slowly later.
The code is as follows:
<?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;
}
/**
* 生成1个表的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 同1目录
*/
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 );
}
}
}
?>