Welcome to WebmasterWorld Guest from 174.129.135.89

Forum Moderators: open

Message Too Old, No Replies

PHP MySQL database backup script

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

5+ Year Member



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;
}
?>
4:37 am on Mar 14, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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`;
8:04 am on Apr 2, 2010 (gmt 0)

5+ Year Member



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.
6:19 pm on Apr 2, 2010 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



I've had those . . . then you can try system(). :-)
9:04 pm on Apr 2, 2010 (gmt 0)

5+ Year Member



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!