PHP版MySQL超大数据库分卷备份恢复精简版 pdo
PHP版MySQL超大数据库分卷备份恢复精简版 利用 pdo 连接mysql 兼容性更强
<?php header("content-Type: text/html; charset=utf-8");session_start();ini_set('display_errors', 1);ini_set('display_startup_errors', 1);error_reporting(E_ALL);
$dbconfig=empty($_SESSION['dbconfig'])?array('db_host'=>'','db_user'=>'','db_pw'=>'','db_name'=>'',):$_SESSION['dbconfig'];
if(!empty($_POST['db_user']) && !empty($_POST['db_name'])){$dbconfig=$_POST;$_SESSION['dbconfig']=$dbconfig;}
$db_host = $dbconfig['db_host']; //数据库服务器
$db_user = $dbconfig['db_user']; //数据库用户名
$db_pw = $dbconfig['db_pw']; //数据库密码
$db_name = $dbconfig['db_name']; //数据库名
if(empty($db_user)){echo tpl_header().tpl_sitting();exit;}@extract($_GET);
if(!empty($act) && $act=='logout'){$_SESSION['dbconfig']='';message("注销成功!",'?act=');exit;}//注销
$db = new db;$db->connect($db_host, $db_user, $db_pw, $db_name);
if(empty($act))$act='';echo tpl_menu();
if($act=='import'){//导入
if(!empty($dosubmit)){
$fileid = isset($fileid) ? $fileid : 1;$filepath = $pre.$fileid.'.sql';
if(file_exists('./data/'.$filepath)){
$sql = cache_read($filepath,'');sql_execute($sql);$fileid++;
message("数据文件 $filepath 导入成功!","?pre=".$pre."&fileid=".$fileid."&dosubmit=1&act=import");
}else{message("数据库恢复成功!");}
}else{echo tpl_import();exit;}
}else {//导出
if(!empty($dosubmit)){
if(!is_writable('./data/')) message('数据无法备份到服务器!请检查 ./data 目录是否可写。');if(empty($pre))$pre='NULL';
$query = $db->query("SHOW TABLES FROM ".$db_name);$k = 0;foreach($query as $r){$r=array_values($r);if($pre=='NULL' || str_exists($r[0],$pre)){$tables[$k] = $r[0];$k++;}}
$fileid = isset($fileid) ? $fileid : 1;if($fileid==1 && $tables)
{if(!isset($tables) || !is_array($tables)) message('请选择要备份的数据表!');$random = mt_rand(1000, 9999);cache_write('bakup_tables.php', $tables);}
else{if(!$tables = cache_read('bakup_tables.php')) message('请选择要备份的数据表!');}
$sqldump = '';$tableid = isset($tableid) ? $tableid - 1 : 0;
$startfrom = isset($startfrom) ? intval($startfrom) : 0;$tablenumber = count($tables);for($i = $tableid; $i < $tablenumber && strlen($sqldump) < $sizelimit * 1000; $i++)
{$sqldump .= sql_dumptable($tables[$i], $startfrom, strlen($sqldump));$startfrom = 0;}
if(trim($sqldump)){$sqldump = "#xiaogg QQ756663992 Created\n# ----\n\n\n".$sqldump;$tableid = $i;
$filename = $db_name.'_'.date('Ymd').'_'.$random.'_'.$fileid.'.sql';$fileid++;$bakfile = './data/'.$filename;
file_put_contents($bakfile, $sqldump);
message("备份文件 $filename 写入成功!", '?sizelimit='.$sizelimit.'&tableid='.$tableid.'&fileid='.$fileid.'&startfrom='.$startrow.'&random='.$random.'&dosubmit=1');
}else{cache_delete('bakup_tables.php');message('备份完毕!');}exit;
}else{echo tpl_explort();exit;}
}
function tpl_header(){return '<!DOCTYPE html><html><head><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no, minimal-ui"><title>MYSQL精简备份恢复</title>
<style>body{font-family:"宋体";font-size:16px;line-height:20px;padding:0px;color:#333;}a{display: inline-block;padding: 0 12px 1px; line-height: 32px; color: #FFF;text-shadow: 0 -1px 0 rgba(0,0,0,0.25);font-size: 14px;background-color: #abbac3;border: 5px solid #abbac3;cursor: pointer; vertical-align: middle;position: relative;}
form{margin-left:10px;}input,select{width:auto;height: 34px;padding:0px 3px;margin:3px;line-height: 1.42857143;color: #555;background-color: #fff;border: 1px solid #ccc;
-webkit-box-shadow: inset 0 1px 1px rgba(0,0,0,.075);box-shadow: inset 0 1px 1px rgba(0,0,0,.075);
-webkit-transition: border-color ease-in-out .15s, -webkit-box-shadow ease-in-out .15s;
-o-transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s;transition: border-color ease-in-out .15s, box-shadow ease-in-out .15s;}
input[type="submit"]{color: #fff;background-color: #428bca;border-color: #357ebd;padding:5px 10px;}</style></head><body>';}
function tpl_footer(){return '</body></html>';};
function tpl_menu(){$str= tpl_header().'<a href="?act=">导出</a> <a href="?act=import">导入</a> <a href="?act=logout">注销</a><br><br>';return $str;}
function tpl_import(){
$str= '<form action="" method="get">';
$str.= '前缀:'.tpl_pres().'<br>开始卷:<input name="fileid" type="text" value="1"/>';
$str.= '<br><input name="dosubmit" type="hidden" value="1"/><input name="act" type="hidden" value="import"/>';
$str.= '<input type="submit" value="从data目录恢复" /></form><br>';return $str.tpl_footer();
}
function tpl_explort(){
$str= '<form action="" method="get">';
$str.= '数据库前缀:<input name="pre" type="text" value=""/><br>分卷大小:<input name="sizelimit" type="text" value="2048"/>K<br>';
$str.= '<input name="dosubmit" type="hidden" value="1"/><input type="submit" value="备份到data目录" /></form>';return $str.tpl_footer();
}
function tpl_sitting(){
$str= "<form action='' method='post'>服务器主机：<input name='db_host' value='localhost' type='text'/><br/>";
$str.= "数据库用户：<input name='db_user' value='' type='text'/><br/>";
$str.= "数据库密码：<input name='db_pw' value='' type='password'/><br/>";
$str.= "数据库名称：<input name='db_name' value='' type='text'/><br/><input type='submit' value='设置'/></form>";return $str.tpl_footer();
}
function tpl_pres(){
$pres=array();$str='<select name="pre">';
foreach (glob("./data/*_1.sql") as $filename) {
$name=basename($filename);$pre=str_replace('1.sql','',$name);
$str.='<option value="'.$pre.'">'.$pre.'</option>';
}$str.='</select>';return $str;
}
function cache_read($file, $mode = 'i'){$cachefile = './data/'.$file;if(!file_exists($cachefile)) return array();return $mode == 'i' ? include $cachefile : file_get_contents($cachefile);}
function cache_write($file, $string, $type = 'array'){
if(is_array($string)){$type = strtolower($type);
if($type == 'array'){$string = "<?php\n return ".var_export($string,TRUE).";\n?>";}
elseif($type == 'constant'){$data='';foreach($string as $key => $value) $data .= "define('".strtoupper($key)."','".addslashes($value)."');\n";$string = "<?php\n".$data."\n?>";}}
file_put_contents('./data/'.$file, $string);
}
function sql_execute($sql){global $db;$sqls = sql_split($sql);if(is_array($sqls)){foreach($sqls as $sql){if(trim($sql) != '') {$db->query($sql);}}}else{$db->query($sqls);} return true;}
function sql_split($sql) {
global $db_charset, $db;
if($db->version() > '4.1' && $db_charset) {$sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=".$db_charset,$sql);}
$sql = str_replace("\r", "\n", $sql);$ret = array();$num = 0;
$queriesarray = explode(";\n", trim($sql));unset($sql);
foreach($queriesarray as $query) {
$ret[$num] = '';$queries = explode("\n", trim($query));$queries = array_filter($queries);
foreach($queries as $query) {$str1 = substr($query, 0, 1);if($str1 != '#' && $str1 != '-') $ret[$num] .= $query;}$num++;
}return($ret);
}
function str_exists($haystack, $needle){return !(strpos($haystack, $needle) === FALSE);}
function cache_delete($file){return @unlink('./data/'.$file);}
function sql_dumptable($table, $startfrom = 0, $currsize = 0){
global $db, $sizelimit, $startrow;if(!isset($tabledump)) $tabledump = ''; $offset = 100;
if(!$startfrom){
$tabledump = "DROP TABLE IF EXISTS `".$table."`;\n";
$create = array_values($db->get_one("SHOW CREATE TABLE `".$table."`"));
$tabledump .= $create[1].";\n\n";
}
$numrows = $offset;
while($currsize + strlen($tabledump) < $sizelimit * 1000 && $numrows == $offset){
$list = $db->get_all("SELECT * FROM `".$table."` LIMIT $startfrom, $offset");
$numrows = count($list);if($numrows>0){
$comma = [];$tabledump .= "INSERT INTO `".$table."` VALUES";
foreach($list as $rowarr){
foreach($rowarr as $key=>$val){$rowarr[$key]=str_replace("'","\'",$val);}$row=implode("','",$rowarr);
$comma[]="('".$row."')";
}$tabledump .=implode(',',$comma).";\n";}
$startfrom += $offset;
}$startrow = $startfrom;$tabledump .= "\n";return $tabledump;
}
function message($msg,$url_forward='',$ms=1250){echo $msg;if($url_forward){echo '<script type="text/javascript">function redirect(url){window.location=url;}setTimeout("redirect(\''.$url_forward.'\');",'.$ms.');</script>';}exit;}
class db {
public static $PDOStatement = null;public static $config = array();public static $pconnect = false;
public static $error = '';protected static $link;public static $connected = false;public static $numRows = 0;
public static $dbVersion = null; public static $queryStr = '';public static $lastInsertId = null;
public function connect($dbhost, $dbuser, $dbpw, $dbname,$dbprot=3306,$dbtype='mysql',$db_charset='utf8'){
if (!class_exists('PDO')) self::throw_exception("不支持:PDO");if($dbprot<3000)$dbprot=3306;
$dbConfig = array('hostname' => $dbhost,'username' => $dbuser,'password' => $dbpw,'database' => $dbname,'hostport' => $dbprot,'dbms' => $dbtype,'dsn' => $dbtype.":host=".$dbhost.";dbname=".$dbname);
if(empty($dbConfig['hostname'])) self::throw_exception("没有定义数据库配置");
self::$config = $dbConfig;if(empty(self::$config['params'])) self::$config['params'] = array();
if (!isset(self::$link) ) {$configs = self::$config;if(self::$pconnect) {$configs['params'][constant('PDO::ATTR_PERSISTENT')] = true;}
try {self::$link = new PDO( $configs['dsn'], $configs['username'], $configs['password'],$configs['params']);} catch (PDOException $e) { self::throw_exception($e->getMessage());}
if(!self::$link) {self::throw_exception('PDO CONNECT ERROR'); return false;}self::$link->exec('SET NAMES '.$db_charset);
self::$dbVersion = self::$link->getAttribute(constant("PDO::ATTR_SERVER_INFO")); self::$connected = true;unset($configs);
}return self::$link;}
public static function get_all($sql=null) {if($sql != null){self::select($sql);}return self::$PDOStatement->fetchAll(constant('PDO::FETCH_ASSOC'));}
public static function get_one($sql=null) {if($sql != null){self::select($sql);}return self::$PDOStatement->fetch(constant('PDO::FETCH_ASSOC'),constant('PDO::FETCH_ORI_NEXT'));}
public static function query($sql='') {if(self::isMainIps($sql)) {return self::execute($sql);}else{return self::get_all($sql);}}
public static function getLastSql(){$link = self::$link; if ( !$link ) return false; return self::$queryStr;}
public static function getLastInsId(){$link = self::$link; if ( !$link ) return false; return self::$lastInsertId; }
public static function version(){$link = self::$link; if ( !$link ) return false; return self::$dbVersion; }
public static function close() {self::$link = null; }public static function free() {self::$PDOStatement = null;}
public static function select($sql='') {$link = self::$link; if ( !$link ) return false;self::$queryStr = $sql; if ( !empty(self::$PDOStatement) ) self::free();self::$PDOStatement = $link->prepare(self::$queryStr);$bol = self::$PDOStatement->execute();self::haveErrorThrowException(); return $bol;}
public static function execute($sql='') {$link = self::$link;if ( !$link ) return false;self::$queryStr = $sql;if ( !empty(self::$PDOStatement) ) self::free();$result = $link->exec(self::$queryStr);self::haveErrorThrowException();
if ( false === $result) { return false; } else { self::$numRows = $result; self::$lastInsertId = $link->lastInsertId(); return self::$numRows; }}
private static function ismainIps($query) {$queryIps = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|REVOKE|LOCK|UNLOCK'; if (preg_match('/^\s*"?(' . $queryIps . ')\s+/i', $query)) { return true; } return false; }
public static function throw_exception($err){echo 'ERROR:'.$err.'';}
public static function haveErrorThrowException() {$obj = empty(self::$PDOStatement) ? self::$link : self::$PDOStatement;$arrError = $obj->errorInfo();
if($arrError[0] !== '00000') {self::$error = $arrError[0]."|".$arrError[2]. "<br/>[ SQL ] : ".self::$queryStr."<br/>"; self::throw_exception(self::$error); return false;}
if(self::$queryStr=='')self::throw_exception('Query was empty<br/><br/>[ SQL语句 ] :');
}
}?>
