《MYSQL教程詳解MYSQL的備份還原(PHP實現(xiàn))》要點：
本文介紹了MYSQL教程詳解MYSQL的備份還原(PHP實現(xiàn))，希望對您有用。如果有疑問，可以聯(lián)系我們。
手把手教你實現(xiàn)MYSQL的備份還原
示例代碼用我比較熟悉的
PHP,當然你看完并理解了其中的思路,相信你也可以快速地用你熟悉的語言自己寫出來.
一、新建dbBackup類,設置默認參數(shù).
代碼如下:
class dbBackup {
??? public $host='localhost';??? //數(shù)據(jù)庫地址
??? public $user='root';??? //登錄名
??? public $pwd='';??? //密碼
??? public $database;??? //數(shù)據(jù)庫名
??? public $charset='utf8';??? //數(shù)據(jù)庫連接編碼：mysql_set_charset
}
二、添加數(shù)據(jù)庫連接function.
代碼如下:
/**
???? * 連接數(shù)據(jù)庫 ...
???? */
??? function db() {???????
??????? $con = mysql_connect($this->host,$this->user,$this->pwd);
??????? if (!$con){
??????????? die('Could not connect');
??????? }
??????? $db_selected = mysql_select_db($this->database, $con);
??????? if (!$db_selected) {
??????????? die('Can\'t use select db');
??????? }
??????? mysql_set_charset($this->charset); //設置編碼
??????? return $con;
??? }
三、查詢數(shù)據(jù)庫表集合
代碼如下:
/**
???? * 表集合 ...
???? */
??? function tblist() {
??????? $list=array();
??????? $rs=mysql_query("SHOW TABLES FROM $this->database");
??????? while ($temp=mysql_fetch_row($rs)) {
??????????? $list[]=$temp[0];
??????? }
??????? return $list;
??? }
四、查詢表結構
代碼如下:
/**
???? * 表結構SQL ...
???? */
??? function sqlcreate() {
??????? $sql='';
??????? $tb=$this->tblist();???????
??????? foreach ($tb as $v) {
??????????? $rs=mysql_query("SHOW CREATE TABLE $v");
??????????? $temp=mysql_fetch_row($rs);
??????????? $sql.="-- 表的結構：{$temp[0]} --\r\n";
??????????? $sql.="{$temp[1]}";
??????????? $sql.=";-- <xjx> --\r\n\r\n";
??????? }
??????? return $sql;
??? }
注：$sql.=";-- <xjx> --\r\n\r\n"; 每句SQL后面必須加上分號(;)分割,MYSQL導入才能識別.-- <xjx> -- 是程序對SQL語句分割的標識,可以自定義但必須是注釋語句,否則影響SQL語句.\r\n無實際意義用于文本美觀
五、INSERT INTO語句
代碼如下:
/**
???? * 數(shù)據(jù)插入SQL ...
???? */
??? function sqlinsert() {
??????? $sql='';
??????? $tb=$this->tblist();???????
??????? foreach ($tb as $v) {
??????????? $rs=mysql_query("SELECT * FROM $v");
??????????? if (!mysql_num_rows($rs)) {//無數(shù)據(jù)返回
??????????????? continue;
??????????? }???????
??????????? $sql.="-- 表的數(shù)據(jù)：$v --\r\n";
??????????? $sql.="INSERT INTO `$v` VALUES\r\n";???????
??????????? while ($temp=mysql_fetch_row($rs)) {
??????????????? $sql.='(';
??????????????? foreach ($temp as $v2) {
??????????????????? if ($v2===null) {
??????????????????????? $sql.="NULL,";
??????????????????? }
??????????????????? else {
??????????????????????? $v2=mysql_real_escape_string($v2);
??????????????????????? $sql.="'$v2',";
??????????????????? }???????????????????
??????????????? }
??????????????? $sql=mb_substr($sql, 0, -1);
??????????????? $sql.="),\r\n";
??????????? }
??????????? $sql=mb_substr($sql, 0, -3);
??????????? $sql.=";-- <xjx> --\r\n\r\n";???
??????? }
??????? return $sql;
??? }
注：
1.無數(shù)據(jù)返回時必須跳出本次循環(huán),避免生成多余代碼
2.當字段值為(NULL)時,插入字符為(NULL)而不是('NULL'),沒有單引號.3.
$v2=
mysql_real_escape_string(
$v2),這是必要的轉義
4.mb_substr($sql, 0, -1)、mb_substr($sql, 0, -3),必須去除最后一個逗號(,) 否則SQL語句出錯5.
$sql.=";-- <xjx> --\r\n\r\n"
,詳見第四步注
六、備份操作
代碼如下:
/**
???? * 備份 ...
???? * @param $filename 文件路徑
???? */
??? function beifen($filename) {
??????? $this->db();??? //連接數(shù)據(jù)庫
??????? $sql=$this->sqlcreate();
??????? $sql2=$this->sqlinsert();???????
??????? $data=$sql.$sql2;
??????? return file_put_contents($filename, $data);
??? }
七、還原操作
代碼如下:
/**
???? * 還原 ...
???? * @param $filename 文件路徑
???? */
??? function huanyuan($filename) {
??????? $this->db();??? //連接數(shù)據(jù)庫
??????? //刪除數(shù)據(jù)表
??????? $list=$this->tblist();
??????? $tb='';
??????? foreach ($list as $v) {
??????????? $tb.="`$v`,";
??????? }
??????? $tb=mb_substr($tb, 0, -1);
??????? if ($tb) {
??????????? $rs=mysql_query("DROP TABLE $tb");
??????????? if ($rs===false) {
??????????????? return false;
??????????? }
??????? }
??????? //執(zhí)行SQL
??????? $str=file_get_contents($filename);
??????? $arr=explode('-- <xjx> --', $str);
??????? array_pop($arr);
??????? foreach ($arr as $v) {
??????????? $rs=mysql_query($v);
??????????? if ($rs===false) {
??????????????? return false;
??????????? }
??????? }
??????? return true;
??? }
備份示例：
代碼如下:
$x=new dbBackup();
$x->database='test';
$rs=$x->beifen('db.sql');
var_dump($rs);
還原示例：
代碼如下:
$x=new dbBackup();
$x->database='test';
$rs=$x->huanyuan('db.sql');
var_dump($rs);
完整代碼：
代碼如下:
class dbBackup {
??? public $host='localhost';??? //數(shù)據(jù)庫地址
??? public $user='root';??? //登錄名
??? public $pwd='';??? //密碼
??? public $database;??? //數(shù)據(jù)庫名
??? public $charset='utf8';??? //數(shù)據(jù)庫連接編碼：mysql_set_charset
??? /**
???? * 備份 ...
???? * @param $filename 文件路徑
???? */
??? function beifen($filename) {
??????? $this->db();??? //連接數(shù)據(jù)庫
??????? $sql=$this->sqlcreate();
??????? $sql2=$this->sqlinsert();???????
??????? $data=$sql.$sql2;
??????? return file_put_contents($filename, $data);
??? }
??? /**
???? * 還原 ...
???? * @param $filename 文件路徑
???? */
??? function huanyuan($filename) {
??????? $this->db();??? //連接數(shù)據(jù)庫
??????? //刪除數(shù)據(jù)表
??????? $list=$this->tblist();
??????? $tb='';
??????? foreach ($list as $v) {
??????????? $tb.="`$v`,";
??????? }
??????? $tb=mb_substr($tb, 0, -1);
??????? if ($tb) {
??????????? $rs=mysql_query("DROP TABLE $tb");
??????????? if ($rs===false) {
??????????????? return false;
??????????? }
??????? }
??????? //執(zhí)行SQL
??????? $str=file_get_contents($filename);
??????? $arr=explode('-- <xjx> --', $str);
??????? array_pop($arr);
??????? foreach ($arr as $v) {
??????????? $rs=mysql_query($v);
??????????? if ($rs===false) {
??????????????? return false;
??????????? }
??????? }
??????? return true;
??? }
??? /**
???? * 連接數(shù)據(jù)庫 ...
???? */
??? function db() {???????
??????? $con = mysql_connect($this->host,$this->user,$this->pwd);
??????? if (!$con){
??????????? die('Could not connect');
??????? }
??????? $db_selected = mysql_select_db($this->database, $con);
??????? if (!$db_selected) {
??????????? die('Can\'t use select db');
??????? }
??????? mysql_set_charset($this->charset);??? //設置編碼
??????? return $con;
??? }
??? /**
???? * 表集合 ...
???? */
??? function tblist() {
??????? $list=array();
??????? $rs=mysql_query("SHOW TABLES FROM $this->database");
??????? while ($temp=mysql_fetch_row($rs)) {
??????????? $list[]=$temp[0];
??????? }
??????? return $list;
??? }
??? /**
???? * 表結構SQL ...
???? */
??? function sqlcreate() {
??????? $sql='';
??????? $tb=$this->tblist();???????
??????? foreach ($tb as $v) {
??????????? $rs=mysql_query("SHOW CREATE TABLE $v");
??????????? $temp=mysql_fetch_row($rs);
??????????? $sql.="-- 表的結構：{$temp[0]} --\r\n";
??????????? $sql.="{$temp[1]}";
??????????? $sql.=";-- <xjx> --\r\n\r\n";
??????? }
??????? return $sql;
??? }
??? /**
???? * 數(shù)據(jù)插入SQL ...
???? */
??? function sqlinsert() {
??????? $sql='';
??????? $tb=$this->tblist();???????
??????? foreach ($tb as $v) {
??????????? $rs=mysql_query("SELECT * FROM $v");
??????????? if (!mysql_num_rows($rs)) {//無數(shù)據(jù)返回
??????????????? continue;
??????????? }???????
??????????? $sql.="-- 表的數(shù)據(jù)：$v --\r\n";
??????????? $sql.="INSERT INTO `$v` VALUES\r\n";???????
??????????? while ($temp=mysql_fetch_row($rs)) {
??????????????? $sql.='(';
??????????????? foreach ($temp as $v2) {
??????????????????? if ($v2===null) {
??????????????????????? $sql.="NULL,";
??????????????????? }
??????????????????? else {
??????????????????????? $v2=mysql_real_escape_string($v2);
??????????????????????? $sql.="'$v2',";
??????????????????? }???????????????????
??????????????? }
??????????????? $sql=mb_substr($sql, 0, -1);
??????????????? $sql.="),\r\n";
??????????? }
??????????? $sql=mb_substr($sql, 0, -3);
??????????? $sql.=";-- <xjx> --\r\n\r\n";???
??????? }
??????? return $sql;
??? }
}
//備份
//$x=new dbBackup();
//$x->database='test';
//$rs=$x->beifen('db.sql');
//var_dump($rs);
//還原
//$x=new dbBackup();
//$x->database='test';
//$rs=$x->huanyuan('db.sql');
//var_dump($rs);
轉載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/3369.html
