Programmatically Backup mysql database with PHP
This script will perform a backup from a given database using PHP
<?php
backup_tables(`localhost`, `root`, ``, `mydbname`);
function backup_tables($host, $user, $pass, $name, $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 ` . $table . `;`;
$row2 = mysql_fetch_row(mysql_query(`SHOW CREATE TABLE ` . $table));
$return.= " " . $row2[1] . "; ";
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(" ", " ", $row[$j]);
if (isset($row[$j]))
{
$return.= `"` . $row[$j] . `"`;
}
else
{
$return.= `""`;
}
if ($j < ($num_fields - 1))
{
$return.= `,`;
}
}
$return.= "); ";
}
}
$return.= " ";
}
//save file
$handle = fopen(`db-backup-`.time().`-`.(md5(implode(`,`,$tables))).`.sql`,`w+`); fwrite($handle,$return); fclose($handle); }
}
?>