I want to share my views with you all on how to take backup of Database(MySql) using simple PHP code. , I recently worked on some project, due to some reasons I lost my database, then i think for is any code for taking backup of database, finally i got this code and now I am easily collect the backup of database each day, when it is run in cron job.
The follwing code will be the useful for the create a backup of database using the php code:
PHP Code
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | <?php /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = "*") { $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == "*") { $tables = array(); $result = mysql_query("SHOW TABLES"); while($row = mysql_fetch_row($result)) { $tables[] = $row[0]; } } else { $tables = is_array($tables) ? $tables : explode(",",$tables); } //cycle through foreach($tables as $table) { $result = mysql_query("SELECT * FROM ".$table); $num_fields = mysql_num_fields($result); $return.= "DROP TABLE ".$table.";"; $row2 = mysql_fetch_row(mysql_query("SHOW CREATE TABLE ".$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) { while($row = mysql_fetch_row($result)) { $return.= "INSERT INTO ".$table." VALUES("; for($j=0; $j<$num_fields; $j++) { $row[$j] = addslashes($row[$j]); $row[$j] = ereg_replace("\n","\\n",$row[$j]); if (isset($row[$j])) { $return.= """.$row[$j].""" ; } else { $return.= """"; } if ($j<($num_fields-1)) { $return.= ","; } } $return.= ");\n"; } } $return.="\n\n\n"; } $prefix="db_"; $tmpDir="mysqldump/"; $sqlFile = $tmpDir.$prefix.date("Y_m_d").".sql"; //save file $handle = fopen($sqlFile,"w+"); fwrite($handle,$return); fclose($handle); } ?> |
This will be useful .
7 Comments
Srinivas Tamada · June 5, 2009 at 5:21 am
nice………..
Vote this on php-html.net · March 12, 2010 at 1:19 pm
[…]A small and useful PHP snippet to generate an mySQL dump[…]
Ganga Bhavani · March 18, 2011 at 8:53 am
Hi Sir,
Iam Ganga Bhavani working as a consultant in Govt. company on PHP Developement since 7months so as you can think i have less knowledge on PHP.
And my problem is on my task.
Task is given to me that backup the postgresql database into selected folder using php.
I have seen your code and worked on it but i did not get the correct result.So, please send me the complete code of my task and save me please.
Thank you in advance.
Ganga Bhavani
olind sihombing · September 23, 2011 at 8:58 am
nice post.. i usually use mysql manager… but now.. i can backup with php code 😀
Dolly · July 4, 2012 at 7:59 am
Hi Sir,
Iam Dolly working in Govt. company on PHP Developement so as you can think i have less knowledge on PHP.
And my problem is on my task.
Task is given to me that backup the postgresql database into selected folder using php.
I have seen your code and worked on it but i did not get the correct result.So, please send me the complete code of my task and save me please.
Thank you in advance.
Dolly
vadim · February 16, 2013 at 1:06 pm
Parse error: syntax error, unexpected ‘”‘ in D:\wamp\www\backup\index.php on line 44
MARCO · February 10, 2014 at 8:14 pm
HORROR! DON’T USE MYSQL_* !!! Use mysqli instead