自制PHP数据库备份还原类，PDO/MySQLi/MySQL三种方式通用
<?php
/*
*数据库备份还原
*作者：七歌工作室
*最后更新：2018年12月22日
*/
namespace Sevstudio;
class MySQLHelper{
public $SucBackUpFilePaths; //记录备份成功的文件名集合
//构造函数
function __construct($param){
$this->Host = empty($param['host']) ? 'localhost' : $param['host'];
$this->Port = empty($param['port']) ? 3306 : intval($param['port']);
$this->Uid = empty($param['uid']) ? '' : $param['uid'];
$this->Pwd = empty($param['pwd']) ? '' : $param['pwd'];
$this->Databases = empty($param['databases']) ? '' : $param['databases'];
$this->Charset = empty($param['charset']) ? 'utf8' : $param['charset'];
$this->FileExtention = empty($param['extention']) ? '.sql' : $param['extention']; //备份文件后缀名 .开头
$this->UseSection = !isset($param['usesection']) ? false : $param['usesection']; //默认不使用分卷
$this->SectionSize = !isset($param['sectionsize']) ? 5*1024*1024 : floatval($param['sectionsize']); //分卷大小，默认5M
//参数校验
if($this->Uid == ''){ $this->throwError('uid was needed'); }
if($this->Databases == ''){ $this->throwError('databases was needed'); }
if(!is_bool($this->UseSection)){ $this->throwError('usesection must be boolean'); }
$this->init();
}
//初始化
private function init(){
//规范备份文件后缀名
if(substr($this->FileExtention,0,1) != '.'){
$this->FileExtention = '.'.$this->FileExtention;
}
$this->BackFileName = 'data_'.sha1(uniqid().rand(1000,9999)); //备份文件名，不含分卷和后缀
//分卷信息初始化
$this->SectionIndex = 1;
$this->PageCount = 0; //累计单个备份文件写入语句的次数
$this->PageStep = 5; //单个文件每写入N次语句则开始判断分卷大小
if(extension_loaded('pdo') && extension_loaded('pdo_mysql')){
define("SEVSTUDIO_BACK_METHOD","PDO");
}else if(extension_loaded('mysqli')){
define("SEVSTUDIO_BACK_METHOD","MYSQLI");
}else if(extension_loaded('mysql')){
define("SEVSTUDIO_BACK_METHOD","MYSQL");
}else{
$this->throwError('No backup plug-ins are available');
}
}
//错误处理
private function throwError($msg){
die($msg);
}
//连接数据库
public function connect(){
try{
if(SEVSTUDIO_BACK_METHOD == 'PDO') {
$this->Con = new \PDO(
"mysql:host={$this->Host};port={$this->Port};dbname={$this->Databases}",
$this->Uid,
$this->Pwd,
array(\PDO::MYSQL_ATTR_INIT_COMMAND => "set names '{$this->Charset}'")
);
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
$this->Con = mysqli_connect($this->Host,$this->Uid,$this->Pwd,$this->Databases,$this->Port);
if(!$this->Con){ $this->throwError('MySQL connected failed'); }
mysqli_query($this->Con,"set names '{$this->Charset}'");
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
$this->Con = mysql_connect($this->Host.':'.$this->Port,$this->Uid,$this->Pwd);
if($this->Con === false){ $this->throwError('MySQL connected failed'); }
if(mysql_select_db($this->Databases,$this->Con) === false) {
$this->throwError('select databases failed');
}
if(mysql_query("set names '{$this->Charset}'") === false){
$this->throwError('MySQL connected failed');
}
}
}catch(\Exception $e){
$this->throwError('MySQL connected failed');
}
}
//获取数据库版本
public function GetVersion(){
if(empty($this->Con)){ $this->connect(); }
$version = '';
$sql = 'select version()';
if(SEVSTUDIO_BACK_METHOD == 'PDO') {
$result = $this->Con->query($sql);
$version = $result->fetchColumn();
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
$result = mysqli_query($this->Con,$sql);
$tmp = mysqli_fetch_array($result);
$version = $tmp[0];
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
$result = mysql_query($sql,$this->Con);
$tmp = mysql_fetch_array($result);
$version = $tmp[0];
}
return $version;
}
/*
*执行备份入口
*dirPath string 备份文件保存路径(文件夹)
*return boolean 失败：false，成功：true
*/
public function BackUp($dirPath = 'backup'){
//备份文件夹处理
$_dirpath = $this->fixPath($dirPath);
if(!file_exists($_dirpath)){
if(!mkdir($_dirpath,0777,true)){ $this->throwError('make save directory failed'); }
}
if(substr($_dirpath,strlen($_dirpath)-1) == DIRECTORY_SEPARATOR){
$this->BackUpSavePath = substr($_dirpath,0,strlen($_dirpath)-1);
}else{
$this->BackUpSavePath = $_dirpath;
}
$this->connect();
if(SEVSTUDIO_BACK_METHOD == 'PDO') {
return $this->BackUp_PDO($dirPath);
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
return $this->BackUp_MySQLi($dirPath);
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
return $this->BackUp_MySQL($dirPath);
}
}
///PDO方式备份
private function BackUp_PDO(){
$result = $this->Con->query('show tables');
if($result === false){ return false; }
$content = "set names '{$this->Charset}';".PHP_EOL;
$this->SaveBackFile($content);//写出
//表名循环
while($table = $result->fetchColumn()){
//表结构
$content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
$tableCreate = $this->Con->query("show create table `$table`");
$content.= $tableCreate->fetchColumn(1).';'.PHP_EOL . PHP_EOL;
$this->SaveBackFile($content);//写出
//表数据
$data = $this->Con->query("select * from `$table`");
while($row = $data->fetch(\PDO::FETCH_ASSOC)){
$content.= $this->GetOneInsertQuery($table,$row);
$this->SaveBackFile($content);//写出
}
}
return true;
}
//MySQLi方式备份
private function BackUp_MySQLi(){
$result = mysqli_query($this->Con,'show tables');
if($result === false){ return false; }
$content = "set names '{$this->Charset}';".PHP_EOL;
$this->SaveBackFile($content);//写出
//表名循环
while($tableInfo = mysqli_fetch_array($result)){
$table = $tableInfo[0];
//表结构
$content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
$tableCreate = mysqli_query($this->Con,"show create table `$table`");
$tableCreate = mysqli_fetch_assoc($tableCreate);
$content.= $tableCreate['Create Table'].';'.PHP_EOL . PHP_EOL;
$this->SaveBackFile($content);//写出
//表数据
$data = mysqli_query($this->Con,"select * from `$table`");
while($row = mysqli_fetch_assoc($data)){
$content.= $this->GetOneInsertQuery($table,$row);
$this->SaveBackFile($content);//写出
}
}
return true;
}
//MySQL方式备份
private function BackUp_MySQL(){
$result = mysql_query('show tables',$this->Con);
if($result === false){ return false; }
$content = "set names '{$this->Charset}';".PHP_EOL;
$this->SaveBackFile($content);//写出
//表名循环
while($tableInfo = mysql_fetch_array($result)){
$table = $tableInfo[0];
//表结构
$content.= PHP_EOL . "DROP TABLE IF EXISTS `{$table}`;".PHP_EOL;
$tableCreate = mysql_query("show create table `$table`",$this->Con);
$tableCreate = mysql_fetch_array($tableCreate);
$content.= $tableCreate['Create Table'].';'.PHP_EOL . PHP_EOL;
$this->SaveBackFile($content);//写出
//表数据
$data = mysql_query("select * from `$table`",$this->Con);
while($row = mysql_fetch_assoc($data)){
$content.= $this->GetOneInsertQuery($table,$row);
$this->SaveBackFile($content);//写出
}
}
return true;
}
//获取一条数据的insert语句
private function GetOneInsertQuery($tableName,$row){
$keys = array_keys($row);
$keys = array_map('addslashes',$keys);
$keys = join('`,`',$keys);
$keys = "`".$keys."`";
$vals = array_values($row);
$vals = array_map('addslashes',$vals);
$vals = join("','",$vals);
$vals = "'".$vals."'";
return "INSERT INTO `$tableName`($keys) VALUES($vals);".PHP_EOL;
}
//保存备份数据到文件
private function SaveBackFile(&$content){
$fileName = $this->BackUpSavePath. DIRECTORY_SEPARATOR .$this->BackFileName.'_'.$this->SectionIndex.$this->FileExtention;
file_put_contents($fileName,$content,FILE_APPEND);
$content = '';
$this->PageCount++;
$this->SucBackUpFilePaths[] = $this->BackFileName.'_'.$this->SectionIndex;
//拒绝每写入一次语句就判断一次分卷大小
if($this->UseSection && $this->PageCount >= $this->PageStep){
$this->PageCount = 0;
try{
$size = filesize($fileName);
if($size > $this->SectionSize){
$this->SectionIndex++;
}
}catch(\Exception $e){
//
}
clearstatcache();
}
}
//路径标准化
private function fixPath($str){
$t = str_replace('\\',DIRECTORY_SEPARATOR,$str);
return str_replace('/',DIRECTORY_SEPARATOR,$t);
}
/*
*执行还原入口
*filepath string 还原文件路径或文件夹路径
*deletefilewhilesuccess boolean 备份成功后是否删除备份文件
*return 失败：false，成功：执行成功及失败语句数量
*/
public function Restore($filepath = 'backup',$deletefilewhilesuccess = false){
$_filepath = $this->fixPath($filepath);
if(!file_exists($_filepath)){ return false; }
$this->RestoreResult = array(
'success' => 0, //成功执行命令数
'error' => 0, //失败...
);
if(is_dir($_filepath)){
//还原目录
//去掉末尾路径分隔符
if(substr($_filepath,strlen($_filepath)-1) == DIRECTORY_SEPARATOR){
$_filepath = substr($_filepath,0,strlen($_filepath)-1);
}
//读取目录下.sql文件
$files = array();
$hd = opendir($_filepath);
while($o = readdir($hd)){
if($o == '.' || $o == '..' || strpos($o,'.') === false){ continue; }
$type = substr($o,strripos($o,'.'));
if(strtolower($type) != $this->FileExtention){ continue; }
$files[] = $o;
}
closedir($hd);
if(count($files) < 1){ return false; }
//开始处理
if($this->UseSection){
//使用分卷
$tmpstr = implode('-',$files);
$result = preg_match_all('/data_[a-z0-9]{30,}_\d{1,}\\'.$this->FileExtention.'/i',$tmpstr,$arr,PREG_PATTERN_ORDER);
if($result === false || count($arr) < 1){
return false;//没有符合分卷规则的备份
}
//索引排序，导入时需要按顺序导入
$real = array();
$rd = ''; //随机标记统一，多个随机标记代表执行了多次备份的文件放到了一起。
foreach($arr[0] as $o){
$info = explode('_',$o);
$rand = $info[1];
if($rd == ''){
$rd = $rand;
}else if($rd != $rand){
/*存在多个随机标记
代表执行了多次备份的文件放到了一起
或其他数据库的备份文件也在一起
*/
$this->throwError('Backup files with different databases');
}
$index = str_replace($this->FileExtention,'',$info[2]);
$real[$index] = $o;
}
foreach($real as $x){
$this->RestoreSingle($_filepath . DIRECTORY_SEPARATOR . $x);
}
}else{
//不使用分卷，所有.sql文件
foreach($files as $o){
$this->RestoreSingle($_filepath . DIRECTORY_SEPARATOR . $o);
}
}
}else{
//还原单个文件
$this->RestoreSingle($_filepath);
}
if($deletefilewhilesuccess && !empty($this->SuccessFiles)){
foreach($this->SuccessFiles as $o){
@unlink($o);
}
}
return $this->RestoreResult;
}
//还原一个分卷
private function RestoreSingle($filename){
$this->connect();
$content = file_get_contents($filename);
$rows = explode(';'.PHP_EOL,$content);
foreach($rows as $r){
if(trim($r) == ''){ continue; }
$res = $this->Query($r);
if($res === false){
$this->RestoreResult['error']++;
}else{
$this->RestoreResult['success']++;
}
}
if($this->RestoreResult['error'] < 1){
//成功时，记录成功文件路径，用于删除备份文件
$this->SuccessFiles[] = $filename;
}
}
//执行query命令
private function Query($sql){
if(SEVSTUDIO_BACK_METHOD == 'PDO') {
return $this->Con->query($sql);
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQLI'){
return mysqli_query($this->Con,$sql);
}else if(SEVSTUDIO_BACK_METHOD == 'MYSQL'){
return mysql_query($sql,$this->Con);
}
}
}使用方法，
$pm = array(
'host' => '127.0.0.1',
'uid' => 'root',
'pwd' => 'root',
'databases' => 'python_caiji',
'port' => 3306,
'charset' => 'utf8',
'usesection' => true,
'sectionsize' => 50*1024, //使用分卷备份，分卷大小
'extention' => '.sev' //自定义备份文件后缀名
);
$hp = new Sevstudio\MySQLHelper($pm);
//数据库备份到python_caiji目录，目录不存在会自动创建
//$result = $hp->BackUp('python_caiji');
//还原python_caiji文件夹下的备份文件，也可以写单个sql文件名路径
$result = $hp->Restore('python_caiji',true);
//$result = $hp->GetVersion();
if($result === false){
die('error');
}else{
var_dump($result);
}同时支持pdo_mysql，mysqli，mysql方式，会自动选择备份还原方式
php5和php7，mysql5.5+测试均成功。