-
Jan 2nd, 2010, 03:05 AM
#1
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
Last edited by Nightwalker83; Jan 14th, 2010 at 11:48 PM.
Reason: Fixed spelling
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Jan 14th, 2010, 11:35 PM
#2
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; } ?>
Last edited by Nightwalker83; Mar 31st, 2010 at 02:42 AM.
Reason: Fixed spelling
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Jan 15th, 2010, 10:33 AM
#3
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
Last edited by I_Love_My_Vans; Jan 15th, 2010 at 10:37 AM.
-
Jan 15th, 2010, 11:57 PM
#4
Re: Backup a Mysql database (php 5.3.0)
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.
Last edited by Nightwalker83; Jan 16th, 2010 at 06:41 AM.
Reason: Fxed spelling!
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Apr 5th, 2011, 09:36 PM
#5
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
-
Apr 6th, 2011, 03:14 AM
#6
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:
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.
Last edited by Nightwalker83; Apr 6th, 2011 at 05:31 AM.
Reason: Adding more!
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Apr 7th, 2011, 03:42 AM
#7
Re: Backup a Mysql database (php 5.3.0)
any how thanks for your reply
-
Jul 13th, 2012, 09:27 AM
#8
Fanatic Member
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!
-
Jul 15th, 2012, 06:35 AM
#9
Re: Backup a Mysql database (php 5.3.0)
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.
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Mar 15th, 2016, 05:24 AM
#10
New Member
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
-
Mar 19th, 2016, 08:43 PM
#11
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".
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|