Home » mysql, PHP Code »

June 4, 2009

How to take backup of Database using PHP code?

4

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 colloect 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 .


Related Posts

    How to send external html content mail using php?How to send mail using phpmailer classHow to get the real IP address using php?How to add line breaks in the textarea

 
Tags: , , , ,

4 Comments

  1. Srinivas Tamada says:
    June 5, 2009 at 5:21 am

    nice………..

  2. Vote this on php-html.net says:
    March 12, 2010 at 1:19 pm

    [...]A small and useful PHP snippet to generate an mySQL dump[...]

  3. Ganga Bhavani says:
    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

  4. olind sihombing says:
    September 23, 2011 at 8:58 am

    nice post.. i usually use mysql manager… but now.. i can backup with php code :D

Leave a Reply