homepage Welcome to WebmasterWorld Guest from 54.163.91.250
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
PHP MySQL database backup script
kajje




msg:4096865
 9:24 am on Mar 13, 2010 (gmt 0)

I was looking for a php script that could make a backup of my MySQL databases. Couldn't find one so I modified/merged/changed some example code I found all over the web.

Maybe it comes handy for anybody...

<?
/////////
//////// MYSQL DUMP
/////// V1.00a 20100205 KAJJE
////// can be either used as standalone script or as runned in cron
/////////////////////////////////////////////////////////////////////
$mysql_host= "localhost";
$mysql_database= "da-tebase";
$mysql_username= "da-tebase";
$mysql_password= "pas-sword";

$save_tofile= "1";

$save_to_email= "1";
$save_address= "your@email.com";

$report_send= "1";
$report_address= "your@email.com";

//////////////////////////////////////////////////////////////

$scriptstart = (float) array_sum(explode(' ',microtime()));
set_time_limit(0);
$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
$db_selected = mysql_select_db($mysql_database, $link);

$filename = date("Ymd-His")."-".$mysql_database;
$dbdump = "/****************************************************************
* AUTOMATICLY GENERATED MYSQL DUMP
* Date: ".date("Y-m-d H:i:s")."
* Host: $mysql_host
* User: $mysql_username
* File: $filename
***************************************************************/\n\n";
$dbdump .=_mysqldump($mysql_database);


if ($save_tofile == "1")
{
$fp = fopen($filename.".sql", 'w');
fwrite($fp, $dbdump);
fclose($fp);
}

if ($save_to_email == "1")
{
mail($save_address, "[SQLDUMP] ".$filename, $dbdump);
}

$scriptend = (float) array_sum(explode(' ',microtime()));
echo "Database backup generated on ".date("Y-m-d H:i:s")."<BR>";
echo "Processing time: ". sprintf("%.4f", ($scriptend-$scriptstart))." seconds";

if ($report_send == "1")
{
mail($report_address , "[SQLDUMP] REPORT ".$filename, "MySQL dump finished\nDatabase: $mysql_database\nDate : ".date("Y-m-d H:i:s")."\nProcessing time: ". sprintf("%.4f", ($scriptend-$scriptstart))." seconds");
}





function _mysqldump($mysql_database)
{
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{
while( $row= mysql_fetch_row($result))
{
$dbdump .=_mysqldump_table_structure($row[0]);
$dbdump .=_mysqldump_table_data($row[0]);

}
}
else
{
$dbdump .= "/* no tables in $mysql_database */\n";
}
mysql_free_result($result);
return $dbdump;
}

function _mysqldump_table_structure($table)
{
$dbdump .= "/* Table structure for table `$table` */\n";
$dbdump .= "DROP TABLE IF EXISTS `$table`;\n\n";
$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
if($row= mysql_fetch_assoc($result))
{
$dbdump .= $row['Create Table'].";\n\n";
}
}
mysql_free_result($result);
return $dbdump;
}

function _mysqldump_table_data($table)
{
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
$num_rows= mysql_num_rows($result);
$num_fields= mysql_num_fields($result);

if( $num_rows > 0)
{
$dbdump .= "/* dumping data for table `$table` */\n";
$field_type=array();
$i=0;
while( $i < $num_fields)
{
$meta= mysql_fetch_field($result, $i);
array_push($field_type, $meta->type);
$i++;
}
$dbdump .= "insert into `$table` values\n";
$index=0;
while( $row= mysql_fetch_row($result))
{
$dbdump .= "(";
for( $i=0; $i < $num_fields; $i++)
{
if( is_null( $row[$i]))
$dbdump .= "null";
else
{
switch( $field_type[$i])
{
case 'int':
$dbdump .= $row[$i];
break;
case 'string':
case 'blob' :
default:
$dbdump .= "'".mysql_real_escape_string($row[$i])."'";
}
}
if( $i < $num_fields-1)
$dbdump .= ",";
}
$dbdump .= ")";

if( $index < $num_rows-1)
{
$dbdump .= ",";
}
else
{
$dbdump .= ";";
}
$dbdump .= "\n";
$index++;
}
}
}
mysql_free_result($result);
$dbdump .= "\n";
return $dbdump;
}
?>

 

rocknbil




msg:4097249
 4:37 am on Mar 14, 2010 (gmt 0)

Does your server support mysqldump?

This is a perl version, just sub qq|| for quotes, take $cmd and exec() it . . . . . create a date/time for the filename string and give it an .sql extension.

$cmd = qq|mysqldump -h $db_host --user=$sql_login --password=$sql_pass $db_name > $root/$db_bu/$file|;
$result = `$cmd`;

kajje




msg:4108793
 8:04 am on Apr 2, 2010 (gmt 0)

Hi rocknbil,

Your solution is far more easy and of course completely error-proof.

There's a lot of environments however where the exec() function doesn't work.

rocknbil




msg:4109019
 6:19 pm on Apr 2, 2010 (gmt 0)

I've had those . . . then you can try system(). :-)

kajje




msg:4109114
 9:04 pm on Apr 2, 2010 (gmt 0)

Additionally system() and exec() are not OS cross compatible, the mysqldump exectutable would be on different names and locations.

But running the real dumper is far more error proof that's for sure!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved