PHP to achieve MySQL database backup source code

Below is a PHP database backup source code, you can also according to their own needs to modify.

<?php


Backing up a database


$host = "localhost";


$user = "root"; Database account


$password = ""; Database Password


$dbname = "MySQL"; Database name


The account number, password, and name are all sent from the page.


if (!mysql_connect ($host, $user, $password))//connect to MySQL Database


{


Echo ' Database connection failed, please check and try again ';


Exit


}


if (!mysql_select_db ($dbname))//whether the database exists


{


Echo ' does not exist database: '. $dbname. ', please check and try again ';


Exit


}


mysql_query ("Set names ' UTF8 '");


$mysql = "Set CharSet utf8;\r\n";


$q 1 = mysql_query ("Show Tables");


while ($t = mysql_fetch_array ($q 1))


{


$table = $t [0];


$q 2 = mysql_query ("Show create Table ' $table '");


$sql = Mysql_fetch_array ($q 2);


$mysql. = $sql [' Create Table ']. "; \ r \ n";


$q 3 = mysql_query ("select * from ' $table");


while ($data = Mysql_fetch_assoc ($q 3))


{


$keys = Array_keys ($data);


$keys = Array_map (' addslashes ', $keys);


$keys = Join (', ', $keys);


$keys = "'". $keys. "`";


$vals = Array_values ($data);


$vals = Array_map (' addslashes ', $vals);


$vals = Join ("', '", $vals);


$vals = "'". $vals. "'";


$mysql. = "INSERT INTO ' $table ' ($keys) values ($vals); \ r \ n";


}


}





$filename = $dbname. Date (' Ymjgi '). ". SQL"; Store path, default to project outermost


$fp = fopen ($filename, ' w ');


Fputs ($fp, $mysql);


Fclose ($FP);


echo "Data backup succeeded";


?>

If you are using thinkphp, you can use the following code to back up the MySQL database

<?php


Www.111cn.net


