Php backup script

miles_rich

How would you write a script to back up a mysql database eg. dumpit and then to back up all htdocs files? im on linux, so would i write a bash script to do this and then call it from php if so how would i call it? or can i do it direct from php? if so how?

jacob_kochi

User following code for dumping mysql database . Give appropriate values for variables

$dbname(Data base name)
$dbhost(Host name)
$username(User name)
$pass(Password)

<?php

		$dbname="your database name";
		$dbhost="Host name";
		$username="Database user name";
		$pass="Database Password";	
		$constr=mysql_connect($dbhost,$username,$pass) or die("Connection failure");
		mysql_select_db($dbname,$constr);

function sqldumptable($table, $fp=0) {

$tabledump = "DROP TABLE IF EXISTS $table;\n";
$tabledump .= "CREATE TABLE $table (\n";

$firstfield=1;

// get columns and spec
$fields = mysql_query("SHOW FIELDS FROM $table");
while ($field = mysql_fetch_array($fields)) {
if (!$firstfield) {
$tabledump .= ",\n";
} else {
$firstfield=0;
}
$tabledump .= " $field[Field] $field[Type]";
if (!empty($field["Default"])) {
// get default value
$tabledump .= " DEFAULT '$field[Default]'";
}
if ($field['Null'] != "YES") {
// can field be null
$tabledump .= " NOT NULL";
}
if ($field['Extra'] != "") {
// any extra info?
$tabledump .= " $field[Extra]";
}
}
mysql_free_result($fields);

// get keys list
$keys = mysql_query("SHOW KEYS FROM $table");
while ($key = mysql_fetch_array($keys)) {
$kname=$key['Key_name'];
if ($kname != "PRIMARY" and $key['Non_unique'] == 0) {
$kname="UNIQUE|$kname";
}
if(!is_array($index[$kname])) {
$index[$kname] = array();
}
$index[$kname][] = $key['Column_name'];
}
mysql_free_result($keys);

// get each key info
while(list($kname, $columns) = @each($index)){
$tabledump .= ",\n";
$colnames=implode($columns,",");

if($kname == "PRIMARY"){
  // do primary key
  $tabledump .= "   PRIMARY KEY ($colnames)";
} else {
  // do standard key
  if (substr($kname,0,6) == "UNIQUE") {
    // key is unique
    $kname=substr($kname,7);
  }

  $tabledump .= "   KEY $kname ($colnames)";

}

}

$tabledump .= "\n);\n\n";
if ($fp) {
fwrite($fp,$tabledump);
} else {
echo $tabledump;
}

// get data
$rows = mysql_query("SELECT * FROM $table");
$numfields=mysql_num_fields($rows);
while ($row = mysql_fetch_array($rows)) {
$tabledump = "INSERT INTO $table VALUES(";

$fieldcounter=-1;
$firstfield=1;
// get each field's data
while (++$fieldcounter<$numfields) {
  if (!$firstfield) {
    $tabledump.=", ";
  } else {
    $firstfield=0;
  }

  if (!isset($row[$fieldcounter])) {
    $tabledump .= "NULL";
  } else {
    $tabledump .= "'".mysql_escape_string($row[$fieldcounter])."'";
  }
}

$tabledump .= ");\n";

if ($fp) {
  fwrite($fp,$tabledump);
} else {
  echo $tabledump;
}

}
mysql_free_result($rows);

//print $tabledump;
return true;
}

$filename="database_backup.txt";

$filehandle=fopen($filename,"w");
$result=mysql_query("SHOW tables");
while ($currow=mysql_fetch_array($result)) {
sqldumptable($currow[0], $filehandle);
fwrite($filehandle, "\n\n\n");
}
fclose($filehandle);
echo " Data dumped sucessfully!Click <a href='database_backup.txt'>here</a> to download";

bobthedog

Alternatively, if you have permissions to run external programs on your box, then you could always do: -

exec('mysqldump -h HOSTNAME -u USERNAME --password=PASSWORD DATABASE_NAME OPTIONAL_TABLE_LIST');

so - you could use this as....

exec('mysqldump -h localhost -u fred --password=fred some_tables');

...to dump all of the data and structure in the 'some_tables' database, or...

exec('mysqldump -h localhost -u fred --password=fred some_tables names');

... to dump just the 'names' table in the database 'some_tables'. Anyhow - just run mysqldump without any params to see most of the list of what you can do with it. YOu can then take the output of the exec func (of which ever func you want to use to run a system command) and either dump it to the browser, or put it in a new file.

However, if you are running it from a bash script, then you could probably be a bit cleverer, and write the SQL output into a file (using standard *NIX commands to pipe output) and then recurse through your root dir for your htdocs files and tarball (and maybe compress) it into one file. You can then pass the filename back to your php script and let the script dump the file to the browser for the user to download.

One thing to mention though is security. This file now contains all your site info, and you want to make sure that no one else can get hold of this file while it is being generated. Make sure you set perms etc on it to prevent anyone else accessing it, and you will probably want your PHP script to delete the file after you have finished with it.

Hope this helps ya 🙂