Backup MySQL Database Using PHP
If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).Some web hosts do offer such backup feature, you can also backup things using PHP exec() function (if supported by host) but if not, you can use this PHP functions to back up your entire database tables.Here is a little PHP script that I use to backup MySQL databases.
if (isset($_POST['name'])) {
/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
$link = mysqli_connect($host,$user,$pass);
mysqli_select_db($link,$name);
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysqli_query($link, 'SHOW TABLES');
while($row = mysqli_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
$return = '';
foreach($tables as $table)
{
$result = mysqli_query($link, 'SELECT * FROM '.$table);
$num_fields = mysqli_field_count($link);
$return.= 'DROP TABLE '.$table.';';
$row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = mysqli_fetch_row($result))
{
$return.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j < $num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = preg_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";
}
$time = time();
//save file to the browser
header("Content-Type: text/plain");
header("Content-Disposition: attachment; filename=\"$name-backup-$time.sql\"");
header("Content-Length: " . strlen($return));
header("Content-Transfer-Encoding: binary");
echo $return;
}
backup_tables($_POST['host'], $_POST['user'], $_POST['pass'], $_POST['name']);
} else {
?>
Backup database
