Backup a Mysql database (php 5.3.0)
Hi,
This is a php script I made because I was curious how the automated backup of the mysql databases was achieved.
PHP Code:
<?php
//Name of host you want to use
$db_host = "localhost";
//Name of database you want to use
$db_name = "students";
//User
$db_user = "root";
//Password
$db_pass = "";
//Name of table or tables
$table = "students";
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name) or die("Unable to select database.");
function datadump ($table) {
$result ="";
$row="";
$result .= "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
$query = mysql_query("select * from $table");
$num_fields = @mysql_num_fields($query);
//Retrieve the data from the original database
for ($i =0; $i <$num_fields; $i++) {
$result .= "INSERT INTO ".$table." VALUES(";
$data = mysql_query("SELECT * FROM $table");
while($info = mysql_fetch_array( $data,MYSQL_ASSOC )){
$comma_separated = implode(",", $info);
//Insert the data from the original array in to sql statement
if ($i<($num_fields)) $result .= "$comma_separated";
$result .= ");\n";
}
return $result . "\n\n\n";
}
}
$table1 = datadump ($table); //Add more datadumps if needed
$content = $table1; // Add additional datadumps to the string
//Creates an .sql file for download to your computer
$file_name = "MySQL_Database_Backup.sql";
Header("Content-type: application/octet-stream");
Header("Content-Disposition: attachment; filename=$file_name");
echo $content;
exit;
?>
If you have any comments or suggestions on how I can improve the code please post them. By the way, I'm hoping to create a totally automated back script sometime.
Edit:
Forgot to mention the php code version which is 5.3.0.
Nightwalker
Re: Backup a Mysql database (php 5.3.0)
Here is version 1.1 of the database backup script:
PHP Code:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="POST" enctype="multipart/form-data" name="backup">
<label>Host:</label> <br />
<input name="host" type="text" maxlength="20" /><br />
<label>Database name:</label> <br />
<input name="db_name" type="text" maxlength="20" /><br /><label>Table:</label> <br />
<input name="table" type="text" maxlength="20" /><br /><label>User:</label> <br />
<input name="user" type="text" maxlength="20" /><br /><label>Password:</label> <br />
<input name="pass" type="password" maxlength="20" /><br />
<input name="submit" type="submit" /> <input name="reset" type="reset" />
</form>
</body>
</html>
<?php
if($_SERVER['REQUEST_METHOD'] == "POST"){
$db_host = "";
$db_name = "";
$db_user = "";
$db_pass = "";
$table = "";
//Name of host you want to use
$db_host = $_POST["host"];
//Name of database you want to use
$db_name = $_POST["db_name"];
//User
$db_user = $_POST["user"];
//Password
$db_pass = $_POST["pass"];
//Name of table or tables
$table = $_POST["table"];
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name) or die("Unable to select database.");
function datadump ($table) {
$result ="";
$row="";
$result .= "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
$query = mysql_query("select * from $table");
$num_fields = @mysql_num_fields($query);
//Retrieve the data from the original database
for ($i =0; $i <$num_fields; $i++) {
$result .= "INSERT INTO ".$table." VALUES(";
$data = mysql_query("SELECT * FROM $table");
while($info = mysql_fetch_array( $data,MYSQL_ASSOC )){
$comma_separated = implode(",", $info);
//Insert the data from the original array in to sql statement
if ($i<($num_fields)) $result .= "$comma_separated";
$result .= ");\n";
return $result . "\n\n\n";
}
}
}
$table1 = datadump ($table); //Add more datadumps if needed
$content = $table1; // Add additional datadumps to the string
//Creates an .sql file for download to your computer
$file_name = "MySQL_Database_Backup.sql";
header('Content-Disposition: attachment; filename='.basename($file_name));
//Remove the form from the $content data
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
ob_clean();
//flush();
echo $content;
exit;
}
?>
Re: Backup a Mysql database (php 5.3.0)
1) Your opening HTML docytpe needs a <
2) You should probably check for the data type of each field before constructing the INSERT string, I ran this code on my test box and found that a long string which contained a few commas, needless to say if I were to attempt to execute this data I would probably get errors.
3) If you decide to echo the SQL out onto the screen afterwards consider escaping HTML characters, in on of my records I have an <img> tag that has been executed in the browser.
Edit:
Just read kows post in the PHP forum, he's right, take a look at mysqldump
Re: Backup a Mysql database (php 5.3.0)
Quote:
Originally Posted by
I_Love_My_Vans
1)
Just read kows post in the PHP forum, he's right, take a look at mysqldump
Thanks! Although, the reason I have created the backup using php is so I could get an understanding of how the backup system works not for the purpose of performing an actual backup. I know I can use programs such as mysql dump to backup the database but in it that case mysql dump (or another program) would be called to preform the backup where as my code above runs independently of any of those database programs.
I also noticed for the next version of the code I would need a function so the user doesn't send the data until all the required fields are filled in. Doing so causes an error with the code.
Re: Backup a Mysql database (php 5.3.0)
night walker
it will be great help to the community if you provide some automation for MySQL back Up
operation through vb.net
Re: Backup a Mysql database (php 5.3.0)
Here is version 1.2 of the above script which I created a while ago.
PHP Code:
<!-- Change Log
www.aaronspehr.net
Version 1.0 2nd January 2010 - Created the basic database backup function.
Version 1.1 15th January 2010 - Added a form to allow the user to enter the details of the database they want to backup.
Version 1.2 24th January 2010 - Form validation added to ensure the user enters the correct information so the system does not fail.
-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Assegai Database Backup</title>
<script type="text/javascript">
//The function checks to see whether or not the form fields have been filled-in correctly and if not then it displays a message telling the user to fill-in that information.
function ValidateInputs(objForm) {
//objForm. is used for Firefox validation purposes
var sHost = objForm.host.value;
var sName = objForm.db_name.value;
var sTable = objForm.table.value;
var sUser = objForm.user.value;
var sErr = "";
var sInstr = "";
//Check the fields
//Checks if all fields have been filled in
if (sHost == "") {
sErr = "You must enter a host name!\n";
}
if (sName == "") {
sErr = sErr + "You must enter a database name!\n";
}
if (sTable == "") {
sErr = sErr + "You must enter a table!\n";
}
if (sUser == "") {
sErr = sErr + "You must enter your database username!\n";
}
if (sErr == "") {
return true;
}
else {
sInstr = "The following errors were found with your submission."
sInstr = sInstr + "\nPlease correct them and try again.\n\n"
sErr = sInstr + sErr
//inform the user of the problem
alert(sErr);
return false;
}
}
</script>
</head>
<style type="text/css">
body{
margin-top: 150px;
margin-left: 300px;
}
form{
margin-left: 100px;
}
</style>
<body>
<h1>Assegai Database Backup</h1>
<noscript>To ensure the backup system works for you please enable javascript!</noscript>
<form action="<?php $_SERVER['PHP_SELF'];?>" method="POST" enctype="multipart/form-data" name="backup" onsubmit="return ValidateInputs(this);">
<label>Host:</label> <br />
<input name="host" type="text" maxlength="20" /><br />
<label>Database name:</label> <br />
<input name="db_name" type="text" maxlength="20" /><br /><label>Table:</label> <br />
<input name="table" type="text" maxlength="20" /><br /><label>User:</label> <br />
<input name="user" type="text" maxlength="20" /><br /><label>Password:</label> <br />
<input name="pass" type="password" maxlength="20" /><br />
<input name="submit" type="submit" value="Submit" /> <input name="reset" type="reset" value="Reset" />
</form>
</body>
</html>
<?php
if($_SERVER['REQUEST_METHOD'] == "POST"){
$db_host = "";
$db_name = "";
$db_user = "";
$db_pass = "";
$table = "";
//Name of host you want to use
$db_host = $_POST["host"];
//Name of database you want to use
$db_name = $_POST["db_name"];
//User
$db_user = $_POST["user"];
//Password
$db_pass = $_POST["pass"];
//Name of table or tables
$table = $_POST["table"];
mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db($db_name) or die("Unable to select database.");
function datadump ($table) {
$result ="";
$row="";
$result .= "# Dump of $table \n";
$result .= "# Dump DATE : " . date("d-M-Y") ."\n\n";
//Retrieve the data from the original database
$data = mysql_query("SELECT * FROM $table");
while($info = mysql_fetch_assoc($data)){
foreach($info as $col => $value)
{
$result .= "INSERT INTO ".$table." $col
VALUES(";
$comma_separated = implode(",", $info);
//Insert the data from the original array in to sql statement
$result .= "$comma_separated";
$result .= ");\n";
return $result . "\n\n\n";
}
}
}
$table1 = datadump ($table); //Add more datadumps if needed
$content = $table1; // Add additional datadumps to the string
//Creates an .sql file for download to your computer
$file_name = "MySQL_Database_Backup.sql";
header('Content-Disposition: attachment; filename='.basename($file_name));
//Remove the form from the $content data
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
ob_clean();
//flush();
echo $content;
exit;
}
?>
Edit:
Quote:
Originally Posted by
make me rain
night walker
it will be great help to the community if you provide some automation for MySQL back Up
operation through vb.net
Unfortunately, I do not know vb.net sorry. I might try automating the MYSQL back up using C# though.
Re: Backup a Mysql database (php 5.3.0)
any how thanks for your reply
Re: Backup a Mysql database (php 5.3.0)
This is an excellent script. I will use it to backup some modem statistics I've been logging!
Re: Backup a Mysql database (php 5.3.0)
Quote:
Originally Posted by
Slyke
This is an excellent script. I will use it to backup some modem statistics I've been logging!
Feel free to adjust the above as you see fit and if you can make any improvements on the above code please post them here.
Re: Backup a Mysql database (php 5.3.0)
A good skript, but it build only table "students"
It would be cool to write the automatic backup there every week for example. And duplicate database on the cloud
Re: Backup a Mysql database (php 5.3.0)
There is a better code based on the one above written by someone else on the forums. Try searching for "MySql Backup".
