How to take backup of Database using PHP code?

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

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

Anil Kumar Panigrahi

With more than 10 years experience in PHP and Founder of Anil Labs, a blog for PHP and related posts. contributed posts regarding CodeIgniter, CakePHP and Learn PHP online

Contact : [email protected]

You may also like...

7 Responses

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

  2. Ganga Bhavani says:

    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

  3. olind sihombing says:

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

  4. Dolly says:

    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

  5. vadim says:

    Parse error: syntax error, unexpected ‘”‘ in D:\wamp\www\backup\index.php on line 44

  6. MARCO says:

    HORROR! DON’T USE MYSQL_* !!! Use mysqli instead

Leave a Reply

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