PHP [PHP] Automatically backup your database

Sometimes it can be convenient to save your whole MySQL database as a safety measure. The script below declares a single method "backupDatabase()" that does all of this in a simple way :

• Lists all your tables automatically
  
• Create a sql file containing instruction to recreate and fill your tables
• Give you a count of how many rows were backed up table by table
• Save the file on your server (by default in directory "dbbackup")
• Each filename contains timestamp, so you can repeat operation without risking overwrite

I personnally use it on my website to do regular backups (at least when some important data has been added) : I put all the following in a single file, and made a menu entry pointing to it. When I feel like it I click on this link and the rest is done automatically.

Hopes this helps

<?php

function backupDatabase() {
$link = mysql_connect($_SESSION['db_server'], $_SESSION['db_login'], $_SESSION['db_password']);
mysql_select_db($_SESSION['db_instance']);

$filename = "dbbackup/backup_" . date("Y_m_d_H_i") . ".sql";
$fp = fopen($filename, "w");

if (!is_resource($fp))
return false;

$res = mysql_query("SHOW TABLES");
mysql_query("SET NAMES 'utf8'");

$num_rows = mysql_num_rows($res);
$i = 0;

while ($i < $num_rows) {
$tablename = mysql_tablename($res, $i);

// Structure

fwrite($fp, "DROP TABLE IF EXISTS `$tablename`;\n");
$query = "SHOW CREATE TABLE $tablename";
$resCreate = mysql_query($query);
$row = mysql_fetch_array($resCreate);
$schema = $row[1] . ";";
fwrite($fp, "$schema\n\n");

// Data

$query = "SELECT * FROM $tablename";
$export = mysql_query($query);
$count = 0;

while ($row = mysql_fetch_row($export)) {

$data = "";
$header = "";
$num_fields = mysql_num_fields($export);

for ($j = 0; $j < $num_fields; $j++) {
$header .= "`" . mysql_field_name($export, $j) . "`,";
}

$header = substr($header, 0, -1);
$line = '';

foreach ($row as $value) {
if ($value == NULL)
$value = "NULL";

$value = str_replace('"', '""', $value);
$value = str_replace("'", "\''", $value);

$value = "'" . $value . "'" . ",";
$line .= $value;
}

$data .= "INSERT INTO `$tablename` ($header) VALUES (" . trim($line) . ");\n";
$count++;

$data = str_replace("\r", "", $data);
$data = str_replace(',)', ')', $data);
$data = str_replace('\'NULL\'', 'NULL', $data);

fwrite($fp, $data . "\n");
}

echo $tablename." : ".$count." rows<br/>";

$i++;
}

fclose($fp);

echo "<p>Database backup complete in file ".$filename."</p>";
}

backupDatabase();

?>

 

Thanks - i will try to use this

 

<?php

$filename='database_backup_'.date('G_a_m_d_y').'.sql';

$result=exec('mysqldump database_name --password=your_pass --user=root --single-transaction >/var/backups/'.$filename,$output);

if($output==''){/* no output is good */}
else {/* we have something to log the output here*/}

 

Hello Dear

- Using SQL Command through PHP

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )
{
die('Could not connect: ' . mysql_error());
}

$table_name = "employee";
$backup_file = "/tmp/employee.sql";
$sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );

if(! $retval )
{
die('Could not take data backup: ' . mysql_error());
}

echo "Backedup data successfully\n";

mysql_close($conn);
?>

- Using MySQL binary mysqldump through PHP.

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )
{
die('Could not connect: ' . mysql_error());
}

$table_name = "employee";
$backup_file = "/tmp/employee.sql";
$sql = "LOAD DATA INFILE '$backup_file' INTO TABLE $table_name";

mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );

if(! $retval )
{
die('Could not load data : ' . mysql_error());
}
echo "Loaded data successfully\n";

mysql_close($conn);
?>

- Using phpMyadmin user interface -

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';

$backup_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "test_db | gzip > $backup_file";

system($command);
?>