Class Baksqlaction extends Commonaction {





public $config = '; Related configuration


Public $model = '; Instantiate a model


Public $content; Content


Public $dbName = '; Database name


Public $dir _sep = '/'; Path symbols





Initializing data





function _initialize () {


Parent::_initialize ();


Header ("Content-type:text/html;charset=utf-8");


Set_time_limit (0); No timeout


Ini_set (' Memory_limit ', ' 500M ');


$this->config = Array (


' Path ' => C (' db_backup '),//Where backup files exist


' Iscompress ' => 0,//whether to turn on gzip compression "not tested"


' Isdownload ' => 0//download file ' not tested ' after backup completed


);


$this->dbname = C (' db_name '); Current database name


$this->model = new Model ();


$sql = ' Set interactive_timeout=24*3600 '; Disconnect after how many seconds are idle


$this->model>execute ($sql);


}





/* -


* +------------------------------------------------------------------------


* * @ backed up data list


* +------------------------------------------------------------------------


*/





function index () {


$path = $this->config[' path '];


$FILEARR = $this->myscandir ($path);


foreach ($fileArr as $key => $value) {


if ($key > 1) {


Get File creation time


$fileTime = Date (' y-m-d h:i:s ', Filemtime ($path. '/' . $value));


$fileSize = FileSize ($path. '/' . $value)/1024;


Get File size


$fileSize = $fileSize < 1024? Number_format ($fileSize, 2). ' KB ':


Number_format ($fileSize/1024, 2). ' MB ';


Building list Arrays


$list [] = Array (


' Name ' => $value,


' Time ' => $fileTime,


' Size ' => $fileSize


);


}


}


$this->assign (' list ', $list);


$this->display ();


}





/* -


* +------------------------------------------------------------------------


* * Get data table


* +------------------------------------------------------------------------


*/





function Tablist () {


$list = $this->model->query ("show TABLE STATUS from {$this->dbname}"); Get information about a table


echo $Backup->getlastsql ();


$this->assign (' list ', $list);


$this->display ();


}





/* -


* +------------------------------------------------------------------------


* * Backup the entire database


* +------------------------------------------------------------------------


*/





function Backall () {


$tables = $this->gettables ();


if ($this->backup ($tables)) {


$this->success (' Database backup succeeded! ', '/public/ok ');


} else {


$this->error (' Database backup failed! ');


}


}





/* -


* +------------------------------------------------------------------------


* * * Backup by table, can be batch


* +------------------------------------------------------------------------


*/





function Backtables () {


$tab = $_request[' tab '];


if (Is_array ($tab))


$tables = $tab;


Else


$tables [] = $tab;


if ($this->backup ($tables)) {


if (Is_array ($tab))


$this->success (' Database backup succeeded! ');


Else


$this->success (' Database backup succeeded! ', '/public/ok ');


} else {


$this->error (' Database backup failed! ');


}


}





Restore Database


function Recover () {


if ($this->recover_file ($_get[' file ')) {


$this->success (' Data restore success! ', '/public/ok ');


} else {


$this->error (' Data restore failed! ');


}


}





Delete Data backup


function Deletebak () {


if (unlink ($this->config[' path '). $this->dir_sep. $_get[' file ']) {


$this->success (' Delete backup successful! ', '/public/ok ');


} else {


$this->error (' Delete backup failed! ');


}


}





/* -


* +------------------------------------------------------------------------


* * Download Backup files


* +------------------------------------------------------------------------


*/





function Downloadbak () {


$file _name = $_get[' file '];


$file _dir = $this->config[' path '];


if (!file_exists ($file _dir. "/" . $file _name)) {//check whether the file exists


return false;


Exit


} else {


$file = fopen ($file _dir. "/" . $file _name, "R"); Open File


Input File Label


Header (' Content-encoding:none ');


Header ("Content-type:application/octet-stream");


Header ("Accept-ranges:bytes");


Header ("Accept-length:"). FileSize ($file _dir. "/" . $file _name));


Header (' content-transfer-encoding:binary ');


Header ("content-disposition:attachment; Filename= ".  $file _name); Provide the browser with the real file name to download


Header (' Pragma:no-cache ');


Header (' expires:0 ');


Output file contents


Echo fread ($file, FileSize ($file _dir. "/" . $file _name));


Fclose ($file);


Exit


}


}





/* -


* +------------------------------------------------------------------------


* @ Get the file array in the directory


* +------------------------------------------------------------------------


* * @ $FilePath directory path


* * @ $Order sort


* +------------------------------------------------------------------------


* @ Gets the list of files in the specified directory, returns the array


* +------------------------------------------------------------------------


*/





Private Function Myscandir ($FilePath = './', $Order = 0) {


$FilePath = Opendir ($FilePath);


while ($filename = Readdir ($FilePath)) {


$FILEARR [] = $filename;


}


$Order = = 0? Sort ($FILEARR): Rsort ($FILEARR);


return $FILEARR;


}





/*     * ******************************************************************************************** */





/* -


* +------------------------------------------------------------------------


* * @ Read backup files


* +------------------------------------------------------------------------


* * @ $fileName filename


* +------------------------------------------------------------------------


*/





Private Function GetFile ($fileName) {


$this->content = ';


$fileName = $this->trimpath ($this->config[' path '). $this->dir_sep. $fileName);


if (Is_file ($fileName)) {


$ext = STRRCHR ($fileName, '. ');


if ($ext = = '. sql ') {


$this->content = file_get_contents ($fileName);


} elseif ($ext = = '. Gz ') {


$this->content = Implode ("", Gzfile ($fileName));


} else {


$this->error (' Unrecognized file format! ');


}


} else {


$this->error (' file does not exist! ');


}


}





/* -


* +------------------------------------------------------------------------


* * Write data to disk


* +------------------------------------------------------------------------


*/





Private Function Setfile () {


$recognize = ';


$recognize = $this->dbname;


$fileName = $this->trimpath ($this->config[' path '). $this->dir_sep. $recognize. '_' . Date (' Ymdhis '). '_' . Mt_rand (100000000, 999999999). '. sql ');


$path = $this->setpath ($fileName);


if ($path!== true) {


$this->error ("Unable to create the backup directory directory ' $path '");


}


if ($this->config[' iscompress '] = = 0) {


if (!file_put_contents ($fileName, $this->content, lock_ex)) {


$this->error (' Write file failed, please check disk space or permissions! ');


}


} else {


if (function_exists (' Gzwrite ')) {


$fileName. = '. Gz ';


if ($gz = Gzopen ($fileName, ' WB ')) {


Gzwrite ($gz, $this->content);


Gzclose ($GZ);


} else {


$this->error (' Write file failed, please check disk space or permissions! ');


}


} else {


$this->error (' Do not open gzip extension! ');


}


}


if ($this->config[' isdownload ']) {


$this->downloadfile ($fileName);


}


}





Private Function Trimpath ($path) {


return Str_replace (Array ('/', ' \ \ \ ', '//', ' \\\\ '), $this->dir_sep, $path);


}





Private Function SetPath ($fileName) {


$dirs = Explode ($this->dir_sep, DirName ($fileName));


$tmp = ';


foreach ($dirs as $dir) {


$tmp. = $dir. $this->dir_sep;


if (!file_exists ($tmp) &&! @mkdir ($tmp, 0777))


return $tmp;


}


return true;


}





Not tested


Private Function DownloadFile ($fileName) {


Ob_end_clean ();


Header ("Cache-control:must-revalidate, Post-check=0, pre-check=0");


Header (' Content-description:file Transfer ');


Header (' Content-type:application/octet-stream ');


Header (' Content-length: ' FileSize ($fileName));


Header (' Content-disposition:attachment filename= '. basename ($fileName));


ReadFile ($fileName);


}





/* -


* +------------------------------------------------------------------------


* * Add "to string"


* +------------------------------------------------------------------------


* * @ $str string


* +------------------------------------------------------------------------


* @ return to ' $str '


* +------------------------------------------------------------------------


*/





Private Function Backquote ($STR) {


Return "' {$str} '";


}





/* -


* +------------------------------------------------------------------------


* @ Get all the tables in the database


* +------------------------------------------------------------------------


* * @ $dbName database name


* +------------------------------------------------------------------------


*/





Private Function gettables ($dbName = ' ") {


if (!empty ($dbName)) {


$sql = ' show TABLES from '. $dbName;


} else {


$sql = ' show TABLES ';


}


$result = $this->model->query ($sql);


$info = Array ();


foreach ($result as $key => $val) {


$info [$key] = current ($val);


}


return $info;


}





/* -


* +------------------------------------------------------------------------


* * to divide the transmitted data into groups by the specified length.


* +------------------------------------------------------------------------


* * $array the data to be split


* * @ $byte length to split


* +------------------------------------------------------------------------


* * to divide the array by the specified length and return the segmented array


* +------------------------------------------------------------------------


*/





Private Function Chunkarraybybyte ($array, $byte = 5120) {


$i = 0;


$sum = 0;


$return = Array ();


foreach ($array as $v) {


$sum + + strlen ($v);


if ($sum < $byte) {


$return [$i] = $v;


} elseif ($sum = = $byte) {


$return [+ + $i] = $v;


$sum = 0;


} else {


$return [+ + $i] = $v;


$i + +;


$sum = 0;


}


}


return $return;


}





/* -


* +------------------------------------------------------------------------


* * Backup Data {Backup Each table, view and data}


* +------------------------------------------------------------------------


* * @ $tables table array to be backed up


* +------------------------------------------------------------------------


*/





Private function Backup ($tables) {


if (empty ($tables))


$this->error (' No data tables need to be backed up! ');


$this->content = '/* This file was created by Mysqlreback '. Date (' y-m-d h:i:s '). ' */';


foreach ($tables as $i => $table) {


$table = $this->backquote ($table); Add "' for table name


$tableRs = $this->model->query ("show CREATE TABLE {$table}"); Gets the creation statement for the current table


if (!empty ($tableRs [0]["Create View"]) {


$this->content. = "\ r \ n * CREATE VIEW structure {$table} * *";


$this->content. = "\ r \ n DROP VIEW IF EXISTS {$table};/* mysqlreback separation *". $tableRs [0]["Create View"]. ";/* mysqlreback separation * *";


}


if (!empty ($tableRs [0]["Create Table"]) {


$this->content. = "\ r \ n * CREATE TABLE structure {$table} * *";


$this->content. = "\ r \ n DROP TABLE IF EXISTS {$table};/* mysqlreback separation *." $tableRs [0]["Create Table"]. ";/* mysqlreback separation * *";


$tableDateRow = $this->model->query ("SELECT * from {$table}");


$VALUESARR = Array ();


$values = ';


if (false!= $tableDateRow) {


foreach ($tableDateRow as & $y) {


foreach ($y as & $v) {


if ($v = = ")//corrected when empty is 0 return tree


$v = ' null '; Set NULL for NULL


Else


$v = "'". Mysql_escape_string ($v).       "'"; Non-null plus turn-signifier


}


$VALUESARR [] = ' ('. Implode (', ', $y). ')';


}


}


$temp = $this->chunkarraybybyte ($VALUESARR);


if (Is_array ($temp)) {


foreach ($temp as $v) {


$values = Implode (', ', $v). '/* mysqlreback separation * *;


if ($values!= ';/* mysqlreback separation/*) {


$this->content. = "\ r \ n Insert data {$table} *";


$this->content. = "\ r \ n INSERT into {$table} VALUES {$values}";


}


}


}


Dump ($this->content);


Exit


}


}





if (!empty ($this->content)) {


$this->setfile ();


}


return true;


}





/* -


* +------------------------------------------------------------------------


* * @ Restore Data


* +------------------------------------------------------------------------


* * @ $fileName filename


* +------------------------------------------------------------------------


*/





Private Function Recover_file ($fileName) {


$this->getfile ($fileName);


if (!empty ($this->content)) {


$content = explode (';/* mysqlreback separation * *, $this->content);


foreach ($content as $i => $sql) {


$sql = Trim ($sql);


if (!empty ($sql)) {


$mes = $this->model->execute ($sql);


if (false = = $mes) {//If NULL write fails, replace with '


$table _change = array (' null ' => ' \ ' \ ');


$sql = Strtr ($sql, $table _change);


$mes = $this->model->execute ($sql);


}


if (false = = $mes) {//If errors are encountered, errors are logged


$log _text = ' The following code restore encountered a problem: ';


$log _text.= "\ r \ n $sql";


Set_log ($log _text);


}


}


}


} else {


$this->error (' Unable to read backup file! ');


}


return true;


}





}


?>