自制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+测试均成功。