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.

How to take backup of Database using PHP code by Anil Kumar Panigrahi

How to take backup of Database using PHP code by Anil Kumar Panigrahi

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

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *