Apr 14, '10
Simple MySQL Backup with PHP
Typically a cron job, or database backup routine is the best method for backing up your data, but sometimes it's just easier to backup when needed. What if you want your content management system users/publishers to be able to backup their data? Or how about only backing up when a change is made?
A little PHP code and you've got backup on demand:
function backupDatabase($file){
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result)){ $tables[] = $row[0]; }
//cycle through
$return = "";
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";
}
//save file
$handle = fopen($file,'w+');
fwrite($handle,$return);
fclose($handle);
}
Then just call the function...
backupDatabase('/path_to_file/backup' . date('m-d-y') . '.sql');
Just make sure the folder where this is going to be output has write privileges for the web account and you're all set! The script outputs a .sql file that can be pushed into the database if/when needed.
A quick note: I would not suggest allowing users to do a restore. Backing up their data can give them a sense of security, however, restoring data should be done (in most cases) very carefully by someone who knows what they're doing. Also, giving users a restore feature tends to become a crutch - "it's ok if I mess up [YOUR_APPLICATION_HERE], I can just restore the system".