PHP to achieve MySQL database backup source code

Source: Internet
Author: User
Tags foreach explode filetime php database table name mysql database

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;


}





}


?>

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.