mySQL Database Backup – Copy DB Backup SQL File in Folder – PHP Script

by Hiroshi on January 12, 2013

in database, MySQL

This is tricky one. At-least for me. I could make use of the following script but it has a problem as well. After much research, you find few scripts and many of those do not work properly. I want to share with you what is working for me. This database backup script will create a database .sql file and copy that in a folder. I wanted to make it downloadable but may be later I will find a good solution. Copy this script and paste it in PHP page. Change hostname, dbname, user and password and it will run on page load. It will dump a .sql file of a database specified by you. I wanted to make it run when after posting a form but its not. And I wanted to give me file in downloadable format. Anyways it stores the db file with records and its pretty neat. But if you keep reloading page, it will keep throwing .sql files in the destination folder. That is why I was using Delete All Files from Given Folder PHP Script and Read Folder, List Files and Link Those Files in PHP scripts with it.

What I did was, placed first a script to unlink / delete all files from db folder. Then I placed this db-database script to generate a fresh copy of database. Then I used simple script to read database folder to list and link to that .sql file. Please let me know if you have easy, complete and great Database backup script that gives file to download after backup or even better if it can email that file.

Here is the Database Backup Script.

Funny thing is, I wanted to place a check before it.

if

<?php if($doBackupDB=='yes') { ?>

Than run backup, but its not understanding that.

<?php
// create new fresh backup
backup_tables('localhost','root','pass','dbName');
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$dbname,$tables = '*')
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysql_query('SHOW TABLES');
while($row = mysql_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
foreach($tables as $table)
{
$result = mysql_query('SELECT * FROM '.$table);
$num_fields = mysql_num_fields($result);
$return.= 'DROP TABLE IF EXISTS '.$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('backups/db-backup-'.time().'.sql','w+');
fwrite($handle,$return);
fclose($handle);
}
?>

Any help?

Related Posts

Previous post:

Next post: