Backup / Restore MySQL Databases with PHP

[tawevolution]

Ok, I am looking for a [b]PHP[/b] script that can back up a mysql database, save it to a folder on my web host (e.g. /backup), and then on another page (restore.php)  have the ability to restore one of the backups. How is this done?

[BillyBoB]

The below code is how to backup the database

[code]<?php
$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

?>[/code]

The below code is how to Restore a backup
[code]<?php
$tableName  = 'mypet';
$backupFile = 'mypet.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

?>[/code]

[utexas_pjm]

If you have access to a shell I would imagine that something like this would be the least painful:

[code]
$backup_cmd = 'mysqldump --tab=/home/you/db_Backups --opt db_name';
shell_exec($backup_cmd);
[/code]

[edit]

BBB's suggestion would be a better route...

[BillyBoB]

that is just to backup i saw that on my research too.

Plus php is very flexible so with the php file you can easily use a form to change the table name and the saved file name. either way.

[tawevolution]

[quote author=BillyBoB link=topic=118360.msg483617#msg483617 date=1165962307]
The below code is how to backup the database

[code]<?php
$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

?>[/code]

The below code is how to Restore a backup
[code]<?php
$tableName  = 'mypet';
$backupFile = 'mypet.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

?>[/code]
[/quote]

Thanks Very Much BillyBoB!!! This is exactly what I have been looking for ... there are a few other things out there - but they are rubbish but this is perfect - thnx bud

(btw utexas_pjm, I want it in PHP for my admin system xD)

Thnx Guys,
Evo out...

EDIT::::
[u][b]This only seems to backup a table - I want the whole database (called development) which has all the tables in it[/b][/u]

[roopurt18]

Here is a script I used to move [i]every[/i] database on our old host to our new webhost.  I'm sure you could modify the code to do what you wanted.

[code]<?php
  // MoveDBs.php
  // Automated PHP script to move the databases from our old host
  // to our new host.
  $new_host = '';
  $new_user = '';
  $new_pass = '';
  $Imported = Array();

  $old_host = '';
  $old_user = '';
  $old_pass = '';
  $DBs = Array();

  set_time_limit(0);

  // Delete all current *.sql files
  $cmd = sprintf('rm ~/host_move/*.sql');
  echo "\n" . $cmd . "\n";
  system($cmd);

  // We need to retrieve the name of all our databases
  // from the old host
  mysql_connect($old_host, $old_user, $old_pass) or
    die("Error: Could not connect to database.");
  $q = mysql_query("SHOW DATABASES");
  if($q){
    while($row = mysql_fetch_array($q)){
      $DBs[] = $row['Database'];
    }
  }
  mysql_close();

  if(true){ // Set false to skip the import
    // Dump each database
    if(count($DBs)){
      foreach($DBs as $DB){
        // Dump a .sql for each database
        $cmd = sprintf('mysqldump --opt -h %s -u %s -p%s --databases %s > %s.sql',
                       $old_host,
                       $old_user,
                       $old_pass,
                       $DB,
                       $DB
                      );
        echo "\n" . $cmd . "\n";
        system($cmd);
      }
    }
  }

  // Now we need to open all of our .sql files and import
  // into our new database
  if(count($DBs)){
    foreach($DBs as $DB){
      // Check that the file exists
      if(file_exists($DB . '.sql')){
        $Imported[$DB]['Msgs'] = "{$DB}.sql - File found.";
        $cmd = sprintf('mysql -h %s -u %s -p%s < %s.sql',
                       $new_host,
                       $new_user,
                       $new_pass,
                       $DB
                      );
        echo "\n{$cmd}\n";
        system($cmd);
      }else{
        $Imported[$DB]['Msgs'] = "ERROR - {$DB}.sql - File DNE.";
        $Imported[$DB]['Errors'] = "{$DB}.sql - File DNE.";
      }
    }
  }else{
    echo "\nNo imported databases exist.\n";
  }

  print_r($Imported);
?>[/code]

[mlin]

Here's BillyBob's example with a loop to get all tables

[code]<?php

$con = mysql_connect("localhost", "username", "password");

mysql_select_db("development");

$doc_root = "/path/to/root/";
$backup_dir = $doc_root . "backup/";

//select all tables
$q = mysql_query("show tables");

if (mysql_num_rows($q) > 0) {
for ($i = 0; $i < mysql_num_rows($q); $i++) {
$tmp = mysql_fetch_assoc($q);
//var_dump($tmp);
$tables[] = $tmp['Tables_in_development'];
}
}


//var_dump($tables);

for ($i = 0; $i < count($tables); $i++) {
$backup_file = $backup_dir . $tables[$i] . ".sql";
if (file_exists($backup_file)) unlink($backup_file);
//delete the current backup if it already exists else the query will fail

$query = "SELECT * INTO OUTFILE '$backup_file' FROM {$tables[$i]}";
$r = mysql_query($query);
if ($r == false) echo $tables[$i] . " table could not be exported\n";
}

?>[/code]

And for restore, loop through your backup directory and use BillyBob's query for each file

[tawevolution]

thnx mlin, yours seems more suitable for the main thing i wanted, but roopurt18, that was another thing i was going to ask (btw, does that MOVE it or COPY it to the new host???)

[roopurt18]

Well it copies it.  So the original data is left intact.  The main difference between the two approaches is mine uses shell commands.  To do it strictly through PHP requires more queries and a little more programming effort on your part.

Also, I'm not sure if the SELECT INTO OUTFILE copies table structure or not, but I know mine does.  That may be something worth considering as well.

[tawevolution]

oh ok, well thanks guys for your help, much appreciated ... now for the 'fun' bit - the work xD