PHP MySQL database backup script

[kajje]

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= "[e-mail]";

$report_send= "1";
$report_address= "[e-mail]";

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

$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]

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]

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]

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

[kajje]

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!