Welcome to WebmasterWorld Guest from 23.22.220.37

Forum Moderators: open

Message Too Old, No Replies

PHP MySQL database backup script

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

New User

5+ Year Member

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

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 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`;
8:04 am on Apr 2, 2010 (gmt 0)

New User

5+ Year Member

joined:Jan 8, 2010
posts:18
votes: 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.
6:19 pm on Apr 2, 2010 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 0


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

New User

5+ Year Member

joined:Jan 8, 2010
posts: 18
votes: 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!